Some Useful C Sharp Programs for Interview

Write C# method for ATM money disposal, which take integers in multiple of 100’s as a input and print number denomination (1000’s, 500’s and 100’s) in it.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication12
{
    class Program
    {
        static void Main(string[] args)
        {
            int x, y, z, c = 0;
            Console.WriteLine("enter the number");
            x = int.Parse(Console.ReadLine());
            {
                y = x / 1000;
                z = (x % 1000) / 500;
                c = ((x % 1000) % 500) / 100;
            }
            Console.WriteLine("the 1000 is {0}", y);
            Console.WriteLine("the 500 is:" + z);
            Console.WriteLine("the 100 is {0}", c);
            Console.ReadKey();
        }
    }
}

Write c# method to swap 2 numbers without using the third/temp variable

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace swap
{
    class Program
    {
        static void Main(string[] args)
        {
            int a = 10;
            int b = 30;
            a = a + b;
            b = a - b;
            Console.WriteLine("the value of b:" + b);
            a = a - b;
            Console.WriteLine(a);
        }
    }
}

Write C# method to reverse the input string without using String.Reverse() method

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace reverse_string
{
    class Program
    {
        static void Main(string[] args)
        {

            char[] a = new char[10];
            Console.WriteLine("enter a string(Max 10 char):");
            a = Console.ReadLine().ToCharArray();
            int len, i;
            char temp;
            len = a.Length - 1;
            for (i = 0; i < a.Length / 2; i++)
            {
                temp = a[i];
                a[i] = a[len];
                a[len--] = temp;
            }
           
            Console.WriteLine("the ordered array");
            for(i=0;i<a.Length;i++)
                Console.Write(a[i]);
            Console.ReadKey();
        }
    }
}

Write C# method to find number of vowel character in the given sentence

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace vowel_count
{
    class Program
    {
        static void Main(string[] args)
        {
            char[] str = new char[10];
            Console.WriteLine("enter a string(Max 10 char):");
            str = Console.ReadLine().ToCharArray();
            int i = 0;
            int vowels=0;
            for (i = 0; i < str.Length; i++)
            {
                if (str[i] == 'A' || str[i] == 'E' || str[i] == 'I' || str[i] == 'O' || str[i] == 'U' ||
                    str[i] == 'a' || str[i] == 'e' || str[i] == 'i' || str[i] == 'o' || str[i] == 'u')
                {
                    vowels++;
                 
                  
                }
        }

                Console.WriteLine("the number of vowels {0}", vowels);
                Console.ReadKey();
           
        }

Write C# method to display all the prime number exist between two input numbers

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
           int i, prime;
            int lim_up=0;
            int lim_low=0;
            int n;
            Console.WriteLine("Enter the lower limit {0}",lim_low);
            lim_low = int.Parse(Console.ReadLine());
             Console.WriteLine("Enter the lower limit {0}",lim_up);
             lim_up = int.Parse(Console.ReadLine());
            Console.WriteLine("the prime numbers are:");



for(n=lim_low+1; n<lim_up; n++)
{
prime = 1;
for(i=2; i<n; i++)
if(n%i == 0)
{
prime = 0;
break;
}
if (prime!=0)
    Console.WriteLine("the prime number {0}", n);
}
Console.ReadKey();
        }
    }
}

Write C# method to display second largest and second smallest number in a given array.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {

            int temp;
            int[] sort = new int[5];
            Console.WriteLine("enter the number");
            for (int i = 0; i < sort.Length; i++)
                sort[i] = int.Parse(Console.ReadLine());
            for (int i = 0; i < sort.Length; i++)
            {
                for (int j = i + 1; j < sort.Length; j++)
                {
                    if (sort[i] > sort[j])
                    {
                        temp = sort[i];
                        sort[i] = sort[j];
                        sort[j] = temp;
                    }
                }
            }
            Console.WriteLine("the output");
            for (int i = 0; i < sort.Length; i++)
                Console.WriteLine(sort[i]);

            Console.WriteLine("the second smallest number is {0}", sort[1]);
            Console.WriteLine("the second largest number is {0}", sort[3]);
            Console.ReadKey();

        }

    }
}

