How to loop through table rows without cursor in SQL Server?

When writing business logic in a stored procedure, sometimes you may need to process the tables row-by-row. You might have used CURSORs to perform such tow-by-row operations. However, I do not prefer to use cursors as they are slow and will impact performance. As a good practice I always try not to use cursors in my sql code. But, how to loop through table rows without a cursor? Let’s see with a simple example.

Example of Cursor

In this example, I have used a cursor to loop through 134,460 records from the table called Cities. Then for this illustration I have printed the data from the individual records. (Of course you do not need a cursor for this operation. However this is just for illustration.)

Number of rows in city table
DECLARE @CityID INT;
DECLARE @CityName NVARCHAR(50);
DECLARE @Population BIGINT;

DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
   SELECT CityID FROM Cities

OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @CityID
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @CityName = CityName, 
      @Population = Population 
      FROM Cities WHERE CityID = @CityID

   PRINT 'Population of ' + @CityName + ' is ' + 
      CAST(@Population AS NVARCHAR)

   FETCH NEXT FROM CUR_TEST INTO @CityID
END

CLOSE CUR_TEST
DEALLOCATE CUR_TEST
GO
Loop Through Table Rows With Cursor

The time taken by the fast forward cursor to complete the operation is 8 seconds.

Converting Cursor to While Loop

Now, let us convert the above example to WHILE loop. To convert a cursor to while loop, first you have to find the total number of rows in the table. Then you have to iterate through the table rows using WHILE control-of-flow element till the total row count is reached. Here is how the above example is converted to while loop:

DECLARE @RowCnt INT;
DECLARE @CityID INT = 1;
DECLARE @CityName NVARCHAR(50);
DECLARE @Population BIGINT;

SELECT @RowCnt = COUNT(*) FROM Cities;

WHILE @CityID <= @RowCnt
BEGIN
   SELECT @CityName = CityName, 
      @Population = Population 
      FROM Cities WHERE CityID = @CityID

   PRINT 'Population of ' + @CityName + ' is ' + 
      CAST(@Population AS NVARCHAR)

   SET @CityID += 1
END
GO
Loop Through Table Rows Without Cursor

If you notice, the while loop took 6 second to complete the operation. When you are working with complex operations, you will notice the while loop is much faster than the fast forward cursor.

Reference


Related Articles & Tools

1 thought on “How to loop through table rows without cursor in SQL Server?”

  1. The method of not using a cursor is incorrect and will not work for the general case.
    1: If any CityId has been deleted, you’ll get an error for that row.
    2: If cities are being actively added/deleted by other users you’ll get incorrect results.
    3: If your ID is not an identity / monotonically increasing number you cannot use this method.

    It is almost always better to use a cursor for your operations.

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.