Inside Microsoft® SQL Server™ 2005

Cursor Overhead

In this chapter’s introduction, I talked about the benefits that set-based solutions have over cursor-based ones. I mentioned both logical and performance benefits. For the most part, efficiently written set-based solutions will outperform cursor-based solutions for two reasons.

First, you empower the optimizer to do what it’s so good at–generating multiple valid execution plans, and choosing the most efficient one. When you apply a cursor-based solution, you’re basically forcing the optimizer to go with a rigid plan that doesn’t leave much room for optimization–at least not as much room as with set-based solutions.

Second, row-by-row manipulation creates a lot of overhead. You can run some simple tests to witness and measure this ...