Fibonacci Series Program

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace fibonacci
{
    class Program
    {
        static void Main(string[] args)
        {
            int f1 = 0, f2 = 1, f3, limit, count;
            Console.WriteLine("enter the limit");
            limit = int.Parse(Console.ReadLine());
            Console.WriteLine("the fibonacci series is:");
            Console.WriteLine("{0} {1}", f1, f2);
            count = 2;
           
            while (count <limit)
            {
                f3 = f1 + f2;
                f1 = f2;
                f2 = f3;
                count++;
                Console.WriteLine("{0}", f3);
               
            }
        }
    }
}

Palindrome Check Program.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Palindrome
{
    class Program
    {
        static void Main(string[] args)
        {
            char[] str = new char[10];
            Console.WriteLine("enter the palindrome");
            str = Console.ReadLine().ToCharArray();
            Console.Write(CheckPalindrome(str));
            Console.WriteLine(str);
            Console.ReadLine();
        }
        private static bool CheckPalindrome(char[] mystring)
        {
            int startchar;
            int lastchar;
            startchar = 0;
            lastchar = mystring.Length - 1;
            while (startchar < lastchar)
            {
                if (mystring[startchar] == mystring[lastchar])
                {
                    startchar++;
                    lastchar--;
                }
                else
                {
                    return false;
                }
            }
            return true;

        }
    }
}

C Sharp Interview Questions/Answers Part-1

 What are the new features introduced in c# 4.0?
This question is basically asked to check, if you are passionate about catching up with latest technological advancements. The list below shows a few of the new features introduced in c# 4.0. If you are aware of any other new features, please submit those using the from at the end of this post.
1. Optional and Named Parameters
2. COM Interoperability Enhancements
3. Covariance and Contra-variance
4. Dynamic Type Introduction

What's the difference between IEnumerable<T> and List<T> ?
1. IEnumerable is an interface, where as List is one specific implementation of IEnumerable. List is a class.
2. FOR-EACH loop is the only possible way to iterate through a collection of IEnumerable, where as List can be iterated using several ways. List can also be indexed by an int index, element can be added to and removed from and have items inserted at a particular index.
3. IEnumerable doesn't allow random access, where as List does allow random access using integral index.
4. In general from a performance standpoint, iterating thru IEnumerable is much faster than iterating thru a List.

Difference between EXE and DLL?
1. .EXE is an executable file and can run by itself as an application, where as .DLL is usullay consumed by a .EXE or by another .DLL and we cannot run or execute .DLL directly.
2. For example, In .NET, compiling a Console Application or a Windows Application generates .EXE, where as compiling a Class Library Project or an ASP.NET web application generates .DLL. In .NET framework, both .EXE and .DLL are called as assemblies.

What are the difference between interfaces and abstract classes?
1. Abstract classes can have implementations for some of its members, but the interface can't have implementation for any of its members.
2. Interfaces cannot have fields where as an abstract class can have fields.
3. An interface can inherit from another interface only and cannot inherit from an abstract class, where as an abstract class can inherit from another abstract class or another interface.
4. A class can inherit from multiple interfaces at the same time, where as a class cannot inherit from multiple classes at the same time.
5. Abstract class members can have access modifiers where as interface members cannot have access modifiers.

What is a delegate?
A delegate is a type safe function pointer. Using delegates you can pass methods as parameters. To pass a method as a parameter, to a delegate, the signature of the method must match the signature of the delegate. This is why, delegates are called type safe function pointers.

What is the main use of delegates in C#?
Delegates are mainly used to define call back methods.

What do you mean by chaining delegates?
Or
What is a multicast delegate?
The capability of calling multiple methods on a single event is called as chaining delegates. Let me give you an example to understand this further.
1. Create a new asp.net web application
2. Drag and drop a button control and leave the ID as Button1.
3. On the code behind file, add the code shown below.

