Temporary tables, table variables, CTEs, and related structures.
17 associated posts15 primary posts
I'm back at home in Vegas, taking your top-voted questions from https://pollgab.com/room/brento on a hillside enjoying the fall desert weather.
https://www.youtube.com/watch?v=dXwD-nJ2K3c
I drove the Ferrari out through the Valley of Fire State Park and stopped out in the middle of nowhere to take the questions you'd upvoted at https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=k-XobIwWV6A
Let's say you need to troubleshoot someone's query performance, and they're using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging. Nic Neufeld showed how to use DBCC PAGE to do this, but today I'm going to cover another way: reading their statistics histogram.
Paul White (Blog, @SQL_Kiwi) is a legendary SQL Server blogger who's written a ton of good material. You should read all of it. You won't, and I can say that with authority because...I don't read it all either. It's too much.
But when your job eventually requires you to understand temp tables way better than you do today, read these 3 posts in this order - which is probably different than what Paul would recommend, but I think they flow better this way:
When you're doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server's estimates. Ideally, you want your delete/update/insert (what - what'd you think I meant?) queries to be as simple as possible - even a simple join can cause SQL Server to do wildly, wildly incorrect estimates, which affects memory grants, parallelism, wide vs narrow plans, and more.
Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here's what the table looks like:
And here's what my starting stored procedure looks like:
[crayon-6a6eb7fe5ce87832367548/]
It works:
I get this question a lot, so let's set up an example with the Stack Overflow database. My query's goal is to:
Find the top 5 Locations
List the users who live in those top 5 Locations, alphabetized by their DisplayName
Thinking Of You
When people think about Heaps and the problems they can cause, they don't often think of temp tables and table variables as Heaps.
Of course, without a clustered index, any table is a Heap.
Hell Here! Get Your Fresh Hell, Here!
Let's face it -- as far as developer tools go, SSMS is pretty bad.
Intellisense? The jokes write themselves.
You're Crazy
In Part 1, I showed you code that I think has some anti-patterns in it.
In case you didn't recognize it, it's actually code that Microsoft wrote. It's from sp_delete_backuphistory, and it is plum awful.
True story
You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore.
Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks).
Don't make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one's sure what they're doing, but they've been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…
No, not #tables - actual tables. Here's how: [crayon-6a6eb7fe5e990754090862/] The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted. Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data…
SELECT...INTO is one of my favorite SQL Server features. It's great for creating table skeletons with false WHERE clauses (1=2), moving a real table to a staged/temp location for testing, etc. In SQL Server 2014 It acquired the ability to go parallel, which is pretty neat, but that's not what we're talking about here. It…
By now, you have probably heard of CTEs And you may have even heard them referred to as Inline Views. Really, an Inline View can be any type of derived table. It's very easy to illustrate when one may turn into a performance problem with CTEs, if you aren't careful. A lot of people think…
Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014.
This video is a recording of a live webcast, so please excuse any audio and video imperfections. Want the scripts from the webcast? Scroll on down.
There's a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let's take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called "inline specification of CLUSTERED…
