What is your favorite "I didn't know that" moment in T-SQL?

  • aochss

    SSCommitted

    Points: 1677

    All,

    While doing the QotD submissions as well as poking around the web for some answers, I recently ran across a couple of "Face Palm" moments that helped me solve a problem that in the past has taken a lot of code. These moments are prompting me to do some in depth learning of SQL Server over the next couple of months.

    My favorite T-SQL enhancement (2005) has been CROSS APPLY. Wow...

    http://msdn.microsoft.com/en-us/library/ms175156.aspx

    Another was the use of Table Value Constructors to insert multiple records into a table with one statement (2008). This has saved a ton of time when creating sample data.

    http://msdn.microsoft.com/en-us/library/dd776382%28v=sql.105%29.aspx

    My most recent discovery moment has been the ability to declare and assign variables in one statement (2008).

    http://blog.sqlauthority.com/2011/03/18/sql-server-2008-2011-declare-and-assign-variable-in-single-statement/

    I realize that some of these have been around for a while, but when I first used them, I had a smile on my face for the rest of the day. This is why I still love this doing this almost twenty years later.

    Does anyone else have any of these kind of moments?

    Thanks,

    Anton

  • Jeff Moden

    SSC Guru

    Points: 997316

    My most significant "Eureka!" moment shortly after I joined SSC (more than a decade ago for that) or knew anything of a Tally/Numbers Table. It was when I first realized that a simple CROSS JOIN and a couple of ancient forumulas for constraining random numbers could be used to generate a million row test table at the drop of a hat (any hat :-D) with incredibly little effort and without any explicit loop (circa SQL Server 7). I used to call the method "set based loops" until a fellow by the name of R. Barry Young coined the phrase "pseudo cursor" for the method.

    I certainly wasn't the first to do such a thing but I knew nothing, at the time, of those others that had made a similar discovery.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • aochss

    SSCommitted

    Points: 1677

    All,

    OK, add the "EXCEPT" and "INTERSECT" clauses to my list. I was asked to compare a file used to load our master parts data from last week's file to this week's file. I started in the column by column method (yuck), then to using checksum values on the rows and then found the "EXCEPT" gem. Don't know about performance on larger tables, but for around 50,000 rows it works great.

    Good article here is:

    http://www.sqlservercentral.com/Forums/Topic848796-2596-1.aspx

    External Link:

    https://www.simple-talk.com/sql/performance/the-except-and-intersect-operators-in-sql-server/

    Anton

  • Jeff Moden

    SSC Guru

    Points: 997316

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Jeff Moden (8/8/2013)


    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. 🙂

    Indeed it doesn't 🙂

    I remember reading the long discussion about the splitter functions you wrote and all the alternatives. Good stuff.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Jeff Moden

    SSC Guru

    Points: 997316

    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    [font="Arial Black"]Prologue [/font]

    This article was originally published on November 16th, 2009. As with anything else, improvements can be made to code and the subject of this article is no exception. Although this article is still an interesting read (I left everything after the "Introduction" heading as it originally was), I no longer recommend the method in this article because it has been soundly beaten (more than 6 times faster) for performance by another T-SQL method as demonstrated by Michael Meierruth. If you prefer to "cut to the chase", that method can be found in a post in the discussion for this article at the following URL:

    http://www.sqlservercentral.com/Forums/FindPost821209.aspx

    The discussion that followed this article is also fascinating and a large number of people took part in some rather wonderful testing. Some folks even took the time to create and post some CLRs to solve the same problem. All in all, the discussion makes for an incredible learning experience which is typical of the amazing community of professional people we've all grown to know and love here at SSC. I learn something new here everyday.

    One of the things that came out of the discussion is the fact that the default collation can make a huge impact on performance. The the following link to the post in the discussion where Paul White demonstrates that not-so-little nuance:

    http://www.sqlservercentral.com/Forums/FindPost821565.aspx

    Be you Neophyte or "Ninja", thanks for being a part of this community, folks.

    --Jeff Moden

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Sean Lange

    SSC Guru

    Points: 286536

    Koen Verbeeck (8/8/2013)


    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".

    English: sense it makes none.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Sean Lange

    SSC Guru

    Points: 286536

    I can still remember one of those Eureka moments. I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange

    SSC Guru

    Points: 286536

    Koen Verbeeck (8/8/2013)


    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".

    English: sense it makes none.

    Ed Zachary

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".

    English: sense it makes none.

    Ed Zachary

    ?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Sean Lange (8/8/2013)


    I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.

    I think the quote is about right, as far as I remember. Really helps with the set based thinking.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Sean Lange

    SSC Guru

    Points: 286536

    Koen Verbeeck (8/8/2013)


    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Sean Lange (8/8/2013)


    Koen Verbeeck (8/8/2013)


    Jeff Moden (8/8/2013)


    It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

    Hmmm, this might be the language gap. I was being affirmative.

    You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?

    Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".

    English: sense it makes none.

    Ed Zachary

    ?

    Sorry it a reference to an off color joke. It is meant to be "exactly". 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 43 total)

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