:::: MENU ::::

Server-side cursors

Cursors, dbDigger, T-SQL Interview Questions

Server-side cursors

Question: Of course, we all know that server-side cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.
So, suppose you find yourself in a situation where you do need a cursor, and you also need to change the data retrieved by the cursor – how can you assure maximum performance?

 

Answer: Do not use FOR UPDATE in the cursor declaration, and use WHERE keycolumn = @keyvalue in the update statement

 

Explanation: Though not documented by Microosoft, extensive testing has shown that reading a STATIC cursor with no FOR UPDATE option and using the primary key to update the row just read is faster than specifying a FOR UPDATE option (either with or without a column list) and using the WHERE CURRENT OF clause in the UPDATE statement.

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

Consult us to explore the Databases. Contact us