Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in March 2005

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

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.