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;
WHILE LOOP WHILE @@FETCH_STATUS = 0 ...
CLOSE CLOSE cursor_name;
DELLOCATE DEALLOCATE 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;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @item_name;
PRINT CAST(@quantity AS varchar);
FETCH NEXT FROM cr INTO @product_name, @list_price;
END;
CLOSE cr;
DEALLOCATE cr;
Comparison with While Loop
DECLARE @counter INT;
SET @counter = 0;
WHILE @site_value <= 15
BEGIN
PRINT @counter;
SET @counter = @counter + 1;
END;
//The loop would terminate once the @counter exceeded 15
Comparison:-
- 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….
