Home Forums SQL Server 7,2000 T-SQL What is your favorite "I didn't know that" moment in T-SQL? RE: What is your favorite "I didn't know that" moment in T-SQL?

  • August 8, 2013 at 12:51 pm

    #1639950

    Koen Verbeeck (8/8/2013)

    I think one of my first "eureka" moment - as Jeff calls it - was when I read Jeff's article about the Tally table 😀 The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

    Before that I used to create date dimensions with a WHILE loop, and I was so proud I didn't use a cursor. Ahem... :blush: Now I create my date dimensions in just a few milliseconds.

    Another one was when I read another article by Jeff, called REPLACE Multiple Spaces with One[/url], which presents a really clever way to clean-up your data without nesting endlessly replace statements (it can replace any number of spaces with 3 REPLACE functions).

    Regarding SSIS, which I do most of the time, this little nugget by Jamie Thomson really rocked my brain: FileNameColumnName property, Flat File Source Adapter. Simple, but effective.

    Thanks for the feedback on thos, Koen. I really appreciate it. Unfortunately, I'm not always right.

    Go back and read that article again the "Replace Multiple Spaces" thing, again. As so often happens, someone in the discussion brought up the nested replaces using a slightly different method and, as much as I hate to admit it, it's an order of magnitude faster than the method I came up with. IIRC, I provide a link to the post in the updated prologue of the article.

    You've just gotta love this community. An article get's people thinking and then pure magic comes out of the discussion. It doesn't get any better than that. 🙂

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