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] )