Tip

Picking your cursor in SQL Server

Barrie Sosinsky, Contributor

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.