Crthuky

I'd like to know what the general replacement is for a cursor. The general implementation of a cursor I see out and about is

DECLARE @variable INT, @sqlstr NVARCHAR(MAX)

DECLARE cursor_name CURSOR
FOR select_statement --essentially to get an array for @variable 
 --usually it's a subset of unique ids for accounts, clients, parts, etc

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sqlstr = N'
 --some query that uses '+ str(@variable) +' to do dirty work
 '

 EXEC sp_executesql @sqlstr
FETCH NEXT FROM cursor_name INTO @variable
END

CLOSE cursor_name
DEALLOCATE cursor_name

Since so many people are anti-cursor (with a nod to SO: Why do people hate cursors) what is the general replacement for the general implementation (preferably SQL Server)?

There is no "general replacement" - you hid all the "dirty work" here so it's hard to tell if there is even a specific replacement in this case. There are certainly some specific cases where you're processing a set of rows one row at a time, whether using a cursor, or while loop, or any other iterative process, where converting to a set-based process that processes all the rows at once is much better. But there are other things that just have to be done one row at a time, like executing a stored procedure or some dynamic SQL per row, the same query across multiple databases, etc.

Cursor or not, the problems you're alluding to and linking are the same whether you use declare cursor or some other looping struct (see this post), and are irrelevant when the thing you have to do has to be done one row at a time anyway. So if you provide some specific details about what this cursor is doing, you might get some advice about how to remove the cursor (or that you can't), but your search for a magical eliminate-all-cursors approach that you can apply to all scenarios is going to be pretty frustrating for you.

It depends™

The ability to work around one or multiple cursors, will depend on what is going to be executed inside of this cursor. Without knowing what is going on in it, there is no way to tell. It could be that there is no workaround, and you have to do row by row processing.

Below are a some examples.

Not working in sets

This example is the most basic one, and is simply the fact that you could query your entire dataset or parts of your dataset at once, but the cursor was created and is querying the data row by row. Common ones to replace this with are JOIN's, CROSS APPLY / OUTER APPLY and others.

Consider the following data set:

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring')
,(2,'Gandalf','Staff');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3')
,(2,4,'StaffAttribute1')
,(2,5,'StaffAttribute2');

One could try and find each record and it matches separately, by looping over the Lotr Table.

Cursor:

DECLARE @LotrID int
DECLARE C CURSOR FOR SELECT LotrId from dbo.Lotr;
OPEN C
FETCH NEXT FROM C INTO @LotrID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT LotrATtributeId from dbo.LotrAttributes where LotrId = @LotrID;
FETCH NEXT FROM C INTO @LotrID;
END
CLOSE C
DEALLOCATE C

Resulting in two result sets

LotrATtributeId
1
2
3
LotrATtributeId
4
5

When this inner join is used, we get the same result as one resultset.

SELECT LotrATtributeId from dbo.Lotr L
INNER JOIN dbo.LotrAttributes LA 
ON L.LotrId = LA.LotrId;

LotrATtributeId
1
2
3
4
5

String Manipulation

A common one is to use FOR XML PATH('') to replace string manipulations inside of cursors.

Dataset

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3');

Double cursor with string manipulation

DECLARE @LotrId int, @CharacterName varchar(255), @Val varchar(255)
DECLARE @LotrATtributeId int, @AttrVal varchar(255)
DECLARE C CURSOR FOR
SELECT LotrId,CharacterName, Val FROM dbo.Lotr
OPEN C
FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @CharacterName +='|'+ @Val

 DECLARE D CURSOR FOR
 SELECT LotrATtributeId, AttrVal FROM dbo.LotrAttributes where LotrId = @LotrId
 OPEN D
 FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @CharacterName +='['+@AttrVal+ '],'

 FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
 END
 CLOSE D 
 DEALLOCATE D

FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
END
CLOSE C
DEALLOCATE C
SELECT LEFT(@CharacterName,len(@charactername)-1);

Result

(No column name)
Frodo|Ring[RingAttribute1],[RingAttribute2],[RingAttribute3],

Removing the cursors with FOR XML PATH('')

SELECT L.Charactername +'|'+ L.Val + (SELECT stuff((SELECT ','+QUOTENAME(AttrVal) FROM dbo.LotrAttributes LA WHERE LA.LotrId = L.LotrId FOR XML PATH('')), 1, 1, ''))
FROM
dbo.Lotr L;

*

The real workaround here would be figuring out why the data is presented in this manner, and changing the application/... as to not need it in this format, storing it somewhere, ....

If your hands are tied, this would be the next best thing.

Insert top 10 values into a temp table based on Id's in another table

Data

CREATE TABLE dbo.sometable(InsertTableId int, val varchar(255));
CREATE TABLE dbo.Top10Table(Top10TableId int, InsertTableId int, val varchar(255));

INSERT INTO dbo.sometable(InsertTableId,val)
VALUES(1,'bla')
,(2,'blabla');
INSERT INTO dbo.Top10Table(Top10TableId,InsertTableId,Val)
VALUES(1,1,'WUW')
,(2,1,'WUW')
,(3,1,'WUW');

Cursor

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255))

 DECLARE @InsertTableId int;
 DECLARE C CURSOR FOR select InsertTableId from dbo.sometable;
 OPEN C
 FETCH NEXT FROM C INTO @InsertTableId;
 WHILE @@FETCH_STATUS =0
 BEGIN
 INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
 SELECT top(10) Top10TableId,InsertTableId,Val FROM dbo.Top10Table 
 where InsertTableId = @InsertTableId
 ORDER BY Top10TableId 

 FETCH NEXT FROM C INTO @InsertTableId;
 END
 CLOSE C
 DEALLOCATE C

 SELECT * FROM #Top10Values;
 DROP TABLE #Top10Values;

Result

Top10TableId InsertTableId val
1 1 WUW
2 1 WUW
3 1 WUW

Replacing the cursor with CROSS APPLY and a CTE

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255));
;WITH CTE 
AS
(
select InsertTableId from dbo.sometable
)

INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
SELECT T1T.Top10TableId,T1T.InsertTableId,T1T.Val 
FROM 
CTE
CROSS APPLY (SELECT TOP (10) Top10TableId,InsertTableId,Val from dbo.Top10Table T1T
WHERE T1T.InsertTableId = CTE.InsertTableId
) T1T ;

SELECT * FROM #Top10Values;
DROP TABLE #Top10Values;

Other examples

• An example on replacing a cursor to select a dynamic set of items per
  Supplier by using CROSS APPLY here.


• An example on using windowing functions to replace a cursor here.

Sometimes there is no other choice

If you cannot work in sets, and have to do row by row processing, you could still optimize the cursor.

One of the biggest changes in speeding up the cursor is by adding LOCAL FAST_FORWARD to it.

DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT LotrId from dbo.Lotr

Take a look at this blogpost by @AaronBertrand where he explains the possible differences in performance when using or not using cursor settings like LOCAL & FAST_FORWARD.