SQL Server  Alternatives  Cursor

SQL Server Alternatives Cursor

18 Feb 2025
Advanced
64.4K Views
14 min read
Learn with an interactive course and practical hands-on labs

Free SQL Server Online Course with Certificate - Start Today

Cursor Alternative in SQL Server: An Overview

Cursors are used for row-by-row changes, which have a detrimental impact on SQL Server performance due to memory consumption, reduced concurrency, and locking of resources. Avoid using them if possible. This article discusses alternatives such as WHILE loops, temporary tables, and table variables. Cursors should only be used when there are no other feasible options. If you are interested in understanding SQL Server and becoming certified, try taking a comprehensive SQL Server Tutorial or SQL Server Certification Course to improve your understanding of these concepts.

Example of Cursor Alternative

Suppose we have a table "ProductSales" that stores the information about each product sales. Now we want to calculate the Total Sales Quantity and Amount of every product.

We can solve this problem by following three methods.

 CREATE TABLE ProductsSales
(
 ID int IDENTITY(1,1) NOT NULL,
 ProductID int NOT NULL,
 ProductName varchar(50) NOT NULL,
 Qty int NOT NULL,
 Amount decimal(10, 2) NOT NULL ) 
GO
SELECT * FROM ProductsSales
--We have the table with below data 

The code generates a table called ProductsSales that contains columns for ID (auto-incremented), ProductID, ProductName, Quantity, and Amount. It then selects every record from the newly constructed table.

Read More - Top 50 SQL Interview Questions And Answers

Problem solution methods

1. Using Cursor

SET NOCOUNT ON 
DECLARE @ProductID INT 
DECLARE @ProductName VARCHAR(100) 
DECLARE @TotalQty INT 
DECLARE @Total INT 
DECLARE @TProductSales TABLE 
( 
 SNo INT IDENTITY(1,1),
 ProductID INT,
 ProductName VARCHAR(100),
 TotalQty INT,
 GrandTotal INT
 ) 
--Declare Cursor 
DECLARE Cur_Product CURSOR FOR SELECT DISTINCT ProductID FROM ProductsSales
 --Open Cursor 
OPEN Cur_Product 
--Fetch Cursor 
FETCH NEXT FROM Cur_Product INTO @ProductID
WHILE @@FETCH_STATUS = 0
 BEGIN 
SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total) 
FETCH NEXT FROM Cur_Product INTO @ProductID END 
--Close and Deallocate Cursor
 CLOSE Cur_Product 
 DEALLOCATE Cur_Product
 --See Calculated data 
SELECT * FROM @TProductSales 

This code sample defines variables for ProductID, ProductName, TotalQty, and Total and sets NOCOUNT to suppress the "xx rows affected" message. It then defines a table variable @TProductSales, uses a cursor to iterate through different ProductIDs in ProductsSales, calculates the total quantity and amount for each product, inserts the result into @TProductSales, and finally displays the computed data from @TProductSales.

2. Using Table Variable

SET NOCOUNT ON 
DECLARE @ProductID INT 
DECLARE @ProductName VARCHAR(100) 
DECLARE @TotalQty INT 
DECLARE @Total INT 
DECLARE @i INT =1 
DECLARE @count INT 
 --Declare Table variables for storing data 
DECLARE @TProduct TABLE ( SNo INT IDENTITY(1,1),
 ProductID INT
 ) 
 DECLARE @TProductSales TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 ProductName VARCHAR(100),
 TotalQty INT,
 GrandTotal INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID)
 SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
 -- Count number of rows
 SELECT @count = COUNT(SNo) FROM @TProduct WHILE (@i <= @count)
 BEGIN
 SELECT @ProductID = ProductID FROM @TProduct WHERE SNo = @i
 SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
 SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
 INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
 SELECT @i = @i + 1
 END
 --See Calculated data
 SELECT * FROM @TProductSales 

This code sets up variables and table variables to store product data, inserts unique ProductIDs into a table variable, iterates over each ProductID to calculate total quantity and amount, stores the results in another table variable @TProductSales, and finally displays the calculated data.

3. Using a Temporary Table

SET NOCOUNT ON 
DECLARE @ProductID INT 
DECLARE @ProductName VARCHAR(100) 
DECLARE @TotalQty INT 
DECLARE @Total INT 
DECLARE @i INT =1
DECLARE @count INT 
--Create Temporary Tables for storing data 
CREATE TABLE #TProduct ( SNo INT IDENTITY(1,1),
 ProductID INT
 ) 
 CREATE TABLE #TProductSales
 (
 SNo INT IDENTITY(1,1),
 ProductID INT, ProductName VARCHAR(100), TotalQty INT, GrandTotal INT )
 --Insert data to temporary table #Product 
INSERT INTO #TProduct(ProductID) SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
SELECT @count = COUNT(SNo) FROM #TProduct 
WHILE (@i <= @count) 
BEGIN 
SELECT @ProductID = ProductID FROM #TProduct WHERE SNo = @i
SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
INSERT INTO #TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
SELECT @i = @i + 1
 END
 --See Calculated data
 SELECT * FROM #TProductSales 
--Now Drop Temporary Tables
 DROP TABLE #TProduct
 DROP TABLE #TProductSales 

This code disables the row count message, creates the temporary tables #TProduct and #TProductSales, inserts unique ProductIDs into #TProduct, calculates the total quantity and amount for each product, stores the results in #TProductSales, and finally displays the calculated data before dropping the temporary tables.

Read More:

Summary

In this article, I try to explain the Cursor alternative in SQL Server with simple examples. I hope after reading this article you will be able to use the Cursor alternative in SQL Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

FAQs

Temporary tables have been used for a long time and are a great solution to replace cursors in huge data sets. Temporary tables can store the result set, allowing us to do the necessary actions using an iterating algorithm like a 'while' loop.

Cursors may affect performance by using memory, limiting concurrency, and locking resources.

Cursors should only be utilized when there are no other options due to their performance consequences.

WHILE loops are frequently more efficient than Cursors for iterative processes in SQL Server.

In SQL Server, temporary tables and table variables can offer better performance and scalability than cursors. 
 

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at ScholarHat)

He is a renowned Speaker, Solution Architect, Mentor, and 10-time Microsoft MVP (2016–2025). With expertise in AI/ML, GenAI, System Design, Azure Cloud, .NET, Angular, React, Node.js, Microservices, DevOps, and Cross-Platform Mobile App Development, he bridges traditional frameworks with next-gen innovations.

He has trained 1 Lakh+ professionals across the globe, authored 45+ bestselling eBooks and 1000+ technical articles, and mentored 20+ free courses. As a corporate trainer for leading MNCs like IBM, Cognizant, and Dell, Shailendra continues to deliver world-class learning experiences through technology & AI.
Live Training - Book Free Demo
ASP.NET Core Certification Training
06 Sep
08:30PM - 10:30PM IST
Checkmark Icon
Get Job-Ready
Certification
Advanced Full-Stack .NET Developer with Gen AI Certification Training
06 Sep
08:30PM - 10:30PM IST
Checkmark Icon
Get Job-Ready
Certification
Azure AI Foundry Certification Training
06 Sep
07:00AM - 09:00AM IST
Checkmark Icon
Get Job-Ready
Certification
React Certification Training
07 Sep
07:00AM - 09:00AM IST
Checkmark Icon
Get Job-Ready
Certification
Azure Developer Certification Training
08 Sep
08:30PM - 10:30PM IST
Checkmark Icon
Get Job-Ready
Certification
Accept cookies & close this