Start Learning Free
Previous Tutorial
22
DecSQL Server Alternatives Cursor
18 Mar 2024
Advanced
59.1K 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
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.