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 language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server SQL/Transact SQL (T-SQL) Research SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure XML in SQL Server Processing XML files with SQL Server functions Top 10 SQL Server development tips of 2008 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) T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) Collaboration Data Objects  (SearchSQLServer.com) commit  (SearchSQLServer.com) container  (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