Cursor Example
Cursor Examples for SQL Server
Here’s an easy example of a cursor that loops through MyTable and gets an ID and a string from each row:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/* Set up variables to hold the current record we're working on */ DECLARE @CurrentID INT, @CurrentString VARCHAR(100); DECLARE cursor_results CURSOR FOR SELECT MyID, MyString FROM dbo.MyTable; OPEN cursor_results FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString WHILE @@FETCH_STATUS = 0 BEGIN /* Do something with your ID and string */ EXEC dbo.MyStoredProcedure @CurrentID, @CurrentString; FETCH NEXT FROM cursor_results into @CurrentID, @CurrentString END /* Clean up our work */ CLOSE cursor_results; DEALLOCATE cursor_results; |
Performance Considerations of Cursors
Cursors do row-by-row processing – or as Jeff Moden calls it, ReBAR – Row By Agonizing Row. This is a common approach for developers who think like application servers think – and hey, I used to write my T-SQL the same way myself!
However, at scale – when you’re running dozens or hundreds of cursor operations per minute – this can quickly run into higher CPU use and lock escalation problems. (That sounds kind of funny for me to say, because in the database world, “scale” is more along the lines of thousands of operations per second, but you’re not likely to achieve those levels with cursors in code.)
In the database world, you’re much better off performing set-based operations: working on all of the data at once, in a single statement. This lets SQL Server take a single set of quick locks on exactly the rows it needs, perform a quick update, and then get out.
For an example, check out this Stack Overflow answer on converting a cursor to set-based operation.
Sometimes cursors are fine – like for one-time utility code or database maintenance routines – but if you find yourself writing cursors as part of a transactional operation, it’s time to think about set-based operations instead. If you really need to use cursors and you need them to be fast, check out Brad Schulz’s The Truth About Cursors: Part 1. If you’re curious about cursors, Part 2 and Part 3 are also worth reading.
Cursor Training Videos
In this 16-minute video, Doug Lane explains how to use a date table, which will help you work around cursors that loop through date lists: