Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Hidden RBAR: Counting with Recursive CTE's Expand / Collapse
Author
Message
Posted Monday, August 08, 2011 12:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
Comments posted to this topic are about the item Hidden RBAR: Counting with Recursive CTE's

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1155832
Posted Monday, August 08, 2011 2:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 28, 2013 1:56 AM
Points: 161, Visits: 363
Very insightful article. Thanks.
I have one question, how do you discard the output?
(Anticipating a "slap-to-the-forehead" moment, but asking anyway...)

Best,
Henk



Post #1155866
Posted Monday, August 08, 2011 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:35 AM
Points: 1,046, Visits: 2,997
Sorry, but there's a glaring inconsistency in this article.

No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
  • what you've been eating all this time isn't really made from beer or

  • there isn't really a secret formula, and you're stringing us all along.


  • Tut, tut, Mr Moden....




    Semper in excretia, sumus solum profundum variat
    Post #1155869
    Posted Monday, August 08, 2011 5:12 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, August 25, 2013 11:23 PM
    Points: 3, Visits: 51
    Excellent, Thanks Jeff!
    Post #1155915
    Posted Monday, August 08, 2011 7:21 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 10:21 PM
    Points: 35,951, Visits: 30,235
    Henk van den Berg (8/8/2011)
    Very insightful article. Thanks.
    I have one question, how do you discard the output?
    (Anticipating a "slap-to-the-forehead" moment, but asking anyway...)

    Best,
    Henk


    Heh... no problem, Henk. A lot of people miss it. In SQL Server 2005, start by selecting {Tools}{Options}{Query Results}{Results to Grid}. Then, click the {Discard results after execution} check box. In 2005, you may have to open a new query window in SSMS before the setting takes affect. In 2008, (IIRC) the setting is good for the current window and any new windows you open while the box is checked.





    --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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems


      Post Attachments 
    DiscardResults.gif (608 views, 14.88 KB)
    Post #1156030
    Posted Monday, August 08, 2011 7:23 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 10:21 PM
    Points: 35,951, Visits: 30,235
    majorbloodnock (8/8/2011)
    Sorry, but there's a glaring inconsistency in this article.

    No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
  • what you've been eating all this time isn't really made from beer or

  • there isn't really a secret formula, and you're stringing us all along.


  • Tut, tut, Mr Moden....




    Heh... because I like American mass-produced beer in pop-top cans, some would say that I, indeed, have no taste buds.


    --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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1156032
    Posted Monday, August 08, 2011 7:24 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 10:21 PM
    Points: 35,951, Visits: 30,235
    George J Verras (8/8/2011)
    Excellent, Thanks Jeff!


    You bet, George. Thanks for stopping by and for the feedback.


    --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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1156034
    Posted Monday, August 08, 2011 8:32 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 03, 2014 6:39 PM
    Points: 174, Visits: 427
    Greetings Jeff,

    That was a wonderful and very insightful article about rCTE's and ways to count out of the box. I will have to put some of the pseudo-counters into practice in my scripts.

    Thank you for sharing.




    P.S.

    Beer popsicles are possible if you know the *secret* ingredient to mix to make it become solid.

    Post #1156090
    Posted Monday, August 08, 2011 9:19 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Tuesday, January 15, 2013 11:11 AM
    Points: 1,945, Visits: 2,782
    This was really good. I was surprised that the WHILE loop did so better than the rCTE. I was expecting the loop to be a bit better because it does not have to touch a table except to write an integer, but I expected the optimizer to turn the rCTE into a loop.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL
    Post #1156137
    Posted Monday, August 08, 2011 10:26 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, February 12, 2013 7:00 AM
    Points: 9, Visits: 13
    Thank you Jeff for your wonderful post.
    It has been extremely helpful to achieve faster speed in a test case I was creating on sql azure.
    I referenced your post in the Microsoft forum where I had already started a thread before finding your precious info:

    http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c368315a-1bf1-4353-b0af-c4cfbb44b2c1

    Cheers,
    Mario
    Post #1156206
    « Prev Topic | Next Topic »

    Add to briefcase 12345»»»

    Permissions Expand / Collapse