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 Tuesday, August 6, 2013 9:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:08 AM
Points: 354, Visits: 357
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



Post #1481411
Posted Wednesday, August 7, 2013 10:04 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
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 ) 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."

(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 #1482127
Posted Thursday, August 8, 2013 12:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 12,957, Visits: 10,729
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.




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 #1482151
Posted Thursday, August 8, 2013 9:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:08 AM
Points: 354, Visits: 357
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



Post #1482418
Posted Thursday, August 8, 2013 12:51 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
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.


--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 #1482528
Posted Thursday, August 8, 2013 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 12,957, Visits: 10,729
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.




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 #1482536
Posted Thursday, August 8, 2013 1:10 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.

Prologue

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

(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 #1482546
Posted Thursday, August 8, 2013 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 12,957, Visits: 10,729
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?




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 #1482547
Posted Thursday, August 8, 2013 2:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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 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 #1482560
Posted Thursday, August 8, 2013 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 12,957, Visits: 10,729
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.




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 #1482561
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse