Good editorial on an important topic.

To me, cursors seem to be the most thoroughly-reviled feature in SQL Server. It's as though people are trying to believe that the assembly and C++ code (oh my! it's not all managed code) underlying the SQL Server engine is ... set oriented?

The first person I ever met who worked on the SQL team had the following job description: optimizing server-side cursors. How ironic.

Anyway, a lot of times when a pill-swallowing newbie, or a really excellent veteran, makes the statement that "cursors are always bad, always perform worse than well-chosen set-based logic, and should always be considered for elimination," I love asking them this question. "If that is the case, then why do so many of the fastest benchmarks posted at tpc.org use lots of cursors?" Their response is usually the very articulate, "um ... really?"

I also love telling people this statement: "there is always a cursor." It makes some people feel violated to know this, but hey, eventually, the truth will set you free, not myths. There is always a cursor defined on the database engine to handle your data retrieval and other DML. So when I write a background job using a cursor to process 5 million rows in the background, throttling the throughput so that we don't overwhelm our somewhat fragile replication infrastructure, I am often reviled. However, people who know me and have worked with me suspect that there is a method to my madness, so of course they revile me politely. I love telling them, as a loving retort, that when they use while loops to emulate cursors, they may be invoking a separate server-side cursor for each fetch (oh forgive me! I mean SELECT ... or is it the same thing?) at the top of each loop iteration. So while I know I am working with one server-side cursor, they may be working with millions of server-side cursors. The irony is deep here, since their goal, of course, is to write code that minimizes cursor usage.

Please don't read any animosity into this post. I just think this is a really amusing topic that we as a community do not understand very well at all. But, as Phil points out, there are plenty of prophets crying out in the wilderness, railing against the evils of cursors. Moreover, we have plenty of use cases where an explicitly-declared cursor is obviously a ridiculous idea. However, I do think there is a real group-think mentality going on that prevents earnest technical discussion of what is a more complex topic than we, as a community, care to admit. So if anything is frustrating to me about the discussion of cursors, or several other of these SQL Profanities, it is that trying to discuss them online, even in excellent communities like this one, too often turns into a flurry of bumper stickers: "Cursors are bad!" "Never use dynamic SQL!" "Triggers are slow!"

Alas, I am an old man and I tire easily of such bumper-sticker exchanges. After 20 years dedicated to nothing but database work, and having been designated a SQL Server SME by Microsoft themselves, the only blanket advice I would give in this context is as follows:

Cheers,

Chris