Problems and justified use of cursors

The use of cursors in T-SQL should traditionally be avoided when possible, as they are a procedural feature and performace penalties tend to be a problem. However, this excerpt outlines situations in which cursors are acceptable, and even necessary.

Problems with Cursors
Cursors are a valuable but dangerous tool. Their curse is precisely the problem they are designed to solve—the differences between the relational nature of database systems and the record-based nature of client applications.
First of all, cursors are procedural and thus contradict the basic idea behind the SQL language—that is, to define what is needed in a result, not how to get it.

Performance penalties are an even larger problem. Regular SQL statements are set-oriented and much faster. Some...

types of cursors lock records in the database and prevent other users from changing them. Other types of cursors create an additional copy of all records and then work with them. Both approaches have performance implications.

Client-side cursors and API server cursors are also not the most efficient way to transfer information between server and client. It is much faster to use a "fire hose" cursor, which is actually not a cursor at all. You can find more details about "fire hose" cursors in Hitchhiker's Guide to Visual Basic and SQL Server, 6th edition, by William Vaughn (Microsoft Press, 1998).

The Justified Uses of Cursors
The rule of thumb is to avoid the use of cursors whenever possible. However, in some cases, such avoidance is not possible.
Cursors can be used to perform operations that cannot be performed using set-oriented statements. It is acceptable to use cursors to perform processing based on statements, stored procedures, and extended stored procedures, which are designed to work with one item at a time. For example, the sp_addrolemember system stored procedure is designed to set an existing user account as a member of the SQL Server role. If you can list users that need to be assigned to a role, you can loop through them (using a cursor) and execute the system stored procedure for each of them.

Excessive processing based on a single row (for example, business logic implemented in the form of an extended stored procedure) can also be implemented using a cursor. If you implement such a loop in a stored procedure instead of in a client application, you can reduce network traffic considerably.
Another example could be the export of a group of tables from a database to text files using bcp. The bcp utility is a command-prompt program that can work with one table at a time. To use it within a stored procedure, you need to execute it using the xp_cmdshell extended stored procedure, which can run just one command at a time:

Alter Procedure util.ap_Tables_BcpOut
--loop through tables and export them to text files
     @debug int = 0

Declare   @chvTable varchar(128),
          @chvCommand varchar(255)

Declare @curTables Cursor

-- get all USER-DEFINED tables from current database
Set @curTables = Cursor FOR
    select name
     from sysobjects
     where xType = 'U'

Open @curTables

-- get first table
Fetch Next From @curTables
Into @chvTable

-- if we successfully read the current record
While (@@fetch_status = 0)

     -- assemble DOS command for exporting table
     Set @chvCommand = 'bcp "Asset5..[' + @chvTable
                     + ']" out D:backup' + @chvTable
                     + '.txt -c -q -Sdejan -Usa -Pdejan'
     -- during test just display command
     If @debug <> 0
          Select @chvCommand chvCommand

     -- in production execute DOS command and export table
     If @debug = 0
        Execute master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT

     Fetch Next From @curTables
     Into @chvTable


Close @curTables
Deallocate @curTables

Return 0

If you execute this stored procedure (without specifying the @debug parameter), SQL Server will execute the following sequence of command-prompt commands to export tables:

bcp "Asset5..[InventorySum]" out D:backupInventorySum.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[EqType]" out D:backupEqType.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[AcquisitionType]" out D:backupAcquisitionType.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[Action]" out D:backupAction.txt -c -q -Sdejan -Usa -Pdejan
bcp "Asset5..[Contact]" out D:backupContact.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[Contact_with_BC]" out D:backupContact_with_BC.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[EquipmentBC]" out D:backupEquipmentBC.txt -c -q 
-Sdejan -Usa -Pdejan
bcp "Asset5..[Inventory]" out D:backupInventory.txt -c -q -Sdejan 
-Usa -Pdejan
bcp "Asset5..[InventoryProperty]" out D:backupInventoryProperty.txt 
-c -q -Sdejan -Usa -Pdejan
bcp "Asset5..[InventoryXML]" out D:backupInventoryXML.txt -c -q 
-Sdejan -Usa -Pdejan

In Chapter 15, in the "A While Loop with Min() or Max() Functions" section, I will demonstrate another method for looping through a set of records using the While statement. Personally, I seldom use cursors; I prefer to use the method demonstrated in Chapter 15 for operations that cannot be implemented with set operations.

Basic Transact-SQL Programming Constructs

 Home: Introduction
 Tip 1: T-SQL identifiers
 Tip 2: Database object qualifiers
 Tip 3: Character string data types
 Tip 4: Date, time and number data types
 Tip 5: Special data types-Part 1
 Tip 6: Special data types-Part 2
 Tip 7: Local variables
 Tip 8: Global variables
 Tip 9: Table variables
 Tip 10: Flow control statements
 Tip 11: Blocks, looping, and conditional statements
 Tip 12: unconditional and scheduled execution
 Tip 13: Transact SQL cursors
 Tip 14: Cursor related statements
 Tip 15: Problems and justified use of cursors

The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

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