SQL Server Hints: December 2008

Sunday, December 21, 2008

Five Steps to Building a Cursor

1- Declare the variables that will hold the data you want returned by the cursor.
2- Use the DECLARE CURSOR statement to define the SELECT statement for the cursor.
3- Use the OPEN statement to execute the SELECT statement and populate the cursor.
4- Use the FETCH NEXT INTO statement to retrieve values from the next row and to store the column values for that row in the variables.
5- Issue the CLOSE and DEALLOCATE statements to close the cursor and free all cursor resources.

Four Reasons Why Cursors Are Slow

1- Each FETCH in a cursor typically has the same performance as a SELECT statement. Therefore if the cursor needs to retrieve 1,000 rows, this is equivalent to the same number of SELECT statements.
2- Cursors use large amounts of memory.
3- Cursors can cause locking problems in the database.
4- Cursors consume network bandwidth when the results are sent to the client.

Six Reasons Developers Build Cursors

NU :1
Type of problem : Complex Logic
Description : Can be difficult to translate into a set-based solution due to variable formulas and several exceptions.
Often developed by one of two solutions:
- Code all logic within a cursor loop.
- Create a stored procedure that accepts a single ID, processes asingle row, and then returns thecalculated value or updates the row.
Recommended solution : Refactor the logic as a data-driven query by using a case expression to handle the variations. Use a series of set-based queries so that each query handles one type of formula or logic.
When to use cursors : Very rarely

NU :2
Type of problem : Dynamic code iteration
Description : Must construct and execute dynamic data definition language (DDL) code.
Recommended solution : For this situation, cursors are the best solution.
When to use cursors : Always

NU :3
Type of problem : Denormalizing a list
Description : Converts a vertical list of values to a single comma-delimited horizontal list or string. It is often better to report data as a string than as a sub-report. It is easier to read and saves space.
Recommended solution : Although this is often accomplished with a cursor, itis possible to build a set-based solution.
When to use cursors : Sometimes

NU :4
Type of problem : Building a cross-tab query
Description : Traditionally viewed as very difficult using Microsoft® SQL Server™.
Recommended solution : Building a cross-tab query requires using a series of case expressions. SQL Server 2005 has the new PIVOT syntax, which effectively is the same as a series of case expressions.
When to use cursors : Never Constructing a dynamic cross-tab query requires using a cursor to build the columns for the dynamic SQL

NU :5
Type of problem : Cumulative totals (running sums)
Description : Easily added in the reporting tool but sometimes must be calculated within SQL Server and written to a table. Example: when the cumulative total must be retained for integrity reasons, such as a balance column.
Recommended solution : Although cumulative totals can be determined using a correlated subquery, a cursor actually scales better when solving this problem.
hen to use cursors : Always

NU :6
Type of problem : Navigating a hierarchical tree
Description : Procedural programmers typically approach this task by recursively examining each node.
Recommended solution : Solve using several set-based methods using stored procedures or user-defined functions, which perform an order of magnitude faster than the procedural method.
When to use cursors : Never