When you click the Button now, both Method1 and Method2 will be executed. So, this capability of calling multiple methods on a single event is called as chaining delegates. In the example, we are using EventHandler delegate, to hook up Method1 and Method2 to the click event of the button control. Since, the EventHandler delegate is now pointing to multiple methods, it is also called as multicast delegate.

What are the advantages of using interfaces?
Interfaces are very powerful. If properly used, interfaces provide all the advantages as listed below.
1. Interfaces allow us to implement polymorphic behaviour. Ofcourse, abstract classes can also be used to implement polymorphic behaviour.
2. Interfaces allow us to develop very loosely coupled systems.
3. Interfaces enable mocking for better unit testing.
4. Interfaces enables us to implement multiple class inheritance in C#.
5. Interfaces are great for implementing Inverson of Control or Dependancy Injection.
6. Interfaces enable parallel application development.

What are the advantages and disadvantages of using arrays?
Advantages of using arrays:
1. Arrays are strongly typed, meaning you can only have one type of elements in the array. The strongly typed nature of arrays gives us
2 advantages. One, the performance will be much better because boxing and unboxing will not happen. Second, run time errors can be prevented because of type mis matches. Type mis matches and runtime errors are most commonly seen with collection classes like ArrayList, Queue, Stack etc, that are present in System.Collections namespace.
Disadvantages of using arrays:
1. Arrays are fixed in size and cannot grow over time, where ArrayList in System.Collections namespace can grow dynamically.
2. Arrays are zero index based, and hence a little difficult to work with. The only way to store or retrieve elements from arrays, is to use integral index. Arrays donot provide convinient methods like Add(), Remove() etc provided by collection classes found in System.Collections or System.Collections.Generics namespaces, which are very easy to work with.

 Explain what is an Interface in C#?
An Interface in C# is created using the interface keyword. An example is shown below.

Code:

using System;
namespace Interfaces
{
interface IBankCustomer
{
void DepositMoney();
void WithdrawMoney();
}
public class Demo : IBankCustomer
{
public void DepositMoney()
{
Console.WriteLine("Deposit Money");
}

public void WithdrawMoney()
{
Console.WriteLine("Withdraw Money");
}

public static void Main()
{
Demo DemoObject = new Demo();
DemoObject.DepositMoney();
DemoObject.WithdrawMoney();
}
}
}

Can an Interface contain fields? No, an Interface cannot contain fields.

Can an interface inherit from another interface?
Yes, an interface can inherit from another interface. It is possible for a class to inherit an interface multiple times, through base classes or interfaces it inherits. In this case, the class can only implement the interface one time, if it is declared as part of the new class. If the inherited interface is not declared as part of the new class, its implementation is provided by the base class that declared it. It is possible for a base class to implement interface members using virtual members; in that case, the class inheriting the interface can change the interface behavior by overriding the virtual members.

Can you create an instance of an interface?
No, you cannot create an instance of an interface.

What is a partial class. Give an example?
A partial class is a class whose definition is present in 2 or more files. Each source file contains a section of the class, and all parts are combined when the application is compiled. To split a class definition, use the partial keyword as shown in the example below. Student class is split into 2 parts. The first part defines the study() method and the second part defines the Play() method. When we compile this program both the parts will be combined and compiled. Note that both the parts uses partial keyword and public access modifier.

Code:

using System;
namespace PartialClass
{
  public partial class Student
  {
    public void Study()
    {
      Console.WriteLine("I am studying");
    }
  }
  public partial class Student
  {
    public void Play()
    {
      Console.WriteLine("I am Playing");
    }
  }
  public class Demo
  {
    public static void Main()
    {
      Student StudentObject = new Student();
      StudentObject.Study();
      StudentObject.Play();
    }
  }
}

It is very important to keep the following points in mind when creating partial classes.
1. All the parts must use the partial keyword.
2. All the parts must be available at compile time to form the final class.
3. All the parts must have the same access modifiers - public, private, protected etc.
4. Any class members declared in a partial definition are available to all the other parts.
5. The final class is the combination of all the parts at compile time.

