Problems and justified use of cursors

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
...

TIP
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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) SQL Server stored procedures tutorial: Write, tune and get examples How to use rank function in SQL Server 2005 Create a computed column in SQL Server using XML data Using the OUTPUT clause for practical SQL Server applications Create DDL table in SQL Server 2005 to audit DDL trigger activity SQL Server source code analysis and management adds database security SQL and SQL Server Tutorial and Reference Guide Retrieve XML data values with XQuery in SQL Server 2005 How to construct and use SQL OUTER JOINs optimally How to use the LEFT vs. RIGHT OUTER JOIN in SQL SQL/Transact SQL (T-SQL) Research SQL Server stored procedures SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security SQL and SQL Server Tutorial and Reference Guide Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000 XML in SQL Server Manage traces in SQL Server 2005 Analysis Services with XMLA commands Create a computed column in SQL Server using XML data Create DDL table in SQL Server 2005 to audit DDL trigger activity Retrieve XML data values with XQuery in SQL Server 2005 XML data type in SQL Server 2005 vs. VARCHAR (MAX) SQL Server Blog Watch T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers Date, time and number data types

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary