SQL Server Loop through Table Rows without Cursor

By:   |   Updated: 2019-09-12   |   Comments   |   Related: More > T-SQL

Problem

You have always heard that you should avoid cursors in your T-SQL code as a SQL Server best practice, because cursors are detrimental to performance and sometimes cause issues. But sometimes there is a need to loop through the data one row at a time, so in this tip we will look at a comparison of how to do a loop without using cursor.

Solution

We all know that SQL Server, like every relational database allows the user to perform set based operations. Also, as many database vendors do, SQL Server includes a procedural extension which is the T-SQL language. It adds constructs found in procedural languages allowing a more straightforward coding to developers. These constructs were added for a reason and sometimes this is the only approach to the task at hand.

Using a While Loop Instead of Cursors in SQL Server

If you have ever worked with cursors, you may find this title a bit confusing because after all, cursors uses while constructs to iterate between rows. But besides that, I want to show you that in some circumstances when we use a cursor to iterate over a set of rows we can change it to a while loop. In such cases, the only challenge will be to choose a proper exit condition.

Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server

Not everything is wrong with cursors, they also have some advantages over other looping techniques.

  • Cursors are updatable: When you create a cursor, you use a query to define it using the DECLARE CURSOR instruction. By using the UPDATE option in the cursor creation statement, you can update the columns within the cursor.
  • You can move forward and backward in a cursor: By using the SCROLL option in the DECLARE CURSOR statement you can navigate across the cursor records in both directions with the fetch options FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE. Keep in mind that the SCROLL option is incompatible with the FORWARD_ONLY and FAST_FORWARD options.
  • Cursors can be passed to stored procedures: If you use the GLOBAL option to create a cursor, it can be used in any stored procedure or batch executed in the same connection. This allows you to use cursors on nested stored procedures.
  • Cursors have a lot of different options: With cursors you have the chance to use different options that affects how they will behave in regards to locking.
  • Cursors don’t need a condition: By using cursors, you are handling a set of rows as a record. This allows you to move across the cursor without the need of having a Boolean condition. For example, you can create a cursor with the name of the databases residing on a SQL Server instance without the need of a surrogate key to work as a test condition like on a WHILE loop.

There are also some negative aspects that you should be aware when using cursors instead of other looping options.

  • If you use global cursors in your code you are taking the risk of facing errors due to a cursor being closed by some stored procedure nested in your code.
  • Usually cursors have less performance than an equivalent loop using a WHILE loop or CTE.

Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server

There are also benefits to use a WHILE loop compared to a cursor.

  • While loops are faster than cursors.
  • While loops use less locks than cursors.
  • Less usage of Tempdb: While loops don’t create a copy of data in tempdb as a cursor does. Remember that cursors, depending on the options you use to create them can cause the temp tables to be created.

The next list details the negative aspects of WHILE loops.

  • Moving forward or backward is complex: To move forward or backward in a loop you need to dynamically change the iteration condition inside the loop. This requires extra care; otherwise you can end up in an infinite loop.
  • The risk of an infinite loop: Compared to a cursor, you don’t have a fixed set of data to loop (i.e. the data returned by the SELECT statement in the cursor declaration), instead when using a WHILE loop you have to define a boundary with an expression that is evaluated to true or false.

Building the Test Environment for Cursors and Loops

To test this, I will use a table with an identity column (CursorTestID), a varchar column (Filler) and a bigint column (RunningTotal).

CREATE TABLE CursorTest
(
   CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   Filler        VARCHAR(4000), 
   RunningTotal  BIGINT 
)
GO 

The idea is to loop trough the table rows ordered by the CursorTestID column and update the RunningTotal column with the sum of the CursorTestID column value and the value of the RunningTotal column of the previous row.

But before starting, first we need to generate some test rows with the next script.

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )
VALUES ( REPLICATE('a', 4000),  0  )
GO 500000

On the script above you will notice that I only used a single insert statement and I took advantage of the batch separator (the GO 500000 command) as a shortcut to execute this insert statement 500000 times. You can read more about this method to repeat batch execution on this tip: Executing a T-SQL batch multiple times using GO.

Example of a Basic Cursor to Loop through Table Rows in SQL Server

Let’s create a cursor to fill the RunningTotal column. Notice on the next script that I declared the cursor with the option FAST_FORWARD. This is done in order to enhance the performance of the cursor because according to Microsoft the FAST_FORWARD argument “Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled”. In other words, we are instructing SQL Server to use a read only cursor that can only move forward and be scrolled from the first to the last row.

DECLARE @CursorTestID INT;
DECLARE @RunningTotal BIGINT = 0;
 
DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
    SELECT CursorTestID RunningTotal
    FROM   CursorTest
    ORDER BY CursorTestID;
 
OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @CursorTestID
 
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE dbo.CursorTest 
   SET RunningTotal = @RunningTotal + @CursorTestID
   WHERE CursorTestID = @CursorTestID;

   SET @RunningTotal += @CursorTestID

   FETCH NEXT FROM CUR_TEST INTO @CursorTestID
END
CLOSE CUR_TEST
DEALLOCATE CUR_TEST
GO

The next image is a screen capture showing the execution of the script above. As you can see, it took three minutes and five seconds to update the 500,000 rows of our test table.

Cursor execution test.

Example of a Basic While Loop to Cycle through Table Rows in SQL Server

Now I will rewrite the previous script avoiding the use of a cursor. You will notice that it contains a While loop which is almost identical to the one in the cursor script. This is, as I previously said, because even when working with cursors you need to use an iterative control structure.

DECLARE @CursorTestID INT = 1;
DECLARE @RunningTotal BIGINT = 0;
DECLARE @RowCnt BIGINT = 0;

-- get a count of total rows to process 
SELECT @RowCnt = COUNT(0) FROM dbo.CursorTest;
 
WHILE @CursorTestID <= @RowCnt
BEGIN
   UPDATE dbo.CursorTest 
   SET RunningTotal = @RunningTotal  + @CursorTestID
   WHERE CursorTestID = @CursorTestID;

   SET @RunningTotal += @CursorTestID
    
   SET @CursorTestID = @CursorTestID + 1 
 
END

The next image is a screen capture of the execution of the script above. It took less time to run the while loop than the cursor.

Changing the cursor for a While Loop.

Another SQL Server Cursor Example

Let’s take for example the cursor in the tip Standardize SQL Server data with text lookup and replace function. A word of advice, in order to run this code, you should follow the steps in the tip to create the test environment.

CREATE TABLE dbo.Products 
   (
   ProductID INT IDENTITY(1,1),
   ProductName VARCHAR(100)
   )
GO
 
INSERT INTO Products (ProductName) VALUES ('Sir Rodney''s Marmalade') 
INSERT INTO Products (ProductName) VALUES ('Sir Rodney''s Scones') 
INSERT INTO Products (ProductName) VALUES ('Jack''s New England Clam Chowder') 
INSERT INTO Products (ProductName) VALUES ('Louisiana Fiery Hot Pepper Sauce')
INSERT INTO Products (ProductName) VALUES ('Louisiana Hot Spiced Okra ')

CREATE TABLE [dbo].[Synonyms] ( 
    [synonym] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 
    [word] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
) ON [PRIMARY] 
GO 
 
CREATE UNIQUE INDEX [IX_word] ON [dbo].[Synonyms]([word]) ON [PRIMARY] 
GO 
 
INSERT INTO synonyms VALUES ('Jam','Marmalade') 
INSERT INTO synonyms VALUES ('Chowda','Chowder') 
INSERT INTO synonyms VALUES ('Wicked Hot','Hot') 
INSERT INTO synonyms VALUES ('King','Sir')

And here is the cursor code:

SET NOCOUNT ON 
 
DECLARE @word VARCHAR(50),  
    @position INT,  
    @newProductName VARCHAR(500),  
    @oldProductName VARCHAR(500),  
    @newWord VARCHAR(50), 
    @ProductName VARCHAR(500),
    @ProductID INT 
 
DECLARE load_cursor CURSOR FOR 
    SELECT ProductID, ProductName 
    FROM dbo.Products 
 
OPEN load_cursor 
FETCH NEXT FROM load_cursor INTO @ProductID, @ProductName 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @oldProductName = @ProductName 
    SET @ProductName = LTRIM(RTRIM(@ProductName)) 
    SET @newProductName = @ProductName 
    SET @position = CHARINDEX(' ', @ProductName, 1) 
 
    BEGIN 
         WHILE @position > 0 
         BEGIN 
              SET @word = LTRIM(RTRIM(LEFT(@ProductName, @position - 1))) 
              IF @word <> '' 
              BEGIN 
                SELECT @newWord = NULL 
                SELECT @newWord = synonym FROM Synonyms WHERE word = @word  
                IF @newWord IS NOT NULL 
                BEGIN 
                     SET @newProductName = REPLACE(@newProductName, @word, @newWord) 
                END 
              END 
              SET @ProductName = RIGHT(@ProductName, LEN(@ProductName) - @position) 
              SET @position = CHARINDEX(' ', @ProductName, 1) 
         END 
 
         SET @word = @ProductName 
         SELECT @newWord = NULL 
         SELECT @newWord = synonym FROM Synonyms WHERE word = @word 
         IF @newWord IS NOT NULL 
              SET @newProductName = REPLACE(@newProductName, @ProductName, @newWord) 
    END 
 
    IF @oldProductName <> @newProductName 
    BEGIN 
         SELECT @oldProductName AS OldProductName, @newProductName AS NewProductName
         --UPDATE dbo.Products SET ProductName = @newProductName WHERE ProductID = @ProductID  
    END 
 
    FETCH NEXT FROM load_cursor INTO @ProductID, @ProductName 
