SQL Server  Alternatives  Cursor

SQL Server Alternatives Cursor

18 Mar 2024
Advanced
54.7K Views
14 min read

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.

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

Q1. What is the best SQL Server cursor alternative?

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.

Q2. Why should I avoid using cursors in SQL Server?

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

Q3. When should I use cursors in SQL Server?

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

Q4. How do WHILE loops compare to cursors in SQL Server?

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

Q5. What are the benefits of using temporary tables & table variables over cursors?

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

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this