Sql Server

How Cursors Works in Sql Server

How Cursors Works in Sql Server

How Cursors Works in Sql Server

A Sql cursor is a database object that is used to retrieve data / loop over the bunch of a result set/tuples  each row at a time. A SQL cursor is used when we have to manipulate data in each row Or specific row based on some condition(s) and its not feasible by a simple/single TSQL query

SQL Server cursor life cycle


@@FETCHSTATUS function - returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful.

Limitations of a SQL Cursor

  • A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.
  • Cursors can be faster than a while loop but they do have more overhead.
  • Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.
  • Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.
  • The cursors are slower because they update tables row by row. 

DECLARE DECLARE cursor_name CURSOR FOR select_statement;
OPEN OPEN cursor_name;
FETCH FETCH NEXT FROM cursor INTO variable_list;
CLOSE CLOSE cursor_name;

DECLARE @item_name VARCHAR(MAX), @quantity INT;
DECLARE cr CURSOR FOR SELECT product_name, quanitty FROM production.products;
OPEN cr;
FETCH NEXT FROM cr INTO @item_name, @quantity;

     PRINT @item_name;
     PRINT CAST(@quantity AS varchar);

     FETCH NEXT FROM cr INTO @product_name, @list_price;


Comparison with While Loop

 DECLARE @counter INT;
 SET @counter = 0;
 WHILE @site_value <= 15
      PRINT @counter;
      SET @counter = @counter + 1;
 //The loop would terminate once the @counter exceeded 15


  • Cursors are a looping construct built inside the database engine. Because of this, there is a little overhead involved in creating the cursor and destroying it. Also, a majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.
  • A WHILE loop is a programming construct. No objects is created in memory to facilitate the looping through a set of records which is required in a cursor….

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.