Problem
You have always heard that you should avoid cursors in your T-SQL code as a SQL Server best practice. This is because cursors could be detrimental to performance and sometimes cause issues. But sometimes there is a need to loop through the data one row at a time in your SQL queries. In this SQL tutorial 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 use 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 as FOR loops are not available in T-SQL. 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.
Negative Aspects of Cursors
There are also some negative aspects that you should be aware when using cursors instead of other looping options.
- If you use global cursors, you are taking the risk of 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 the structure below. Here is the SQL statement with the table name, column names and data types:
CREATE TABLE CursorTest
(
CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- Column Names
Filler VARCHAR(4000), -- Data Types
RunningTotal BIGINT
)
GO The idea is to loop trough the table rows ordered by the CursorTestID column. Then 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 Transact-SQL script.
INSERT INTO dbo.CursorTest ( Filler, RunningTotal )
VALUES ( REPLICATE('a', 4000), 0 )
GO 500000On the script above I used a single insert statement with the batch separator (GO 500000) to execute this insert statement 500000 times. You can read more about this method here Executing a T-SQL batch multiple times using GO.
Simple 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 following example 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. Here is the syntax:
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
GOThe next image is a screen capture from SQL Server Management Studio (SSMS) 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.
Example of a Basic SQL While Loop to Cycle through Table Rows in SQL Server
Now I will rewrite the previous script avoiding the use of a cursor with a WHILE loop statement. You will notice that it contains a While loop which is almost identical to the statement block in the cursor script where it gets a count of rows with SELECT COUNT and processes row by row with a row number. 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 with a SELECT COUNT statement
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
ENDThe next image is a screen capture from SSMS of the execution of the loop example above. The result set shows it took less time to run the while loop than the cursor.
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
GOIf 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 -- WHERE ID equals a variable
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
GONext Steps
- Are you new to cursors and need some practice? In the next tip you will find an explanation, an easy to understand cursor example and more recommended readings: SQL Server Cursor Example.
- If you want to convert the existing cursors in your code to set based queries, take a look at this chapter SQL Server Convert Cursor to Set Based from the SQL Server Database Design Best Practices Tutorial.
- Do you need another example on using a While loop? Take a look at this tip that will show you how to split DML statements in batches: Optimize Large SQL Server Insert, Update and Delete Processes by Using Batches.
- In case you don’t know how to use TRY…CATCH exception handling, take a look at this tip: SQL Server Try and Catch Exception Handling.
- Stay tuned to the SQL Server T-SQL Tips category to get more coding ideas.
