SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
aochss
aochss
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 443
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
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90648 Visits: 41150
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28453 Visits: 13268
I think one of my first "eureka" moment - as Jeff calls it - was when I read Jeff's article about the Tally table :-D 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... 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, 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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
aochss
aochss
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 443
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
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90648 Visits: 41150
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 :-D 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... 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, 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28453 Visits: 13268
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 :-D 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... 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, 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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90648 Visits: 41150
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28453 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27198 Visits: 17557
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.

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)
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28453 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search