Picking your cursor in SQL Server

Learn which cursor to use with T-SQL for which situation in Microsoft SQL Server.

Cursors are the way you can work with a results set from a SELECT statement, one row at a time. Without using a

cursor you would loose the results of your SELECT statement if you tried to use any single row. Cursors are created using a Transact-SQL statement at the server, or with a client application such as ODBC, Microsoft Active Data Objects, OLE DB, or any DB-Library programming interface to SQL Server. Cursors can have either GLOBAL or LOCAL scope with GLOBAL being the default.

As a general rule, it's better to use other methods to modify records since cursor operations are slow, but if you need to work through on a row-by-row basis, then cursors are really your best option. SQL Server currently supports several different types of cursors: scrollable cursors, static cursors, dynamic cursors, keyset cursors, and forward-only cursors. You specify which cursor you want in your DECLARE statement by using a modifier such as CURSOR STATIC; if you don't specify which one you want SQL Server defaults to the forward-only cursor.

Having decided that you need to use a cursor, since there are so many options, how do you decide which one to use? First, you will get better data consistency if you lock your records as part of you operation. Also, if your system's performance is a key factor, then create your cursor using either the READ_ONLY or STATIC option. Certainly you should try and keep your code simple and straightforward when you DECLARE your cursor: the simpler the better.

It's a good idea to use a STATIC cursor set with its READ_ONLY modifier if you want to examine but not change the data in your results set. That will run the fastest of the different types of cursors. Should you wish to make modifications to the results set, consider using the DYNAMIC cursor. Finally, the SCROLL_LOCK cursor is useful when you want to prevent others from accessing your results set while you are viewing and altering them. It's important to pay attention to concurrency issues when you are viewing a cursor as others can alter your set while you are viewing them if you don't lock the records appropriately.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.


This was first published in March 2005

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close