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:

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!

Cursor-Brent-Ozar

Brent curses a lot, so I guess you could call him a cursor.

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:

More Cursor Resources

Menu
{"cart_token":"","hash":"","cart_data":""}