What are the advantages of using partial classes?
1. When working on large projects, spreading a class over separate files enables multiple programmers to work on it at the same time.

2. When working with automatically generated source, code can be added to the class without having to recreate the source file. Visual Studio uses this approach when it creates Windows Forms, Web service wrapper code, and so on. You can create code that uses these classes without having to modify the file created by Visual Studio.

Is it possible to create partial structs, interfaces and methods?
Yes, it is possible to create partial structs, interfaces and methods. We can create partial structs, interfaces and methods the same way as we create partial classes.

Can you create partial delegates and enumerations?
No, you cannot create partial delegates and enumerations.

Can different parts of a partial class inherit from different interfaces?
Yes, different parts of a partial class can inherit from different interfaces.

Can you specify nested classes as partial classes?
Yes, nested classes can be specified as partial classes even if the containing class is not partial. An example is shown below.

Code:

class ContainerClass
{
  public partial class Nested
  {
    void Test1() { }
  }
  public partial class Nested
  {
    void Test2() { }
  }
}

How do you create partial methods?
To create a partial method we create the declaration of the method in one part of the partial class and implementation in the other part of the partial class. The implementation is optional. If the implementation is not provided, then the method and all the calls to the method are removed at compile time. Therefore, any code in the partial class can freely use a partial method, even if the implementation is not supplied. No compile-time or run-time errors will result if the method is called but not implemented. In summary a partial method declaration consists of two parts. The definition, and the implementation. These may be in separate parts of a partial class, or in the same part. If there is no implementation declaration, then the compiler optimizes away both the defining declaration and all calls to the method.

The following are the points to keep in mind when creating partial methods.
1. Partial method declarations must begin partial keyword.
2. The return type of a partial method must be void.
3. Partial methods can have ref but not out parameters.
4. Partial methods are implicitly private, and therefore they cannot be virtual.
5. Partial methods cannot be extern, because the presence of the body determines whether they are defining or implementing.

What is the use of partial methods?
Partial methods can be used to customize generated code. They allow for a method name and signature to be reserved, so that generated code can call the method but the developer can decide whether to implement the method. Much like partial classes, partial methods enable code created by a code generator and code created by a human developer to work together without run-time costs.

What is a nested type. Give an example?
A type(class or a struct) defined inside another class or struct is called a nested type. An example is shown below. InnerClass is inside ContainerClass, Hence InnerClass is called as nested class.

Code:

using System;
namespace Nested
{
  class ContainerClass
  {
    class InnerClass
    {
      public string str = "A string variable in nested class";
    }

    public static void Main()
    {
      InnerClass nestedClassObj = new InnerClass();
      Console.WriteLine(nestedClassObj.str);
    }
  }
}

What is a Destructor?
A Destructor has the same name as the class with a tilde character and is used to destroy an instance of a class.

Can a class have more than 1 destructor?
No, a class can have only 1 destructor.

Can structs in C# have destructors?
No, structs can have constructors but not destructors, only classes can have destructors.

Can you pass parameters to destructors?
No, you cannot pass parameters to destructors. Hence, you cannot overload destructors.

Can you explicitly call a destructor?
No, you cannot explicitly call a destructor. Destructors are invoked automatically by the garbage collector.

Why is it not a good idea to use Empty destructors?
When a class contains a destructor, an entry is created in the Finalize queue. When the destructor is called, the garbage collector is invoked to process the queue. If the destructor is empty, this just causes a needless loss of performance.

Is it possible to force garbage collector to run?
Yes, it possible to force garbage collector to run by calling the Collect() method, but this is not considered a good practice because this might create a performance over head. Usually the programmer has no control over when the garbage collector runs. The garbage collector checks for objects that are no longer being used by the application. If it considers an object eligible for destruction, it calls the destructor(if there is one) and reclaims the memory used to store the object.

