Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

What is your favorite "I didn't know that" moment in T-SQL? Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 12:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482650
Posted Friday, August 9, 2013 12:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482651
Posted Friday, August 9, 2013 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1482756
Posted Friday, August 9, 2013 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 358, Visits: 385
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



Post #1482766
Posted Friday, August 9, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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.


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


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1482767
Posted Friday, August 9, 2013 7:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,816, Visits: 5,915
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1482770
    Posted Friday, August 9, 2013 8:28 AM
    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 1:58 PM
    Points: 3,113, Visits: 11,542
    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.

    Post #1482791
    Posted Friday, August 9, 2013 11:44 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 2:36 PM
    Points: 35,776, Visits: 32,448
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1482873
    Posted Friday, August 9, 2013 11:54 AM


    UDP Broadcaster

    UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

    Group: General Forum Members
    Last Login: Today @ 11:43 AM
    Points: 1,491, Visits: 1,768
    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.
    Before that I used to create date dimensions with a WHILE loop, and I was so proud I didn't use a cursor. Ahem... 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, 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".
    Post #1482878
    Posted Friday, August 9, 2013 3:21 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 4:21 PM
    Points: 7,928, Visits: 9,653
    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
    Post #1482956
    « Prev Topic | Next Topic »

    Add to briefcase «««12345»»»

    Permissions Expand / Collapse