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

  • Sean Lange

    SSC Guru

    Points: 286536

    This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:

    _______________________________________________________________

    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)


    This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:

    Not all languages make sense. In Dutch we say some of the numbers backwards. For example, we say 62 as 'twee en zestig', which translates to 'two and sixty'.

    Just to confuse the hell out of everyone trying to learn our language πŸ˜€

    And especially useful when you are dictating a phone number or house number over the phone...

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

  • mister.magoo

    SSC-Forever

    Points: 47068

    Koen Verbeeck (8/8/2013)


    Sean Lange (8/8/2013)


    This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:

    Not all languages make sense. In Dutch we say some of the numbers backwards. For example, we say 62 as 'twee en zestig', which translates to 'two and sixty'.

    Just to confuse the hell out of everyone trying to learn our language πŸ˜€

    And especially useful when you are dictating a phone number or house number over the phone...

    We used to do that here in England, with time of day - my Mother still does - "five and twenty to three" = 2:35 :w00t:

    Oh, and I completely "got" your "Indeed it doesn't" comment - it's those crazy Americans that don't use English properly like what we do :hehe:

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Jeff Moden

    SSC Guru

    Points: 997372

    Sean Lange (8/8/2013)


    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.

    I believe Mr. Sheffield was quoting me in that post. Please see my signature line. πŸ™‚

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

  • Jeff Moden

    SSC Guru

    Points: 997372

    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.

    Must be because I obviously took it as the negative. BWAAA-HAAA!!!! No wonder people of different languages go to war over dumb things... they were saying the same thing, took it the wrong way, and decided to fight about it instead of talk about it. There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get 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)


    There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.

    That's just plain wrong πŸ™‚

    I think they are playing with your head. πŸ˜€

    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

    Another one of those A-HA moments, was when I discovered you could select a block of text in SSMS while holding ALT (which is also possible in other Microsoft products).

    Really useful for creating update scripts where the left hand and right hand are the same (for example when I update a dimension/fact table using the staging table):

    UPDATE dest

    SET

    SK_ControlPanel_Status = tmp.SK_ControlPanel_Status

    ,SK_ControlPanel_Nihil = tmp.SK_ControlPanel_Nihil

    ,SK_Date_Expected = tmp.SK_Date_Expected

    ,SK_Date_Received = tmp.SK_Date_Received

    ,SK_Date_LastUpdate = tmp.SK_Date_LastUpdate

    ,Frequency = tmp.Frequency

    ,User_LastUpdate = tmp.User_LastUpdate

    ,UserIDResp = tmp.UserIDResp

    ,Deadline = tmp.Deadline

    ,Date_First_Received = tmp.Date_First_Received

    ,Date_Last_Received = tmp.Date_Last_Received

    ,Date_Last_Validated = tmp.Date_Last_Validated

    ,[Validation_Status] = tmp.[Validation_Status]

    ,[Validation_Rule] = tmp.[Validation_Rule]

    ,[Valid] = tmp.[Valid]

    ,[Job_ID] = tmp.[Job_ID]

    ,[Date_First_Imported] = tmp.[Date_First_Imported]

    ,[Date_Last_Imported] = tmp.[Date_Last_Imported]

    ,[Date_Loaded] = tmp.[Date_Loaded]

    ,[Date_Data_Fixed] = tmp.[Date_Data_Fixed]

    ,[Weeks_Missing] = tmp.[Weeks_Missing]

    ,[Cnt_Initial] = tmp.[Cnt_Initial]

    ,[Cnt_Valid] = tmp.[Cnt_Valid]

    ,[Cnt_Error] = tmp.[Cnt_Error]

    ,[Cnt_Nihil] = tmp.[Cnt_Nihil]

    ,[Cnt_Pending] = tmp.[Cnt_Pending]

    ,[Cnt_Waiting] = tmp.[Cnt_Waiting]

    ,[Cnt_Warning] = tmp.[Cnt_Warning]

    ,[Cnt_Fixed] = tmp.[Cnt_Fixed]

    ,[Cnt_NewVersion] = tmp.[Cnt_NewVersion]

    ,UpdateCtr = dest.UpdateCtr + 1

    ,ModifiedOn = tmp.ModifiedOn

    ,ModifiedBy = tmp.ModifiedBy

    ,RowChangeReason = tmp.RowChangeReason

    FROM

    dbo.FactControlPanel dest

    INNER JOIN

    stagin.FactControlPanel tmp

    ON dest.SK_FactControlPanel = tmp.SK_FactControlPanel

    I just script out the select statement in SSMS, select all the columns while holding alt and paste them into the script. Saves me quite some time.

    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

    Jeff Moden (8/8/2013)


    Sean Lange (8/8/2013)


    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.

    I believe Mr. Sheffield was quoting me in that post. Please see my signature line. πŸ™‚

    Or maybe I just gave the credit to the wrong person. It was several years ago so I very well have remembered it wrong. It is certainly possible that it was just from reading your signature. Regardless of where I first I heard it, the concept made a huge impact on me. So a big thanks Jeff!!!

    _______________________________________________________________

    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/

  • aochss

    SSCommitted

    Points: 1677

    Koen Verbeeck (8/9/2013)


    Another one of those A-HA moments, was when I discovered you could select a block of text in SSMS while holding ALT (which is also possible in other Microsoft products).

    Really.....Used it in Word, but not in SSMS.

    Works in Visual Studio 2008, 2010, 2012 as well.

    Thanks,

    Anton

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Sean Lange (8/8/2013)


    This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:

    It's not yours - it's ours! We don't even charge rent!

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • mister.magoo

    SSC-Forever

    Points: 47068

    Being mostly self taught in SQL Server, it has been more of a gradual slide into understanding, but I guess if I had to choose something it would come from this list:

  • APPLY
  • Tally/Numbers/Calendar Tables
  • Query Plans and how to read them
  • FOR XML PATH('')
  • SqlServerCentral πŸ˜‰
  • MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Jeff Moden (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.

    Must be because I obviously took it as the negative. BWAAA-HAAA!!!! No wonder people of different languages go to war over dumb things... they were saying the same thing, took it the wrong way, and decided to fight about it instead of talk about it. There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.

    I was very confused when I heard my cousin from Texas say "I'll carry you down to the grocery store", meaning that she would walk with me to the grocery store.

    I'm not sure what she would say if she was actually going to carry me.

  • Jeff Moden

    SSC Guru

    Points: 997372

    Sean Lange (8/9/2013)


    Jeff Moden (8/8/2013)


    Sean Lange (8/8/2013)


    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.

    I believe Mr. Sheffield was quoting me in that post. Please see my signature line. πŸ™‚

    Or maybe I just gave the credit to the wrong person. It was several years ago so I very well have remembered it wrong. It is certainly possible that it was just from reading your signature. Regardless of where I first I heard it, the concept made a huge impact on me. So a big thanks Jeff!!!

    Heh... it was actually a huge Eureka moment for me, as well. It definitely made thinking in sets easier.

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

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    Koen Verbeeck (8/8/2013)


    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.

    I speak Ohio English and I understood that it meant "I agree - it doesn't get any better".

  • TomThomson

    SSC Guru

    Points: 104773

    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.

    It's better if you combine the two nouns, which of course forces you to change the word order. Then you get

    English: it makes nonsense

    which is a very accurate reflection of reality. Just change the verb from "makes" to "is" and the result is perfect.

    Tom

  • Viewing 15 posts - 16 through 30 (of 43 total)

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