Usually in .NET, the CLR takes care of memory management. Is there any need for a programmer to explicitly release memory and resources? If yes, why and how?
If the application is using expensive external resource, it is recommend to explicitly release the resource before the garbage collector runs and frees the object. We can do this by implementing the Dispose method from the IDisposable interface that performs the necessary cleanup for the object. This can considerably improve the performance of the application.

When do we generally use destructors to release resources?
If the application uses unmanaged resources such as windows, files, and network connections, we use destructors to release resources.

What is a constructor in C#?
Constructor is a class method that is executed when an object of a class is created. Constructor has the same name as the class, and usually used to initialize the data members of the new object.

In C#, What will happen if you do not explicitly provide a constructor for a class?
If you do not provide a constructor explicitly for your class, C# will create one by default that instantiates the object and sets all the member variables to their default values.

Structs are not reference types. Can structs have constructors?
Yes, even though Structs are not reference types, structs can have constructors.

We cannot create instances of static classes. Can we have constructors for static classes?
Yes, static classes can also have constructors.

Can you prevent a class from being instantiated?
Yes, a class can be prevented from being instantiated by using a private constructor as shown in the example below.

Code:

using System;
namespace TestConsole
{
  class Program
  {
    public static void Main()
    {
      //Error cannot create instance of a class with private constructor
      SampleClass SC = new SampleClass();
    }
  }
  class SampleClass
  {
    double PI = 3.141;
    private SampleClass()
    {
    }
  }
}

Can a class or a struct have multiple constructors?
Yes, a class or a struct can have multiple constructors. Constructors in csharp can be overloaded.

Can a child class call the constructor of a base class?
Yes, a child class can call the constructor of a base class by using the base keyword as shown in the example below.

Code:

using System;
namespace TestConsole
{
  class BaseClass
  {
    public BaseClass(string str)
    {
      Console.WriteLine(str);
    }
  }

  class ChildClass : BaseClass
  {
    public ChildClass(string str): base(str)
    {
    }

    public static void Main()
    {
      ChildClass CC = new ChildClass("Calling base class constructor from child class");
    }
  }
}

If a child class instance is created, which class constructor is called first - base class or child class?
When an instance of a child class is created, the base class constructor is called before the child class constructor. An example is shown below.

Code:

using System;
namespace TestConsole
{
  class BaseClass
  {
    public BaseClass()
    {
      Console.WriteLine("I am a base class constructor");
    }
  }
  class ChildClass : BaseClass
  {
    public ChildClass()
    {
      Console.WriteLine("I am a child class constructor");
    }
    public static void Main()
    {
      ChildClass CC = new ChildClass();
    }
  }
}

Can a class have static constructor?
Yes, a class can have static constructor. Static constructors are called automatically, immediately before any static fields are accessed, and are generally used to initialize static class members. It is called automatically before the first instance is created or any static members are referenced. Static constructors are called before instance constructors. An example is shown below.

Code:

using System;
namespace TestConsole
{
  class Program
  {
    static int I;
    static Program()
    {
      I = 100;
      Console.WriteLine("Static Constructor called");
    }
    public Program()
    {
      Console.WriteLine("Instance Constructor called");
    }
    public static void Main()
    {
      Program P = new Program();
    }
  }
}

Can you mark static constructor with access modifiers?
No, we cannot use access modifiers on static constructor.

Can you have parameters for static constructors?
No, static constructors cannot have parameters.

What happens if a static constructor throws an exception?
If a static constructor throws an exception, the runtime will not invoke it a second time, and the type will remain uninitialized for the lifetime of the application domain in which your program is running.

Give 2 scenarios where static constructors can be used?
1. A typical use of static constructors is when the class is using a log file and the constructor is used to write entries to this file.
2. Static constructors are also useful when creating wrapper classes for unmanaged code, when the constructor can call the LoadLibrary method.

Does C# provide copy constructor?
No, C# does not provide copy constructor.

SQL Server Interview Questions/Answers

Explain DML, DDL, DCL and TCL statements with examples?

DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.


DDL: DDL stands for Data Definition Language. DDL is used to create and modify the structure of database objects.
Examples: CREATE, ALTER, DROP statements.

DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements


TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements


What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate - All operations can be rolled back.

Delete is a logged operation, which means deleted rows are written to the transaction log.Truncate is not a logged operation, which means deleted rows are not written to the transaction log.

Hence, truncate is a little faster than Delete. You can have a where clause in Delete statement where as Truncate statement cannot have a where clause. Truncate will delete all the rows in a Table, but the structure of the table remains. Drop would delete all the rows including the structure of the Table.

What is Cascading referential integrity constraint?
Cascading referential integrity constraints allow you to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys point.

You can instruct SQL Server to do the following:
1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.

3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

DIfference between primary key and unique key in SQL Server?
1. A table can have only one primary key. On the other hand a table can have more than one unique key.
2. Primary key column does not accept any null values, where as a unique key column accept one null value.

What is the difference between Having and Where clause?
a WHERE clause is used in the select statement to filter the rows as they are retrieved from the database table. HAVING clause is used in the select statement in conjunction with the Group By clause, to filter the query results after they have been grouped.
You can use HAVING clause only when you use Group By clause.

What is the use of an Index in SQL Server?
Relational databases like SQL Server use indexes to find data quickly when a query is processed. Creating the proper index can drastically increase the performance of an application.

What is a table scan?
or
What is the impact of table scan on performance?
When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word. The SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan. A full table scan of a very large table can adversely affect the performance. Creating proper indexes will allow the database to quickly narrow in on the rows to satisfy the query, and avoid scanning every row in the table.

What is the system stored procedure that can be used to list all the indexes that are created for a specific table?
sp_helpindex is the system stored procedure that can be used to list all the indexes that are created for a specific table.

For example, to list all the indexes on table tblCustomers, you can use the following command.
EXEC sp_helpindex tblCustomers

[b]What is the purpose of query optimizer in SQL Server?[/b]
An important feature of SQL Server is a component known as the query optimizer. The query optimizer's job is to find the fastest and least resource intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task.

What is the first thing you will check for, if the query below is performing very slow?
SELECT * FROM tblProducts ORDER BY UnitPrice ASC

Check if there is an Index created on the UntiPrice column used in the ORDER BY clause. An index on the UnitPrice column can help the above query to find data very quickly.When we ask for a sorted data, the database will try to find an index and avoid sorting the results during execution of the query. We control sorting of a data by specifying a field, or fields, in an ORDER BY clause, with the sort order as ASC (ascending) or DESC (descending).

With no index, the database will scan the tblProducts table and sort the rows to process the query. However, if there is an index, it can provide the database with a presorted list of prices. The database can simply scan the index from the first entry to the last entry and retrieve the rows in sorted order.

The same index works equally well with the following query, simply by scanning the index in reverse.
SELECT * FROM tblProducts ORDER BY UnitPrice DESC

What is the significance of an Index on the column used in the GROUP BY clause?
Creating an Index on the column, that is used in the GROUP BY clause, can greatly improve the perofrmance. We use a GROUP BY clause to group records and aggregate values, for example, counting the number of products with the same UnitPrice. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY.

The following query counts the number of products at each price by grouping together records with the same UnitPrice value.
SELECT UnitPrice, Count(*) FROM tblProducts GROUP BY UnitPrice

The database can use the index (Index on UNITPRICE column) to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able to count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.

What is the role of an Index in maintaining a Unique column in table?
Columns requiring unique values (such as primary key columns) must have a unique index applied. There are several methods available to create a unique index.
1. Marking a column as a primary key will automatically create a unique index on the column.
2. We can also create a unique index by checking the Create UNIQUE checkbox when creating the index graphically.
3. We can also create a unique index using SQL with the following command:
CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)

The above SQL command will not allow any duplicate values in the ProductName column, and an index is the best tool for the database to use to enforce this rule. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values.

What are the disadvantages of an Index?
There are 2 disadvantages of an Index
1. Increased Disk Space
2. Insert, Update and Delete statements could be slow. In short, all DML statements could be slow.

