Post reply

Alternative to CURSOR object

  • November 24, 2021 at 12:54 pm

    #3956478

    Dear Friends,

    Would be great if you can advise on an alternative to CURSOR object. Thank you in advance..

    Best Regards...Arshad

  • November 24, 2021 at 1:32 pm

    #3956501

    Hard to know what to say here without detail.

    Instead of cursors, use set-based queries.

    There you go.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 24, 2021 at 2:16 pm

    #3956507

    I agree with Grant here.  I find that MOST of the time when I think a Cursor is required, it is just that I have been doing too much .NET development.  A cursor should not be needed in MOST queries.  There are exceptions, but I have gotten to the point where the ONLY time I am using a cursor in a query is for an administrative related task where row based operation is required.  Something like a home-build sp_msforeachtable for example I would use a cursor for.

    One alternative to a cursor though is a WHILE statement, but it is another one that I would try to avoid.  SQL RARELY needs loops.  Plus loops in SQL are incredibly slow.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 24, 2021 at 7:37 pm

    #3956630

    Arsh wrote:

    Dear Friends,

    Would be great if you can advise on an alternative to CURSOR object. Thank you in advance..

    Best Regards...Arshad

    1. Tally/Numbers Table
    2. Tally/Numbers Inline function.
    3. Other "Pseudo-Cursors" based on "the presence of rows" or cCTE (Cascading CTEs), which will frequently also include Windowing Functions, etc.
    4. On rare occasions, rCTEs (Recursive CTEs) but those can frequently be beat by a well formed While Loop because that's all they really are but use more resources than a While Loop.
    5. #3 combined with some dynamic SQL.

    Frequently, rereading the problem and understanding that SQL Server is, at its most basic, nothing more than a file server and that ever SELECT, INSERT, UPDATE, and DELETE is really a nearly machine language level While Loop/Cursor that some of us refer to as a "Pseudo-Cursor" and forms the basis of understanding for what "Set-Based" actually is.  If nothing else, do like it says in my signature line below.

    Those are all of the more common answers for how to replace a cursor/while loop, both of which is known as "RBAR" (and so is an rCTE in many cases).

    Do you have an example of a cursor that you'd like to replace?

    Also, if you've not worked with a Tally or Numbers table yet, see the following article for an introduction to that kind of understanding with the understanding that it's only an introduction for how to replace While Loops (and the very closely related "Cursor") with some "Set Based/Pseudo-Cursor" magic.

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply