SQL Server Cursors are, by themselves, neither good nor bad. But they are highly inefficient in the SQL world because SQL Server really works best with SET data. Having said that, cursors are very familiar to programmers, especially VB programmers who have learned to process record sets one row at a time in a FOR or WHILE loop.

I once was working on a consulting gig where a majority of my time was spent in code reviews as a DBA. One day my boss walks in absolutely livid about a code review she'd just been to. The major transgression the programmer had made was making a change to a stored procedure and not updating the comments to reflect it was his change and not hers (as she was a highly qualified DBA and wouldn't do something like this) but his second transgression takes a little explaining. He had rewritten a stored procedure that returned a single row of data to use output parameters. That, in and of itself, is a good thing since it cuts down on the overhead between SQL Server and the client. What my boss really couldn't understand is why the programmer had opened a cursor to populate the output parameters.

It really was a simple explanation. If he had been writing VB code, he would have opened a recordset, read the column information into the variables and returned them. He didn't know you could SELECT @variablename = columnvalue FROM tablename WHERE filter, so he wrote his SQL code the same way he wrote his VB code. Once I took a few minutes to calm the boss down I went out and had a quick talk with him and he quickly understood and had few problems from that point on.

Still, there are times when SET based operations seem to not work. If you have several million records and need to add and populate a new column in the table, just executing:

ALTER TABLE tablename ADD
    NewColumn nvarchar(50) NULL
GO

UPDATE tablename SET NewColumn = OldColumn + OtherData
GO

Can be horrendously slow. It's possible that you're going to end up with lots of page splits and other horrendous performance problems and it's going to create a huge Transaction Log and could block the table. So, naturally, some folks turn to a cursor to fix this. Which is bad for a couple of reasons.

First, even with a READ ONLY cursor, you're going to be using up server memory to hold the cursor while you process several million individual records. Second, it's going to take a long time to process. And third, it can be a pain to restart. But there's an alternative method, using the WHILE EXISTS statement.

The initial way to do the update is to write something like:

WHILE EXISTS(SELECT * FROM tablename WHERE NewColumn  IS NULL)
UPDATE tablename SET NewColumn = OldColumn + OtherData WHERE PrimaryKey IN
  (SELECT TOP 1000 PrimaryKey FROM tablename WHERE NewColumn  IS NULL)

Some notes: Index the NewColumn. You may delete the index when you're done, but if you don't, SQL Server will likely generate the index for you and it's better to have control over it. Also, execute the UPDATE statement by itself, see how long it takes to run. Adjust the number of rows returned in the IN clause. I'm running an update right now on 10,000,000 rows and it looks like 25,000 is the number that balances out the time it takes to run the update with the number of rows being updated. 50,000 records takes 25 seconds. 5,000 takes 3. 25,000 takes 10 so I settled there. I don't need to fine tune it too much since this is a one-off operation.

So, next time you're tempted to use a cursor, ask yourself if a WHILE EXISTS clause will work just as well.

Later this week I'll post about cursors and talk about valid and invalid uses.