Saturday, January 22, 2011

Cursor and Temp Table in SQL Server

Cursors are used for data to fetch the data from database and then performing row base operation.
Cursors fetches data with select statement and then performs operation on each row while loop.
This operation can also be performed by using the temp table. Can insert the records in temp table and looping can be done by using while loop on basis of table count.
In most cases temp table approach is much faster than Cursor approach.

Cursor syntax
1.      Declare cursor
2.      Select statement from table to get result set.
3.      Open cursor
4.      While loop for fetch status
5.      Fetch next
6.      Perform operation.
7.      Close cursor
8.      DE allocates cursor.
Temp table
1.      Create temp table
2.      Get count
3.      Use while loop
Perform the operation on rows


  1. your comparison isn't a fair comparison.. in the cursor you are actually doing something, however in your do while, you are not performing any action

  2. If you break down what SQL actually has to do in in order to do something to each row in a while look like your example of a cursor does you will see that the number of steps in the background are very close, but you should actually look at the number of reads and writes...