What are the advantages of using Stored Procedures ?

Stored procedures have many advantages. They help you to separate the client application from the underlying structure of the database which allows you to simplify client coding, and improve the stability of the application. Furthermore, by using stored procedures, you can very easily create reusable code that can be executed from all of the applications that you and other developers write.

Stored procedures are very effective in terms of performance because they are pre-compiled and execute quickly. When you build a stored procedure, a query plan is created-which contains the most efficient method of executing the stored procedure given available indexes and other environmental factors. Another benefit of stored procedures is that they tend to lock data for shorter periods of time than the equivalent application code.

Stored procedures facilitate the security of data. This is because when you give users or groups rights to stored procedures, it is not necessary for you to give them rights to the underlying tables. A common scenario is to give users or groups view-only rights to tables. This way, they can build their own queries and reports. You then use stored procedures to add, edit, and delete data. Once you give users or groups the rights to the stored procedures, it is not necessary to give them add, edit, and delete rights to the underlying tables.

You can use stored procedures in either a two-tier or a three-tier application development model. In the two-tier model, the data and the stored procedures reside on a SQL Server. The application tier contains the GUI (graphical user interface) as well as the code that interacts with the tables, views, and stored procedures that reside on the SQL Server. In the three-tier model, the data and the stored procedures once again reside on the SQL Server. The difference is that the application tier contains only the GUI. The code that interacts with the data tier is located in a separate library, generally a Visual Basic DLL. We refer to this as the business logic tier. You can then use the business object, or DLL, with multiple applications. The three-tier model promotes reuse, and further separates the application from the data tier.

What is the difference between Stored Procedures and Functions

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

What's new in C# 3.5 ?

The key features that are introduced in C# 3.5 are

° Anonymous Types for LINQ

° Implicitly Typed Local Variables - The var Keyword

° Extension Methods

° Object and Collection Initializers

° Lambda Expressions

What are the types of polymorphism ?

There are two types of polymorphism:

1. Static (Compile time): Function Overloading, Operator Overloading
   
2. Dynamic (Run time): Virtual Functions
   

What is object slicing ?

When a derived class object is assigned to a base class, only the base class's part of content in the derived object are copied to the base class, leaving behind the derived class specific contents. This is referred as Object Slicing.

Example:

Class Base
{
public int i;
};

class Derived : public Base
{
public int j;
};

int main()
{
Base objB;
Derived objD;
objB = objD;
//Here objD contains both i and j.
//But only i is copied to objB.
}

What is the difference between DELETE and TRUNCATE ?

1. TRUNCATE is a DDL command whereas DELETE is a DML command.
2. TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3. You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5. You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
6. TRUNCATE command resets the High Water Mark for the table but DELETE does not. So after TRUNCATE the operations on table are much faster.

How to maintain the scroll position of the page on postback ?

Using MaintainScrollPositionOnPostback attribute in @Page directive

Note:- You can define this attribute for all pages by setting the maintainScrollPostitionOnPostback attribute (note that it is case-sensitive in configuration files) on the element of the Web.config file.