Difference between Set-Based approach and Cursor-Based approach in SQL Server ?

Think of it this way. If your wife wants you to fold the washing that's come out of the tumble dryer - she might well say 'can you please fold the washing?'. That's a set based approach - it's an operation over a collection of items.
The cursor based approach would be the equivalent of your wife asking you to fold a t-shirt, then when you had come back with that asking you to fold a pair of trousers, then when you had come back with that, asking if you could fold a jumper. No doubt, that would really hack you off. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.

When working in T-SQL, try to tell the system what you want to do with the data, not how you want it done.

What are the benefits of using Exists ?

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.
Here's a simple example from the pubs database using EXISTS:

if EXISTS (select *
 from authors
 where au_id = '172-32-1176')
  Print 'Record exits - Update'
ELSE
  Print 'Record doesn''t exist - Insert'

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.
The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. Consider the following two queries:

if exists (select *
 from authors
 where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
 from authors
 where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table.  
Even with that small number of records, the IF EXISTS version runs 4 
times faster than selecting a count.  This is because it stops as soon 
as it finds a single record that matches the criteria.  The second 
statement must process all the rows that match.

 

What are the differenct types of cursors in SQL Server ?

Following are the different types of cursors in Sql Server:

• Base Table : These are the lowest level cursor available. These can scroll forward or backward with minimal cost, and can be updated.
• Static : Cursor can move to any record, but the changes on data can't be seen.
• Forward-Only : Cursor moves one step forward. Can't move backward
• Dynamic : Most resource extensive. Cursor can move anywhere and all the changes on the data can be seen
• Keyset-driven : Only updated data can be viewed, inserted and deleted data cannot be viewed.

What are the types of temporary tables and where are they stored in SQL Server ?

There are two types of temporary tables:

• Local temporary tables:
  • Only available to the current connection to the database for the current login
  • They are dropped when the connection is closed
• Global temporary tables:
  • Available to any connection upon their creation
  • They are dropped when the last connection using them is closed  
   

Local Temporary Table CREATE TABLE #table_name (
    column_name [DATATYPE] )  

Global Temporary Table CREATE TABLE ##table_name (
column_name [DATATYPE] )

What will happen if same method is implemented in two parts of the partial class ?

It doesn't compile as you can't have two methods with the same name in one class.

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