Overuse of cursors :

Let's face it – we love loops! Whether we start programming with VB, C, C++, Java, or C#, one of the first constructs we encounter is some form of a loop. They can helpfully solve pretty much any challenge you might face.

And so, it is only natural on the day we start programming with SQL to seek out our favorite loop construct. And here it is – the mighty cursor (and its little WHILE brother)! Then we hurry to put the well known tool to use in solving our problems.

Let's look at one example. Given a table with product prices, we have to perform a monthly update of prices for products; the price updates are stored in another table with new prices.

ProductPrices table:

sku price effective_start_date effective_end_date
---- ------ -------------------- ------------------
1 10.50 2009-01-01 NULL
2 11.50 2009-01-01 NULL
3 19.00 2009-01-01 NULL
4 11.25 2009-01-01 NULL

NewPrices table:

sku price
---- ------
2 11.25
4 12.00

A cursor solution may look like this:

DECLARE @sku INT;
DECLARE @price DECIMAL(15, 2);
DECLARE PriceUpdates

CURSOR LOCAL
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT sku, price
FROM NewPrices;

OPEN PriceUpdates;

FETCH NEXT FROM PriceUpdates
INTO @sku, @price;

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE ProductPrices
SET price = @price, effective_start_date = CURRENT_TIMESTAMP
WHERE sku = @sku;

FETCH NEXT FROM PriceUpdates
INTO @sku, @price;

END

CLOSE PriceUpdates;
DEALLOCATE PriceUpdates;

Mission accomplished! Now we can take a well-deserved break while the query is running. Soon, the realization dawns that procedural row by row processing is not working well in SQL. Besides being very slow, our solution is long, hard to read and maintain. This is the moment we understand the power of SQL is its set-based nature. The same task can be accomplished using a very efficient set-based query that is easier to understand and maintain:

UPDATE ProductPrices
SET price = (SELECT N.price
FROM NewPrices AS N
WHERE N.sku = ProductPrices.sku),
effective_start_date = CURRENT_TIMESTAMP
WHERE EXISTS(SELECT *
FROM NewPrices AS N
WHERE N.sku = ProductPrices.sku);

There are different ways to write a set based query to solve this problem: using the MERGE statement, update with Common Table Expression, or the SQL Server specific update with join. But the point is to utilize the natural power of the SQL language and use set based techniques to solve problems and to avoid procedural solutions.

Note: While you should avoid cursors as much as possible, there are certain problems, such as running total aggregations, that today are still best solved using cursors. We can be optimistic that future enhancements will provide better tools to solve those problems in a set based way.