Hidden RBAR: Counting with Recursive CTE's

  • No, I use custom stored function for that purpose.

    -Leonard

  • Excellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.

  • Jeff Moden (8/10/2011)


    Paul Herbert (8/10/2011)


    As usual a good article Moden.

    A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. 🙂

    Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. 🙂 I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. 😉

    Welcome Paul!

    I'd really be interested in an administrative article by you. The administrative side is my primary SQL job focus, but I know there are so many things that I could set-up better on that side.

    Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:

  • Bill Kline-270970 (8/11/2011)


    Excellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.

    Thanks, Bill. :blush: I really appreciate the compliment but, gosh no... I'm not in "the best" category. There are plenty of folks that are much better at this than I. I just try really hard to keep up with all of them. 🙂

    --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)
    Intro to Tally Tables and Functions

  • Kenney Hill (8/11/2011)


    Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:

    Heh... the fact that I talk with the dust bunnies isn't bad enough? 😛

    --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)
    Intro to Tally Tables and Functions

  • Another great article Jeff 🙂

    Jeff Moden (8/8/2011)


    ... some would say that I, indeed, have no taste buds

    Surely you meant 'no taste' 😛

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows (8/19/2011)


    Another great article Jeff 🙂

    Jeff Moden (8/8/2011)


    ... some would say that I, indeed, have no taste buds

    Surely you meant 'no taste' 😛

    heh... that, too! And don't call me "Shirley". 😛

    --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)
    Intro to Tally Tables and Functions

  • Nice article...

    Using tally( or numbers table) is best way..Easy to code :).

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • Jeff Moden

    Beer has some interesting freezing qualities... take it down to somewhere between 26 and 28oF and it's still liquid. Give it a little bit of a sharp rap and all the beer in the bottle will turn to ice in just several seconds.

    The "Hot Ice" trick requires a chemical that you can make from regular household items like vinegar and (IIRC) baking soda and (IIRC) Isopropyl alcohol which, of course, is poison to drink. Makes a really cool (no pun intended) effect though. Hmmm... I wonder if it'll still work with Ethanol.

    [font="Comic Sans MS"]It does not have to be beer. Supercooled water can be produced - you need distilled water. You probably will not be able to do this in a freezer, the mechanical vibrations from the compressor would be equivalent to the shaking action you want to create yourself to be able to observe the spontaneous formation of ice. You have to use a cold bath of salty water brought down to below the freezing temperature of pure water. What happens is that shaking creates nucleation sites where the phase can change to solid ice.

    I just completed viewing all of prof. Grossman's Thermodynamics DVD lectures - this was just one of his many demos.

    [/font]

  • I've actually had the phenomena occur in my truck overnight in the winter months both with bottled spring water and Pepsi. It's amazing how cold it was (~10o-15oF) and it was still liquid.

    --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)
    Intro to Tally Tables and Functions

  • [font="Comic Sans MS"]Ah but that was a MIXTURE, not pure water. Even CO2 dissolved in water is a mixture.

    And for an amazing development, check out NITINOL - a shape memory compound which is "trained" for a particular shape at low temperature and another shape at high temperature - just watch it change shape with a change in the ambient temperature (also from Grossman's DVD)

    Totally off-topic (you got me stated with the liquid ice and Rosetta stone). OK enough diversion from T-SQL[/font]

  • j-1064772 (6/23/2015)


    [font="Comic Sans MS"]Ah but that was a MIXTURE, not pure water. Even CO2 dissolved in water is a mixture.

    And for an amazing development, check out NITINOL - a shape memory compound which is "trained" for a particular shape at low temperature and another shape at high temperature - just watch it change shape with a change in the ambient temperature (also from Grossman's DVD)

    Totally off-topic (you got me stated with the liquid ice and Rosetta stone). OK enough diversion from T-SQL[/font]

    Been working with various flavors of Nitinol and building electronics to get it to do certain things since 1993. Also worked a brief period with the cryogenic version of pipe connections. Fun stuff.

    --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)
    Intro to Tally Tables and Functions

  • At the risk of sounding like a crazy stalker, I'm glad I started "following" you on this forum Jeff, I can usually guarantee that a thread you pop up on will reference something insightful, interesting and above all useful. Worth the overhead of extra email I have in the morning 😀

    I'd not seen this "old" article before, but it's one of those eye-openers that have really helped my career. Thanks (again).

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • david.edwards 76768 wrote:

    At the risk of sounding like a crazy stalker, I'm glad I started "following" you on this forum Jeff, I can usually guarantee that a thread you pop up on will reference something insightful, interesting and above all useful. Worth the overhead of extra email I have in the morning 😀

    I'd not seen this "old" article before, but it's one of those eye-openers that have really helped my career. Thanks (again).

    Gosh... thank you for the very kind words and for the great feedback on this article, David.  I do sometimes wonder "am I helping" and so posts like this one are greatly appreciated.  You've made my day.  Heh... I need more "stalkers" like you. 😀

    --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)
    Intro to Tally Tables and Functions

Viewing 14 posts - 61 through 74 (of 74 total)

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