Are you getting the most out of your TSQL queries? Most people still write queries like they’re using SQL Server 2000, but there’s lots of new functionality that can make your TSQL simpler to read, write, and support.
Kendra Little introduces you to five cool features that you should think about using to improve your SQL Server queries (along with the time the demo on that feature starts):
1) The glory of the OUTPUT clause – 4:53
2) Over and Partition By – 12:23
3) The APPLY operator for reusable computations – 16:56
4) EXCEPT and INTERSECT – 22:03
5) The Magic of indexed computed columns – 24:24
Looking for the scripts from the webcast? Scroll on down to below the video for the download link.
Scripts and Links
Need to check your database compatibility level? Our sp_Blitz script does that and much more.
Download demo scripts from the webcast here: 5-TSQL-Features-Youre-Missing-Out-On-Demo-Scripts.zip
Questions from the Webcast
Here are some great questions we got from folks during the webcast which I didn’t have time to answer live, but caught up with after the show.
From John on subqueries
Question: Is the difference with using a subquery vs Over/Partion is the subquery is processed for every row and the over isn’t?
Answer: SQL Server is too tricksy to make things so simple to remember. Subqueries won’t necessarily get processed repeatedly for every row as a general rule. The optimizer will do its darndest to rewrite things as efficiently as it can in the time it is allowed for optimization. The behavior depends on the query and the version of SQL Server.
Special things are definitely done when you bring in OVER, though. If you’d like to nerd out on how this works, good news! Paul White wrote a detailed post on this very topic. (Warning: brain melting may ensue.)
From Riley on CROSS APPLY vs CTEs
Question: How does the CROSS APPLY performance compare to using a CTE, where the Subtotal Plus Tax is computed in the CTE and then added to in the final SELECT? Do they generally result in the same plan?
Answer: As as general rule, I don’t expect CROSS APPLY and CTEs to get the same plans– I’ve seen many cases where they do not.
In this specific case for making computations reusable, the plans are very different. The APPLY operator lets you put in the calculation without a FROM clause or a correlation– it uses the same Compute Scalar operator that the original version of the query used. If you put the calculation in a CTE you have to essentially make it a subquery, and that’s not necessarily something SQL Server’s going to be able to normalize out. (I did a quick test of this particular scenario just to see if it was more clever than I thought in 2012, and it gets a much more expensive plan.)
From Donald on computed columns
Question: I thought Computed columns were a bad idea ?
Answer: Not at all! Like anything else, you can do something terrible with them, but they can also be absolutely brilliant by simplifying code or improving performance. Persisting and sometimes indexing the computed column can be very powerful when it’s a frequently read calculation.
From Grace on portability/ANSI standard
Question: Are these statements compatible with ANSI SQL or are they specific to MS T-SQL?
Answer: All my demos and comments here are specific to TSQL, specifically the Microsoft SQL Server implementation. Some of these are likely in the ANSI spec, but SQL Server very frequently doesn’t adhere to ANSI SQL specs– it likes to do it’s own thang. So I wouldn’t count on it!
From Mike on mikes
Question: Hey guys, what microphone setup do you use.. the sound is good. I moderate some PASS VC’s and have to get a new microphone…
Answer: Brent, Jeremiah and I all use Yeti Microphones. We love ‘em. Glad the sound is good live! It suffers a little in the recordings we publish of the webcasts because the audio is recorded through our online webcast service. For trainings we record at home, the Yeti really gets to shine.
Hungry for more on making queries better? Check out our TSQL Training.