Remember that cursors can be useful when the problem is a procedural one, and you must deal with each row individually. I provided examples of such scenarios earlier. Here I want to show an alternative to cursors that programmers may use to apply iterative logic, and compare its performance with the cursor code I just demonstrated in the previous section. Remember that the cursor code that scanned a million rows took approximately 20 seconds to complete. Another common technique to iterate through a table’s rows is to loop through the keys and use a set-based query for each row. To test the performance of such a solution, make sure the Discard Results After Execution option in SSMS is still turned on. Then run the following code:

DECLARE @keycol AS INT, @filler AS CHAR(200);

SELECT @keycol = keycol, @filler = filler
FROM (SELECT TOP (1) keycol, filler
      FROM dbo.T1
      ORDER BY keycol) AS D;

WHILE @@rowcount = 1
BEGIN
  -- Process data here

  -- Get next row
  SELECT @keycol = keycol, @filler = filler
  FROM (SELECT TOP (1) keycol, filler
        FROM dbo.T1
        WHERE keycol > @keycol
        ORDER BY keycol) AS D;
END

This implementation is a bit "cleaner" than dealing with a cursor, and that’s the aspect of it that I like. You use a TOP (1) query to grab the first row (based on key order). Within a loop, when a row was found in the previous iteration, you process the data and request the next row (the row with the next key). This code ran for about 90 seconds–several times slower than the cursor code. I created a clustered index on keycol to improve performance by accessing the desired row in each iteration with minimal I/O. Without that index, this code would run substantially slower because each invocation of the query would need to rescan large portions of data. A cursor solution based on sorted data would also benefit from an index and would run substantially slower without one because it would need to sort the data after scanning it. With large tables and no index on the sort columns, the sort operation can be very expensive because sorting in terms of complexity is O(n log n), while scanning is only O(n).

Before you proceed, make sure you turn off the "Discard Results After Execution" option in SSMS.