SQL Server Best Practices – Series 2

SQL Server Best Practices – Series 2

1.3 Cursors

The use of cursors is often the single biggest cause of slow stored procedures, and is a major cause of transaction blocking and other scalability issues. It is very important that everyone follow these standards when writing stored procedure code. These standards apply equally well to all DBMSs and all versions of them, however, certain keywords may not be available on older DBMSs.

1.3.1 Do you actually need a cursor?

The most basic rule is: try your utmost to avoid having to declare a cursor.

If you want to retrieve only the first row of a query, use a SELECT TOP 1 query instead, or apply extra criteria to ensure only the row you are interested in is returned.

Try breaking down a complex algorithm into smaller parts, and storing intermediate results in a temporary table using an INSERT INTO … SELECT statement.

A good rule-of-thumb is: if you don’t have a loop, you don’t need a cursor.

As a last resort, ask your Team Lead or the Database Owner for advice.

1.3.2 Writing an efficient cursor

The only recommended cursor type is FAST_FORWARD. This uses the absolute minimum of server resources and does not interfere with SQL Server’s tempdb database. Example:

DECLARE <cursor name> CURSOR FAST_FORWARD FOR

<select statement>

Be aware that this cursor type only allows the use of FETCH NEXT, and does not allow updates using WHERE CURRENT OF.

If you think you need to use WHERE CURRENT OF, then all you have to do is read the table’s primary key field(s) in the cursor, then update or delete WHERE <primary key> = <value> instead of updating the cursor itself. This is also faster.

I have seen a lot of stored procedure code open a cursor, do a FETCH FIRST, and then loop through using FETCH NEXT for all remaining rows. Be aware that FETCH NEXT will quite happily fetch the first row, so using FETCH FIRST is not necessary.

If you still need to scroll around the cursor using anything other than FETCH NEXT, please re-consider your algorithm. It should always be possible to re-write it so that it only scrolls forwards. See the Database Owner for advice on this if you can’t see how to do it.

Don’t forget to CLOSE and DEALLOCATE the cursor when you are finished with it.

Regards

Bhagwan Singh Jatav

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook