A cursor lets you retrieve data from a result set one row at a time. In the topics that follow, you’ll learn about the different types of cursors that you can use with a SQL Server database. You’ll also learn about the Transact-SQL statements for working with cursors.
A cursor is a database object that points to a result set. You use a cursor to identify the row you want to retrieve from the result set. This concept is presented in the illustration below.
Cursors are most often used by application programs that work with the data in a database. In most cases, these programs use a standard database AN (application programming interface) that provides access to SQL Server databases through cursors. Then, application programmers can work with the programming language and API they’re most familiar with.
Since an AN cursor is managed by the API on the client machine, you don’t need to write any SQL code to support this type of cursor on the server. If you need to, however, you can create and use your own Transact-SQL cursors on the server. You’ll use this type of cursor in one of two ways. First, you can use them within your own scripts or procedures. Second, you can sometimes use them to provide database access to an application program that doesn’t use one of the standard database APIs.
This section also describes two situations when you might code Transact-SQL cursors for your own use. The more likely use is for administrative scripts or procedures that you use to manage your own database. In particular, this is useful for scripts that make use of system stored procedures or system tables to generate dynamic SQL. You’ll see an example of this kind of script later in this chapter. Transact-SQL cursors can also be useful if you need to perform different operations on different rows in a table and the selection criteria are more complex than you can code in a WHERE clause.
Before you use a Transact-SQL cursor to solve a particular problem, you should consider other solutions first. That’s because standard, set-based data-base access is faster and uses fewer server resources than cursor-based access. In some cases, however, you’ll find that a problem can be solved only with the use of a cursor.
Although you’ll probably use Transact-SQL cursors much less often than API cursors, I can’t show you how to use API cursors because each API implements them in a different way. However, I can show you how to code Transact-SQL cursors. Since both API cursors and Transact-SQL cursors work similarly, learning how to code Transact-SQL cursors should prepare you for learning how to use API cursors.
How a cursor works
A result set with a cursor that points to the first row
The first row of the result set retrieved through the cursor
The two implementations of SQL Server cursors
- Cursors implemented through standard database APIs
- Cursors implemented through Transact-SQL
Two common uses for Transact-SQL cursors
- For your own use in scripts and procedures
- For use in procedures that provide database access to application programs that can’t use standard database APIs
When to code Transact-SQL cursors for your own use
- For administrative scripts and procedures that manipulate database objects using dynamic SQL or system stored procedures
- For scripts or procedures that need to do something different to each row in a result set based on criteria that are more complex than can be stated in a WHERE clause
- By default, SQL statements work with all the rows in a result set. If you need to work with data one row at a time, you can use a cursor.
- The common database APIs (application programming interfaces), such as ADONET and ODBC, use cursors to provide application programs access to SQL Server data. Since the API manages the cursor on the client, you don’t need to write any Transact-SQL code on the server.
- Although some programming problems require the use of Transact-SQL cursors, you won’t use them often. Since cursors are slower and take up more system resources than other database access techniques, you should avoid using them whenever possible.
- Transact-SQL cursors are similar to API cursors. If you understand how Transact-SQL cursors work, then, you’ll have a general understanding of how you might work with API cursors.