Wednesday, April 05, 2006

Replacing SQL cursors without Correlated Queries

Handling Cursor-Friendly Problems in T-SQL: Running Totals Example:
A typical "cursor friendly" problem is one where the data set returned contains at least one column whose value depends on column values from one or more previous rows of the same row set.
The article goes on to compare
Cursor - simplest and at the same time almost the most efficient
Correlated Query - simplest and the least efficient

Then by cunning use of 'SET @variable = column = expression' we get the following
Table Variable - most efficient pure Transact-SQL

this has some potential safety issues that are then addressed by derived tables with an order by clause.
Then he uses the following tweaks to make sure all is well
- FORCE ORDER as a join hint
- force an index to ensure correct ordering.

I am still chewing on this one - but I feel the implications could be huge.
your mileage may vary, but I am excited.

No comments:

Post a Comment