… As a continuation of my occassional slam on T-SQL cursors, I want you to take just a few minutes to read this blog post by the great Paul Randal. It is brief, but includes screen shots from the execution plans that shows more odd T-SQL cursor behavior. Because he used a dynamic optimistic cursor type, he kept getting keylookups in his query plans, despite the fact that he has indexes on the fields in his queries. This is because the cursor is maintaing a worktable in TempDB that it is using lookups against. This is a very good example of why using sets in T-SQL is much preferable than relying on cursors.
A topic that I have run into a lot in the past of the years, particularly when working with Oracle PL/SQL developers and when coverting Oracle applications to T-SQL for SQL Server, is cursors. I find that Oracle developers lean toward using cursors often from writing Pro*C and stored procedures. When you move to SQL Server programming, though, you’ll find much better performance in most cases by moving to set-based operations and being very careful utilizing cursors and to make sure that they are optimized using best practices.
I was reading a very good series on SQL Server Central this week by R Barry Young and am linking you to it here. If you absolutely, postively need to work with cursors, perhaps to fetch rows individually and perform unique operations row by row, then this article will also help you guide for what areas of cursor optimization to look for.