What are the 2 types of Indexes in SQL Server?
1. Clustered Index
2. Non Clustered Index

How many Clustered and Non Clustered Indexes can you have per table?
Clustered Index - Only one Clustered Index per table. A clustered index contains all of the data for a table in the index, sorted by the index key. Phone Book is an example for Clustered Index.
[b]Non Clustered Index[/b] - You can have multiple Non Clustered Indexes per table. Index at the back of a book is an example for Non Clustered Index.

Which Index is faster, Clustered or Non Clustered Index?
Clustered Index is slightly faster than Non Clustered Index. This is because, when a Non Clustered Index is used there is an extra look up from the Non Clustered Index to the table, to fetch the actual rows.

What is a Trigger in SQL Server?
A Trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.

What are the two types of Triggers in SQL Server?
1. After Triggers : Fired after Insert, Update and Delete operations on a table.
2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.

What are the special tables used by Triggers in SQL Server?
Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted table and the data that is being updated is contained in deleted table.

What is the difference between a User Defined Function (UDF) and a Stored Procedure (SP) in SQL Server?
1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
3. UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.
4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
5. UDF should return a value where as Stored Procedure need not.
6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.
7. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
8. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
9. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs

 

What is a View in SQL Server?
You can think of a view either as a compiled sql query or a virtual table. As a view represents a virtual table, it does not physically store any data. When you query a view, you actually retrieve the data from the underlying base tables.

What are the advantages of using views?
Or
When do you usually use views?
1. Views can be used to implement row level and column level security.
2. Simplify the database schema to the users. You can create a view based on multiple tables which join columns from all these multiple tables so that they look like a single table.
3. Views can be used to present aggregated and summarized data.

Can you create a view based on other views?
Yes, you can create a view based on other views. Usually we create views based on tables, but it also possible to create views based on views.

Can you update views?
Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables you can make use INSTEAD OF triggers in SQL Server.

What are indexed views?
Or
What are materialized views?
A view is a virtual table, it does not contain any physical data. A view is nothing more than compiled SQL query. Every time, we issue a select query against a view, we actually get the data from the underlying base tables and not from the view, as the view itself does not contain any data.
When you create an index on a view, the data gets physically stored in the view. So, when we issue a select query against an indexed view, the data is retrieved from the index without having to go to the underlying table, which will make the select statement to work slightly faster. However, the disadvantage is, INSERT, UPDATE and DELETE operations will become a little slow, because every time you insert or delete a row from the underlying table, the view index needs to be updated. Inshort, DML operations will have negative impact on performance.
Oracle refers to indexed views as materialized views.
Only the views created with schema binding, can have an Index. Simply adding WITH SCHEMABINDING to the end of the CREATE VIEW statement will accomplish this. However, the effect is that any changes to the underlying tables which will impact the view are not allowed. Since the indexed view is stored physically, any schema changes would impact the schema of the stored results set. Therefore, SQL Server requires that schema binding be used to prevent the view's schema (and therefore the underlying tables) from changing.
The first index for a view must be a UNIQUE CLUSTERED INDEX, after which, it's possible to create non-clustered indexes against the view.
Indexed Views are heavily used in data warehouses and reporting databases that are not highly transactional.

What are the limitations of a View?
1. You cannot pass parameters to a view.
2. Rules and Defaults cannot be associated with views.
3. The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
4. Views cannot be based on temporary tables.

[b]What are the different types of joins available in sql server?[/b]
There are 3 different types of joins available in sql server, and they are
1. Cross Join
2. Inner Join or Join
3. Outer Join

Outer Join is again divided into 3 types as shown below.
1. Left Outer Join or Left Join
2. Right Outer Join or Right Join
3. Full Outer Join or Full Join

You might have heard about self join, but self join is not a different type of join. A self join means joining a table with itself. We can have an inner self join or outer self join.

Advantages of stored procedures
This is a very common sql server interview question. There are several advantages of using stored procedures over adhoc queries, as listed below.
1. Better Performance : Stored procedures are precompiled and hence run much faster than adhoc queries
2. Better Security : Applications making use of dynamically built adhoc sql queries are highly susceptible to sql injection attacks, where as Stored Procedures can avoid SQL injection attacks completely.
3. Reduced Network Traffic: Stored procedures can reduce network traffic to a very great extent when compared with adhoc sql queries. With stored procedures, you only need to send the name of the procedure between client and server. Imagine the amount of network bandwith that can be saved especially if the stored procedure contains 1000 to 2000 lines of SQL.
4. Better Maintainance and Reusability: Stored procedures can be used any where in the application. It is easier to maintain a stored procedure that is used on several pages as the modfifcations just need to be changed at one place where the stored procedure is defined. On the other hand, maintaining an adhoc sql query that's used on several pages is tedious and error prone, as we have to make modifications on each and every page.

 

MVC Interview Questions/Answers

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

In which assembly is the MVC framework defined?
System.Web.Mvc

Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained with in the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained with in the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained with in the controller.

What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC

What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Seperation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.

Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and alse selecting the view to render.

Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult

What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute.

What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods. ASP.NET Routing makes use of route table. Route table is created when your web application first starts. The route table is present in the Global.asax file.

What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method

Example: http://pragimtech.com/Customer/Details/5
Controller Name = Customer
Action Method Name = Details
Parameter Id = 5

ASP.NET MVC application, makes use of settings at 2 places for routing to work correctly. What are these 2 places?
1. Web.Config File : ASP.NET routing has to be enabled here.
2. Global.asax File : The Route table is created in the application Start event handler, of the Global.asax file.

What is the adavantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should map to a physical file. If the file does not exist, we get page not found error.

An ASP.NET web application that does make use of routing, makes use of URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users.

What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.

Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter between the placeholders. Therefore, routing cannot determine where to separate the value for the controller placeholder from the value for the action placeholder.

What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or ScriptResource.axd from being passed to a controller.

What is the difference between adding routes, to a webforms application and to an mvc application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class, where as to add routes to an MVC application we use MapRoute() method.

How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all parameter.
controller/{action}/{*parametervalues}

What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface

Give 2 examples for scenarios when routing is not applied?
1. A Physical File is Found that Matches the URL Pattern - This default behaviour can be overriden by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to prevent routing from handling certain requests.

What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

If I have multiple filters impleted, what is the order in which these filters get executed?
1. Authorization filters
2. Action filters
3. Response filters
4. Exception filters

What are the different types of filters, in an asp.net mvc application?
1. Authorization filters
2. Action filters
3. Result filters
4. Exception filters

Give an example for Authorization filters in an asp.net mvc application?
1. RequireHttpsAttribute
2. AuthorizeAttribute

Which filter executes first in an asp.net mvc application?
Authorization filter


What are the levels at which filters can be applied in an asp.net mvc application?
1. Action Method
2. Controller
3. Application
[b]Is it possible to create a custom filter?[/b]
Yes

What filters are executed in the end?
Exception Filters

Is it possible to cancel filter execution?
Yes

What type of filter does OutputCacheAttribute class represents?
Result Filter

What are the 2 popular asp.net mvc view engines?
1. Razor
2. .aspx

What symbol would you use to denote, the start of a code block in razor views?
@

What symbol would you use to denote, the start of a code block in aspx views?
<%= %>

In razor syntax, what is the escape sequence character for @ symbol?
The escape sequence character for @ symbol, is another @ symbol

When using razor views, do you have to take any special steps to proctect your asp.net mvc application from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross site scripting (XSS) attacks.

When using aspx view engine, to have a consistent look and feel, across all pages of the application, we can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout pages, reside in the shared folder, and are named as _Layout.cshtml

What are sections?
Layout pages, can define sections, which can then be overriden by specific views making use of the layout. Defining and overriding sections is optional.

What are the file extensions for razor views?
1. .cshtml - If the programming lanugaue is C#
2. .vbhtml - If the programming lanugaue is VB

How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the begining of a comment and *@ to indicate the end. An example is shown below.