Cursor Replacement for NewbiesImprove a query using cursor to SET Based approachReplacing a Cursor with a single Query possible?Optimization around static cursor usehow cursor implementations are different for each cursor type in sql serverSave a dynamic cursor for reuse?SQL SERVER 2008 Cursor ProblemUpdate Using a cursorWhy is the query_hash zero for EXEC statements?FOR UPDATE clause allowed only for DECLARE CURSORMaintenance plan using cursor statementSet cursor rows commandMysql database table cursor

Is it tax fraud for an individual to declare non-taxable revenue as taxable income? (US tax laws)

To string or not to string

Minkowski space

Theorems that impeded progress

What is the offset in a seaplane's hull?

What are these boxed doors outside store fronts in New York?

Is it important to consider tone, melody, and musical form while writing a song?

Accidentally leaked the solution to an assignment, what to do now? (I'm the prof)

Is this a crack on the carbon frame?

What's the output of a record cartridge playing an out-of-speed record

Can divisibility rules for digits be generalized to sum of digits

How does one intimidate enemies without having the capacity for violence?

Test if tikzmark exists on same page

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Writing rule which states that two causes for the same superpower is bad writing

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Smoothness of finite-dimensional functional calculus

Languages that we cannot (dis)prove to be Context-Free

What are the differences between the usage of 'it' and 'they'?

Is it possible to do 50 km distance without any previous training?

Do VLANs within a subnet need to have their own subnet for router on a stick?

Why did the Germans forbid the possession of pet pigeons in Rostov-on-Don in 1941?

The magic money tree problem

In Japanese, what’s the difference between “Tonari ni” (となりに) and “Tsugi” (つぎ)? When would you use one over the other?



Cursor Replacement for Newbies


Improve a query using cursor to SET Based approachReplacing a Cursor with a single Query possible?Optimization around static cursor usehow cursor implementations are different for each cursor type in sql serverSave a dynamic cursor for reuse?SQL SERVER 2008 Cursor ProblemUpdate Using a cursorWhy is the query_hash zero for EXEC statements?FOR UPDATE clause allowed only for DECLARE CURSORMaintenance plan using cursor statementSet cursor rows commandMysql database table cursor






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








4















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
such as: go through all our accounts, if it''s some subset (possible new cursor),
go through those accounts and connect this way,
map those fields and add it to our big uniform table */
'

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)?










share|improve this question






























    4















    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
    such as: go through all our accounts, if it''s some subset (possible new cursor),
    go through those accounts and connect this way,
    map those fields and add it to our big uniform table */
    '

    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)?










    share|improve this question


























      4












      4








      4


      0






      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
      such as: go through all our accounts, if it''s some subset (possible new cursor),
      go through those accounts and connect this way,
      map those fields and add it to our big uniform table */
      '

      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)?










      share|improve this question
















      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
      such as: go through all our accounts, if it''s some subset (possible new cursor),
      go through those accounts and connect this way,
      map those fields and add it to our big uniform table */
      '

      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)?







      sql-server cursors






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 2 days ago







      undrline

















      asked Apr 3 at 20:27









      undrlineundrline

      366




      366




















          3 Answers
          3






          active

          oldest

          votes


















          6














          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.



          The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.



          Your question still asks for a general replacement, and I still believe there is no such thing.






          share|improve this answer

























          • Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

            – undrline
            2 days ago


















          5














          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.






          share|improve this answer




















          • 1





            I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

            – undrline
            2 days ago











          • @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

            – Randi Vertongen
            2 days ago



















          1














          Doug Lane did a series of videos called "T-SQL Level Up" that are on YouTube. Part of the series explores a general approach to removing cursors that goes something like this:



          • Remove all the cursor language (declare cursor, open, fetch, while, close, deallocate, etc) and other variable declarations

          • Identify places where set-based operations can be combined (variables populated by a SELECT that are later used in an INSERT might be replaced by an INSERT INTO...SELECT statement, for example)

          • Move conditional logic (IF...ELSE) into WHERE clauses, CASE statements, subqueries, etc

          As the other great answers here have pointed out, there's no silver bullet for this. But these videos are, in my opinion, a really intuitive approach to solving the problem.



          Doug goes through three cursor replacements of increasing complexity in each part, I'd highly recommend watching (as the whole deal comes across better in video):



          • T-SQL Level Up Chapter 6 Replacing Cursors Part 1

          • T-SQL Level Up Chapter 6 Replacing Cursors Part 2

          • T-SQL Level Up Chapter 6 Replacing Cursors Part 3





          share|improve this answer























            Your Answer








            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233884%2fcursor-replacement-for-newbies%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            3 Answers
            3






            active

            oldest

            votes








            3 Answers
            3






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            6














            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.



            The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.



            Your question still asks for a general replacement, and I still believe there is no such thing.






            share|improve this answer

























            • Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

              – undrline
              2 days ago















            6














            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.



            The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.



            Your question still asks for a general replacement, and I still believe there is no such thing.






            share|improve this answer

























            • Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

              – undrline
              2 days ago













            6












            6








            6







            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.



            The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.



            Your question still asks for a general replacement, and I still believe there is no such thing.






            share|improve this answer















            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.



            The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.



            Your question still asks for a general replacement, and I still believe there is no such thing.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited 2 days ago

























            answered Apr 3 at 21:21









            Aaron BertrandAaron Bertrand

            154k18298493




            154k18298493












            • Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

              – undrline
              2 days ago

















            • Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

              – undrline
              2 days ago
















            Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

            – undrline
            2 days ago





            Thank you for the response. The goal isn't to eliminate all cursors. The idea is that someone who's brand-new to SQL coming from a programming background and doesn't know anything about set-based processes is going to use a cursor and dynamic sql. Surely you've come across some commonalities. Perhaps my edit helps.

            – undrline
            2 days ago













            5














            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.






            share|improve this answer




















            • 1





              I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

              – undrline
              2 days ago











            • @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

              – Randi Vertongen
              2 days ago
















            5














            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.






            share|improve this answer




















            • 1





              I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

              – undrline
              2 days ago











            • @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

              – Randi Vertongen
              2 days ago














            5












            5








            5







            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.






            share|improve this answer















            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.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Apr 3 at 22:00

























            answered Apr 3 at 21:24









            Randi VertongenRandi Vertongen

            4,5361924




            4,5361924







            • 1





              I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

              – undrline
              2 days ago











            • @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

              – Randi Vertongen
              2 days ago













            • 1





              I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

              – undrline
              2 days ago











            • @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

              – Randi Vertongen
              2 days ago








            1




            1





            I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

            – undrline
            2 days ago





            I'm not quite sure yet ... it's gonna take some time to get through the wall of a post, but the beginning and skimming sounds like it might be the answer to my question :)

            – undrline
            2 days ago













            @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

            – Randi Vertongen
            2 days ago






            @undrline Ofcourse these examples are only a few and a part of what can be done. Let me know if you need clarification! :)

            – Randi Vertongen
            2 days ago












            1














            Doug Lane did a series of videos called "T-SQL Level Up" that are on YouTube. Part of the series explores a general approach to removing cursors that goes something like this:



            • Remove all the cursor language (declare cursor, open, fetch, while, close, deallocate, etc) and other variable declarations

            • Identify places where set-based operations can be combined (variables populated by a SELECT that are later used in an INSERT might be replaced by an INSERT INTO...SELECT statement, for example)

            • Move conditional logic (IF...ELSE) into WHERE clauses, CASE statements, subqueries, etc

            As the other great answers here have pointed out, there's no silver bullet for this. But these videos are, in my opinion, a really intuitive approach to solving the problem.



            Doug goes through three cursor replacements of increasing complexity in each part, I'd highly recommend watching (as the whole deal comes across better in video):



            • T-SQL Level Up Chapter 6 Replacing Cursors Part 1

            • T-SQL Level Up Chapter 6 Replacing Cursors Part 2

            • T-SQL Level Up Chapter 6 Replacing Cursors Part 3





            share|improve this answer



























              1














              Doug Lane did a series of videos called "T-SQL Level Up" that are on YouTube. Part of the series explores a general approach to removing cursors that goes something like this:



              • Remove all the cursor language (declare cursor, open, fetch, while, close, deallocate, etc) and other variable declarations

              • Identify places where set-based operations can be combined (variables populated by a SELECT that are later used in an INSERT might be replaced by an INSERT INTO...SELECT statement, for example)

              • Move conditional logic (IF...ELSE) into WHERE clauses, CASE statements, subqueries, etc

              As the other great answers here have pointed out, there's no silver bullet for this. But these videos are, in my opinion, a really intuitive approach to solving the problem.



              Doug goes through three cursor replacements of increasing complexity in each part, I'd highly recommend watching (as the whole deal comes across better in video):



              • T-SQL Level Up Chapter 6 Replacing Cursors Part 1

              • T-SQL Level Up Chapter 6 Replacing Cursors Part 2

              • T-SQL Level Up Chapter 6 Replacing Cursors Part 3





              share|improve this answer

























                1












                1








                1







                Doug Lane did a series of videos called "T-SQL Level Up" that are on YouTube. Part of the series explores a general approach to removing cursors that goes something like this:



                • Remove all the cursor language (declare cursor, open, fetch, while, close, deallocate, etc) and other variable declarations

                • Identify places where set-based operations can be combined (variables populated by a SELECT that are later used in an INSERT might be replaced by an INSERT INTO...SELECT statement, for example)

                • Move conditional logic (IF...ELSE) into WHERE clauses, CASE statements, subqueries, etc

                As the other great answers here have pointed out, there's no silver bullet for this. But these videos are, in my opinion, a really intuitive approach to solving the problem.



                Doug goes through three cursor replacements of increasing complexity in each part, I'd highly recommend watching (as the whole deal comes across better in video):



                • T-SQL Level Up Chapter 6 Replacing Cursors Part 1

                • T-SQL Level Up Chapter 6 Replacing Cursors Part 2

                • T-SQL Level Up Chapter 6 Replacing Cursors Part 3





                share|improve this answer













                Doug Lane did a series of videos called "T-SQL Level Up" that are on YouTube. Part of the series explores a general approach to removing cursors that goes something like this:



                • Remove all the cursor language (declare cursor, open, fetch, while, close, deallocate, etc) and other variable declarations

                • Identify places where set-based operations can be combined (variables populated by a SELECT that are later used in an INSERT might be replaced by an INSERT INTO...SELECT statement, for example)

                • Move conditional logic (IF...ELSE) into WHERE clauses, CASE statements, subqueries, etc

                As the other great answers here have pointed out, there's no silver bullet for this. But these videos are, in my opinion, a really intuitive approach to solving the problem.



                Doug goes through three cursor replacements of increasing complexity in each part, I'd highly recommend watching (as the whole deal comes across better in video):



                • T-SQL Level Up Chapter 6 Replacing Cursors Part 1

                • T-SQL Level Up Chapter 6 Replacing Cursors Part 2

                • T-SQL Level Up Chapter 6 Replacing Cursors Part 3






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 2 days ago









                Josh DarnellJosh Darnell

                7,84022243




                7,84022243



























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233884%2fcursor-replacement-for-newbies%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Sum ergo cogito? 1 nng

                    三茅街道4182Guuntc Dn precexpngmageondP