END 
 
CLOSE load_cursor 
DEALLOCATE load_cursor 
GO

If we dissect this code, we can see that there is one cursor that goes through the table products which I copied below.

DECLARE load_cursor CURSOR FOR 
    SELECT ProductID, ProductName 
    FROM dbo.Products 

SQL Server Cursor Example Converted to a While Loop

In order to replace this cursor with a WHILE LOOP, we need to create a temporary table to implement a tally table. For all of you who don’t know what a tally table is, we can define it as a table that contains a pair of columns consisting of a key and its value. In our particular case we will use a sequential integer key starting from 1, so we can use it as an iterator. This key will be associated to a ProductID from the Products table.

CREATE TABLE #TallyTable ( Iterator INT IDENTITY(1, 1), ProductID INT );
 
INSERT INTO #TallyTable ( ProductID )
SELECT ProductID FROM dbo.Products;

At first, since the Products table has the ProductID key defined as an identity you may be tempted to bypass this step, but you have to consider that in a real case a row could have been deleted, therefore you won’t be able to use the identity column as an iterator. Additionally a row can be deleted while we are running our code and it could lead to execution errors. To avoid this we are going to add a TRY-CATCH block. I will go into this further on.

Before starting the WHILE loop, we need to set its start and stop condition. For this matter I added two new integer variables named @Iterator and @MaxIterator. The @MaxIterator variable is used to keep the number of items in the #TallyTable table and we set its value only once before starting the loop. The @Iterator variable is initialized to 1, as we defined it as the starting number on the sequence and we are going to increment its value at each iteration.

SET NOCOUNT ON 
 
DECLARE @word VARCHAR(50),  
    @position INT,  
    @newProductName VARCHAR(500),  
    @oldProductName VARCHAR(500),  
    @newWord VARCHAR(50), 
    @ProductName VARCHAR(500),
    @ProductID INT,
    @MaxIterator INT,
    @Iterator INT  
 
CREATE TABLE #TallyTable ( Iterator INT IDENTITY(1, 1), ProductID INT );
   
INSERT INTO #TallyTable ( ProductID )
SELECT ProductID FROM   dbo.Products;
 
SELECT @MaxIterator = MAX(Iterator), @Iterator = 1
FROM   #TallyTable;
 
SELECT @ProductID = ProductID
FROM   #TallyTable
WHERE  Iterator = @Iterator;
 
SELECT @ProductName = ProductName 
FROM dbo.Products 
WHERE ProductID = @ProductID
 
BEGIN TRY
   WHILE @Iterator <= @MaxIterator 
   BEGIN 
      SET @oldProductName = @ProductName; 
      SET @ProductName = LTRIM(RTRIM(@ProductName)); 
      SET @newProductName = @ProductName; 
      SET @position = CHARINDEX(' ', @ProductName, 1); 
 
      BEGIN 
          WHILE @position > 0 
          BEGIN 
              SET @word = LTRIM(RTRIM(LEFT(@ProductName, @position - 1))); 
              IF @word <> '' 
              BEGIN 
 
               SELECT @newWord = NULL; 
               SELECT @newWord = synonym FROM Synonyms WHERE word = @word;  
               IF @newWord IS NOT NULL 
               BEGIN 
                   SET @newProductName = REPLACE(@newProductName, @word, @newWord); 
               END; 
              END; 
              SET @ProductName = RIGHT(@ProductName, LEN(@ProductName) - @position); 
              SET @position = CHARINDEX(' ', @ProductName, 1); 
          END; 
 
          SET @word = @ProductName; 
          SELECT @newWord = NULL; 
          SELECT @newWord = synonym FROM Synonyms WHERE word = @word; 
          IF @newWord IS NOT NULL 
              SET @newProductName = REPLACE(@newProductName, @ProductName, @newWord); 
      END; 
 
      IF @oldProductName <> @newProductName 
      BEGIN 
          SELECT @oldProductName AS OldProductName, @newProductName AS NewProductName;
          --UPDATE dbo.Products SET ProductName = @newProductName WHERE ProductID = @ProductID  
      END; 
 
      SET @Iterator = @Iterator +1;
 
      SELECT @ProductID = ProductID
      FROM   #TallyTable
      WHERE  Iterator = @Iterator;
 
      SELECT @ProductName = ProductName 
      FROM dbo.Products 
      WHERE ProductID = @ProductID;
   END; 
END TRY
 
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber ,
       ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
 
DROP TABLE #TallyTable
GO
Next Steps


Last Updated: 2019-09-12


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools