Cursor Replacement for NewbiesImprove a query using cursor to SET Based approachReplacing a Cursor with a single Query possible?how cursor implementations are different for each cursor type in sql serverOracle GoldenGate add trandata errorsSave a dynamic cursor for reuse?SQL SERVER 2008 Cursor ProblemUpdate Using a cursorFOR UPDATE clause allowed only for DECLARE CURSORMaintenance plan using cursor statementSet cursor rows commandMysql database table cursorCursor never stops

How could indestructible materials be used in power generation?

I would say: "You are another teacher", but she is a woman and I am a man

Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?

How can saying a song's name be a copyright violation?

Why is consensus so controversial in Britain?

Is it acceptable for a professor to tell male students to not think that they are smarter than female students?

Bullying boss launched a smear campaign and made me unemployable

Forgetting the musical notes while performing in concert

Avoiding the "not like other girls" trope?

Would Slavery Reparations be considered Bills of Attainder and hence Illegal?

What about the virus in 12 Monkeys?

What type of content (depth/breadth) is expected for a short presentation for Asst Professor interview in the UK?

GFCI outlets - can they be repaired? Are they really needed at the end of a circuit?

Ambiguity in the definition of entropy

What method can I use to design a dungeon difficult enough that the PCs can't make it through without killing them?

Why can't we play rap on piano?

Examples of smooth manifolds admitting inbetween one and a continuum of complex structures

Could the museum Saturn V's be refitted for one more flight?

What is the most common color to indicate the input-field is disabled?

Unable to supress ligatures in headings which are set in Caps

What's the in-universe reasoning behind sorcerers needing material components?

Why does this cyclic subgroup have only 4 subgroups?

How do I gain back my faith in my PhD degree?

How to prevent "they're falling in love" trope



Cursor Replacement for Newbies


Improve a query using cursor to SET Based approachReplacing a Cursor with a single Query possible?how cursor implementations are different for each cursor type in sql serverOracle GoldenGate add trandata errorsSave a dynamic cursor for reuse?SQL SERVER 2008 Cursor ProblemUpdate Using a cursorFOR UPDATE clause allowed only for DECLARE CURSORMaintenance plan using cursor statementSet cursor rows commandMysql database table cursorCursor never stops













1















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










share|improve this question


























    1















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










    share|improve this question
























      1












      1








      1








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










      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
      '

      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










      asked 3 hours ago









      undrlineundrline

      215




      215




















          2 Answers
          2






          active

          oldest

          votes


















          3














          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.






          share|improve this answer






























            2














            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

























              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

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              3














              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.






              share|improve this answer



























                3














                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.






                share|improve this answer

























                  3












                  3








                  3







                  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.






                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 2 hours ago









                  Aaron BertrandAaron Bertrand

                  153k18298493




                  153k18298493























                      2














                      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





























                        2














                        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



























                          2












                          2








                          2







                          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 2 hours ago

























                          answered 2 hours ago









                          Randi VertongenRandi Vertongen

                          4,396924




                          4,396924



























                              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

                              Францішак Багушэвіч Змест Сям'я | Біяграфія | Творчасць | Мова Багушэвіча | Ацэнкі дзейнасці | Цікавыя факты | Спадчына | Выбраная бібліяграфія | Ушанаванне памяці | У філатэліі | Зноскі | Літаратура | Спасылкі | НавігацыяЛяхоўскі У. Рупіўся дзеля Бога і людзей: Жыццёвы шлях Лявона Вітан-Дубейкаўскага // Вольскі і Памідораў з песняй пра немца Адвакат, паэт, народны заступнік Ашмянскі веснікВ Минске появится площадь Богушевича и улица Сырокомли, Белорусская деловая газета, 19 июля 2001 г.Айцец беларускай нацыянальнай ідэі паўстаў у бронзе Сяргей Аляксандравіч Адашкевіч (1918, Мінск). 80-я гады. Бюст «Францішак Багушэвіч».Яўген Мікалаевіч Ціхановіч. «Партрэт Францішка Багушэвіча»Мікола Мікалаевіч Купава. «Партрэт зачынальніка новай беларускай літаратуры Францішка Багушэвіча»Уладзімір Іванавіч Мелехаў. На помніку «Змагарам за родную мову» Барэльеф «Францішак Багушэвіч»Памяць пра Багушэвіча на Віленшчыне Страчаная сталіца. Беларускія шыльды на вуліцах Вільні«Krynica». Ideologia i przywódcy białoruskiego katolicyzmuФранцішак БагушэвічТворы на knihi.comТворы Францішка Багушэвіча на bellib.byСодаль Уладзімір. Францішак Багушэвіч на Лідчыне;Луцкевіч Антон. Жыцьцё і творчасьць Фр. Багушэвіча ў успамінах ягоных сучасьнікаў // Запісы Беларускага Навуковага таварыства. Вільня, 1938. Сшытак 1. С. 16-34.Большая российская1188761710000 0000 5537 633Xn9209310021619551927869394п

                              Беларусь Змест Назва Гісторыя Геаграфія Сімволіка Дзяржаўны лад Палітычныя партыі Міжнароднае становішча і знешняя палітыка Адміністрацыйны падзел Насельніцтва Эканоміка Культура і грамадства Сацыяльная сфера Узброеныя сілы Заўвагі Літаратура Спасылкі НавігацыяHGЯOiТоп-2011 г. (па версіі ej.by)Топ-2013 г. (па версіі ej.by)Топ-2016 г. (па версіі ej.by)Топ-2017 г. (па версіі ej.by)Нацыянальны статыстычны камітэт Рэспублікі БеларусьШчыльнасць насельніцтва па краінахhttp://naviny.by/rubrics/society/2011/09/16/ic_articles_116_175144/А. Калечыц, У. Ксяндзоў. Спробы засялення краю неандэртальскім чалавекам.І ў Менску былі мамантыА. Калечыц, У. Ксяндзоў. Старажытны каменны век (палеаліт). Першапачатковае засяленне тэрыторыіГ. Штыхаў. Балты і славяне ў VI—VIII стст.М. Клімаў. Полацкае княства ў IX—XI стст.Г. Штыхаў, В. Ляўко. Палітычная гісторыя Полацкай зямліГ. Штыхаў. Дзяржаўны лад у землях-княствахГ. Штыхаў. Дзяржаўны лад у землях-княствахБеларускія землі ў складзе Вялікага Княства ЛітоўскагаЛюблінская унія 1569 г."The Early Stages of Independence"Zapomniane prawdy25 гадоў таму было аб'яўлена, што Язэп Пілсудскі — беларус (фота)Наша вадаДакументы ЧАЭС: Забруджванне тэрыторыі Беларусі « ЧАЭС Зона адчужэнняСведения о политических партиях, зарегистрированных в Республике Беларусь // Министерство юстиции Республики БеларусьСтатыстычны бюлетэнь „Полаўзроставая структура насельніцтва Рэспублікі Беларусь на 1 студзеня 2012 года і сярэднегадовая колькасць насельніцтва за 2011 год“Индекс человеческого развития Беларуси — не было бы нижеБеларусь занимает первое место в СНГ по индексу развития с учетом гендерного факцёраНацыянальны статыстычны камітэт Рэспублікі БеларусьКанстытуцыя РБ. Артыкул 17Трансфармацыйныя задачы БеларусіВыйсце з крызісу — далейшае рэфармаванне Беларускі рубель — сусветны лідар па дэвальвацыяхПра змену коштаў у кастрычніку 2011 г.Бядней за беларусаў у СНД толькі таджыкіСярэдні заробак у верасні дасягнуў 2,26 мільёна рублёўЭканомікаГаласуем за ТОП-100 беларускай прозыСучасныя беларускія мастакіАрхитектура Беларуси BELARUS.BYА. Каханоўскі. Культура Беларусі ўсярэдзіне XVII—XVIII ст.Анталогія беларускай народнай песні, гуказапісы спеваўБеларускія Музычныя IнструментыБеларускі рок, які мы страцілі. Топ-10 гуртоў«Мясцовы час» — нязгаслая легенда беларускай рок-музыкіСЯРГЕЙ БУДКІН. МЫ НЯ ЗНАЕМ СВАЁЙ МУЗЫКІМ. А. Каладзінскі. НАРОДНЫ ТЭАТРМагнацкія культурныя цэнтрыПублічная дыскусія «Беларуская новая пьеса: без беларускай мовы ці беларуская?»Беларускія драматургі па-ранейшаму лепш ставяцца за мяжой, чым на радзіме«Працэс незалежнага кіно пайшоў, і дзяржаву турбуе яго непадкантрольнасць»Беларускія філосафы ў пошуках прасторыВсе идём в библиотекуАрхіваванаАб Нацыянальнай праграме даследавання і выкарыстання касмічнай прасторы ў мірных мэтах на 2008—2012 гадыУ космас — разам.У суседнім з Барысаўскім раёне пабудуюць Камандна-вымяральны пунктСвяты і абрады беларусаў«Мірныя бульбашы з малой краіны» — 5 непраўдзівых стэрэатыпаў пра БеларусьМ. Раманюк. Беларускае народнае адзеннеУ Беларусі скарачаецца колькасць злачынстваўЛукашэнка незадаволены мінскімі ўладамі Крадзяжы складаюць у Мінску каля 70% злачынстваў Узровень злачыннасці ў Мінскай вобласці — адзін з самых высокіх у краіне Генпракуратура аналізуе стан са злачыннасцю ў Беларусі па каэфіцыенце злачыннасці У Беларусі стабілізавалася крымінагеннае становішча, лічыць генпракурорЗамежнікі сталі здзяйсняць у Беларусі больш злачынстваўМУС Беларусі турбуе рост рэцыдыўнай злачыннасціЯ з ЖЭСа. Дазволіце вас абкрасці! Рэйтынг усіх службаў і падраздзяленняў ГУУС Мінгарвыканкама вырасАб КДБ РБГісторыя Аператыўна-аналітычнага цэнтра РБГісторыя ДКФРТаможняagentura.ruБеларусьBelarus.by — Афіцыйны сайт Рэспублікі БеларусьСайт урада БеларусіRadzima.org — Збор архітэктурных помнікаў, гісторыя Беларусі«Глобус Беларуси»Гербы и флаги БеларусиАсаблівасці каменнага веку на БеларусіА. Калечыц, У. Ксяндзоў. Старажытны каменны век (палеаліт). Першапачатковае засяленне тэрыторыіУ. Ксяндзоў. Сярэдні каменны век (мезаліт). Засяленне краю плямёнамі паляўнічых, рыбакоў і збіральнікаўА. Калечыц, М. Чарняўскі. Плямёны на тэрыторыі Беларусі ў новым каменным веку (неаліце)А. Калечыц, У. Ксяндзоў, М. Чарняўскі. Гаспадарчыя заняткі ў каменным векуЭ. Зайкоўскі. Духоўная культура ў каменным векуАсаблівасці бронзавага веку на БеларусіФарміраванне супольнасцей ранняга перыяду бронзавага векуФотографии БеларусиРоля беларускіх зямель ва ўтварэнні і ўмацаванні ВКЛВ. Фадзеева. З гісторыі развіцця беларускай народнай вышыўкіDMOZGran catalanaБольшая российскаяBritannica (анлайн)Швейцарскі гістарычны15325917611952699xDA123282154079143-90000 0001 2171 2080n9112870100577502ge128882171858027501086026362074122714179пппппп

                              ValueError: Expected n_neighbors <= n_samples, but n_samples = 1, n_neighbors = 6 (SMOTE) The 2019 Stack Overflow Developer Survey Results Are InCan SMOTE be applied over sequence of words (sentences)?ValueError when doing validation with random forestsSMOTE and multi class oversamplingLogic behind SMOTE-NC?ValueError: Error when checking target: expected dense_1 to have shape (7,) but got array with shape (1,)SmoteBoost: Should SMOTE be ran individually for each iteration/tree in the boosting?solving multi-class imbalance classification using smote and OSSUsing SMOTE for Synthetic Data generation to improve performance on unbalanced dataproblem of entry format for a simple model in KerasSVM SMOTE fit_resample() function runs forever with no result