Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 8, 2011 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 42,046, Visits: 39,430
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1155832
Posted Monday, August 8, 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 8, 2011 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 28, 2016 9:46 AM
Points: 1,055, Visits: 3,062
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 8, 2011 5:12 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, August 11, 2015 2:04 PM
    Points: 3, Visits: 60
    Excellent, Thanks Jeff!
    Post #1155915
    Posted Monday, August 8, 2011 7:21 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 9:27 AM
    Points: 42,046, Visits: 39,430
    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."

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


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


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 9:27 AM
    Points: 42,046, Visits: 39,430
    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."

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


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 9:27 AM
    Points: 42,046, Visits: 39,430
    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."

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

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

    Group: General Forum Members
    Last Login: Thursday, November 17, 2016 11:59 AM
    Points: 181, Visits: 537
    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 8, 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
    Posted Monday, August 8, 2011 10:56 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: 2 days ago @ 10:20 AM
    Points: 4,131, Visits: 2,324
    Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now....
    Post #1156241
    « Prev Topic | Next Topic »

    Add to briefcase 12345»»»

    Permissions Expand / Collapse