SQL Server™ 2005 Bible

Chapter 20. Kill the Cursor!

In This Chapter

• Fetching data with a cursor

• Strategically using or avoiding cursors

• Converting a slow cursor to a high-performance set-based solution

SQL excels at handling sets of rows. However, the SQL world grew out of the old ISAM files structures, and the vestige of looping through data one row at a time remains in the form of the painfully slow SQL cursor.

While there are legitimate reasons to use a cursor, the most common reason is that programmers with a procedural background feel more comfortable thinking in terms of loops and pointers than set-based relational algebra.

SQL cursors also appear deceptively tunable. Programmers see the long list of cursor options and assume these means the cursor can be tweaked for high performance. The types of cursors have names such as fast forward, dynamic, and scrollable. To quote MSDN, "Microsoft(r) SQL Server(tm) 2000 implements a performance optimization called a fast forward-only cursor." The 70-229 SQL Server 2000 Database Design exam even includes a question asking which cursor type is called the firehose cursor. I don't believe there is such a thing. Cursors don't flow, they drip one drop at a time.

The second tier of optimization theory (discussed in Part 6, "Optimization Strategies"), a framework for designing high-performance systems, is developing set-based code, rather than iterative code. While this chapter explains how to iterate through data using a cursor, the emphasis is clearly on strategically ...