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 As 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) Begin -- 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 End 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
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.