SQL Cursor vs WHILE loop

Question

When you need to loop a set of data, is there a benefit of using a SQL Server cursor. Or is using a WHILE loop (see below) just the same.

I ask because the WHILE loop seems clearer and easier to understand.

-- loop through a table
DROP TABLE IF EXISTS #LoopingSet;
CREATE TABLE #LoopingSet (RowID INT IDENTITY(1,1), DatabaseName sysname);
INSERT INTO #LoopingSet (DatabaseName) SELECT [name] FROM sys.databases WHERE database_id > 4 ORDER BY name;
DECLARE @i INT = (SELECT MIN(RowID) FROM #LoopingSet);
DECLARE @n INT = (SELECT MAX(RowID) FROM #LoopingSet);
DECLARE @DatabaseName sysname = '';
WHILE (@i <= @n)
BEGIN
 SELECT @DatabaseName = DatabaseName FROM #LoopingSet WHERE RowID = @i;
 PRINT @DatabaseName; -- do something here
 SELECT @i = MIN(RowID) FROM #LoopingSet WHERE RowID > @i;
END;

• sTTu: You have duplicated this question here.

Answer 1

What you showed here, is actually how cursor works. You just repeated the inner workings of a cursor with a set of SQL statements.

The first problem I see here, is that the source table can have indexes, and once you get record set into temp table, you loose them. A cursor will use original indexes of the source table. But you wont have any access to it anymore.

You would need to create an index on RowID. SQL Server does not create an index on identity fields automatically (it probably should, but does not). So you need to do it yourself. That is the only mistake I see in your sample code.

Also, I would expect to see a minor performance loss with your code (comparing to cursor), but just because the cursor is already compiled/optimized and your code has two independent select statements over temp table. Difficult to say how much the performance loss would be, but you will have it.

But in general - this is how cursor works. So you can use this in your code.

But if I were your teammate - I would refactor it back to cursors. Primarily to be in line with a "standard SQL approach of doing things". Cursor's syntax is not that hard and during any maintenance you will instantly see "aha, this is cursor!". Your code on the other hand, have to be read and thought about, to recognize what algorithm is implemented here.

• Craig: Thanks for the helpful advice.
• Sean Lange: If performance is even a discussion point then the looping concept should be thrown out. Looping should be used very very rarely, and even then it is likely a loop is not the best solution.
• J.D.: @SeanLange by "looping concept" I hope you mean cursors inclusive in that statement too.
• Sean Lange: @J.D. indeed. That was the entire point of my comment. It is highly likely that neither a cursor or a while loop is needed. They are often abused.
• Sean Lange: @KumarHarsh yes there may be times you can't avoid loops but those are very rare. Some administrative tasks and sending emails are about it. In your doctor analogy I would like to get a second opinion before I proceed.

Answer 2

There can be situation ,where looping(RBAR) is the only option. There can be requirement like looping through each row of table is require. In that case we can use Cursor instead of While Loop.

Also if it involve,millions of data then performance wise Cursor out perform While

WHILE loop seems clearer and easier to understand.

Read about Cursor, their type,locking,scope etc. It is easy to use and understand.Use cursor type,locking scope suiting your requirement will enhance the performance.

Also Paul White has already explain along with syntax.This is what I meant.