MS SQL Server

SQL Server FOR Loop Alternatives

SQL Server provides us with alternatives to the traditional “for” loop which offers more efficient and optimized ways to iterate over the data sets and perform iterative operations. These alternatives for “for” loops enable an efficient iteration and data manipulation.

Let us discuss about some important SQL server “for” loop alternatives. We will talk about the “while” loop which enables the iterative operations. We will also discuss about the “tally” table which is a powerful mechanism to generate sequential numbers. After understanding the strengths and use cases of each alternative, the readers can decide the appropriate method to use. These alternatives not only enhance the code readability and maintainability but also improve performance and efficiency in the SQL Server data manipulation.

Create a Table for Demonstration

Let us create a sample table to better illustrate the different SQL Server “for” loop alternatives. Let’s first create a table called “Employees” with columns such as “EmID”, “Name”, and “Salary”.

CREATE TABLE Employees (
    EmID INT,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2)
INSERT INTO Employees (EmID, Name, Salary)
    (1, 'Raj Misha', 50000.00),
    (2, 'Amit Nath', 60000.00),
    (3, 'Sneha Paul', 55000.00),
    (4, 'Priya Dutta', 48000.00),
    (5, 'Anil Kumar', 52000.00),
    (6, 'Meera Kumari', 57000.00);


EmID                 Name           Salary
1       Raj Misha       50000.00
2       Amit Nath       60000.00
3       Sneha Paul      55000.00
4       Priya Dutta     48000.00
5       Anil Kumar      52000.00
6       Meera Kumari        57000.00

Some SQL Server FOR Loop Alternatives

1. WHILE Loop in the SQL Server

A very popular alternative to “for” loop in the SQL Server is the WHILE loop. We can effectively iterate over a result set or perform iterative operations on a set of data using the WHILE loop. For example, we want to raise each employee’s pay by 10% in the table named “Employees”. We can perform this using a WHILE loop.

DECLARE @Counter INT = 1;

DECLARE @TotalRows INT = (SELECT COUNT(*) FROM Employees);

WHILE @Counter <= @TotalRows


    DECLARE @Salary DECIMAL(10, 2);
    -- Retrieve EmID and Salary for the current row
    SELECT @EmID = EmID, @Salary = Salary
    FROM Employees
    WHERE EmID = @Counter;
    -- Increase the salary by 10%
    SET @Salary = @Salary * 1.1;
    -- Update the salary for the current employee
    UPDATE Employees
    SET Salary = @Salary
    WHERE EmID = @EmID;
    -- Print the updated salary
    PRINT 'Updated salary for EmID ' + CAST(@EmID AS VARCHAR) + ': ' + CAST (@Salary AS VARCHAR);
    -- Increment the counter
    SET @Counter = @Counter + 1;



** many more outputs **

Updated salary for EmID 1: 60500.00

Updated salary for EmID 2: 72600.00

Updated salary for EmID 3: 66550.00

** many more outputs **

Updated salary for EmID 4: 58080.00

Updated salary for EmID 5: 62920.00

Updated salary for EmID 6: 68970.00

Here, it iterates over the rows in the “Employees” table and updates the salary by 10% and continues until all rows are updated.

2. Using the Tally Table in the SQL Server

The “tally” table, sometimes referred to as the “numbers” table, is an effective method that includes generating a list of consecutive numbers into a table. The creation of iterative sequences or the execution of set-based operations may then be done using this table. Using the tally table method, we can eliminate the need for “for” loops in the SQL Server. To get the names of all employees, we can perform this using a “tally” table.

-- First Create a tally table

CREATE TABLE Tally (Number INT);

DECLARE @MaxNumber INT = (SELECT MAX (EmID) FROM Employees);


    FROM sys.all_columns AS AC1
    CROSS JOIN sys.all_columns AS AC2


INSERT INTO Tally (Number)



WHERE No <= @MaxNumber;

-- Retrieve employee names using the tally table


FROM Employees AS E

JOIN Tally AS T ON E.EmID = T.Number;

--At last, Drop the tally table



1   Raj Misha
2   Amit Nath
3   Sneha Paul
4   Priya Dutta
5   Anil Kumar
6   Meera Kumari

Here, we create a “tally” table with a single column named “Number” in the T-SQL query. By determining the maximum number of employees from the “EmID” column in the “Employees” table, we then create a sequence of numbers from 1 to the maximum using the variable “K”. This variable “K” assigns a new value to each row using the “ROW_NUMBER ()” function. After that, we insert then these generated numbers into the “tally” table, limiting the insertion to numbers that are less than or equal to the maximum employee number. Lastly, we join the “Employees” table with the “tally” table based on the equality of the “EmID” and “Number” columns.


SQL Server provides several alternatives to the “for” loop and offers more efficient and optimized ways to iterate over the data sets and perform iterative operations. The WHILE loop allows us to execute the code repeatedly based on a specific condition, providing flexibility for iterative tasks. Moreover, the use of a tally table eliminates the need for loops. Using these alternatives methods, we can improve our SQL Server skills and achieve a more efficient data manipulation.

About the author

Somdeb Nath

Somdeb Nath is a highly accomplished programmer with expertise in C, Java, Python, SQL, HTML, CSS, Android, Linux and JavaScript. He has authored 100+ high-quality articles on programming languages. With strong problem-solving skills and a passion for technology, Somdeb is dedicated to continuous learning. Engage with him on LinkedIn: