T-SQL cursors are generally bad approach and are often misused. In today’s world of correlated subqueries, CTE’s, recursive CTE’s, ranking and windowing functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and other tools, you have really few moments where cursor is better solution. On the other hand, there are still scenarios where cursor are best and most efficient solution.

If you think that holy moment of cursor occurred, it’s good to know which type of cursor you should use. It’s good to know what cursor features you need and choose a proper type of cursor. Or you’re most probably wasting server resources. I tried to summarize important aspects which you need consider when using cursors to prevent such situation.

Here is important settings from standard cursor declaration syntax:

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

Here is fast human description of cursor options:

[ LOCAL | GLOBAL ] – Use LOCAL, cursor will live only in scope of batch. GLOBAL cursor lives in whole connection, I can’t imagine many scenarios where you really need it.

[ FORWARD_ONLY | SCROLL ] – Use FORWARD_ONLY, cursor will enable only sequential forward only reading. SCROLL cursor enables to move freely forward and backward which require more resources.

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] – Use FAST_FORWARD, it is the combination of READ_ONLY and FORWARD_ONLY cursor. FAST_FORWARD cursor enables only forward and read only movement which makes it most efficient compared to the other types. STATIC cursor loops over copy of data, not over original data hence doesn’t support any updates. Creating copy of data is very resource-intensive. KEYSET cursor requires unique key identifying rows, these keys are copied in tempdb when cursor is opened. It loops then based on those keys. You must have very good reason to use KEYSET cursor. DYNAMIC cursor immediately reflects any changes made to underlying tables.

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] -READ_ONLY is good but doesn’t support any updates. SCROLL_LOCKS option tells cursor to lock every read row to ensure that updates through cursor will succeed.

And few more important points :

• start with LOCAL FAST_FORWARD cursor and then change it if you need something special
• use minimal count of rows and columns within cursor
• think about what you’re doing inside cursor, try to avoid complex SELECT queries inside
• try to avoid STATIC and KEYSET cursors, they work with tempdb which is another overhead
• don’t forget to CLOSE and DEALLOCATE cursor regardless of its scope settings