Disclaimer
I found this totally by accident, and it even surprised me.
You can probably guess the TL;DR on this is yes, huh?
Dude, where’s my table variable?
I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.
Okay, so it’s just embarrassing.
Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DECLARE @UserId TABLE (UserId INT NOT NULL) INSERT @UserId ( UserId ) VALUES ( 22656 ) SELECT p.OwnerUserId, COUNT(*) AS Records FROM dbo.Posts AS p LEFT JOIN @UserId AS ui ON ui.UserId = p.OwnerUserId GROUP BY p.OwnerUserId GO DECLARE @UserId TABLE (UserId INT NOT NULL PRIMARY KEY CLUSTERED) INSERT @UserId ( UserId ) VALUES ( 22656 ) SELECT p.OwnerUserId, COUNT(*) AS Records FROM dbo.Posts AS p LEFT JOIN @UserId AS ui ON ui.UserId = p.OwnerUserId GROUP BY p.OwnerUserId GO |
What I saw in the query plans was amazing.
The second JOIN query had no table variable operator in it. It had been optimized away.
Well, that’s pretty cool. But that doesn’t seem to be anywhere in the XML. Here’s a pasted plan: https://www.brentozar.com/pastetheplan/?id=H1HbqOxae
Crying Game
Since I’ve never seen this before with a table variable, I thought I might have stumbled on some crazy enhancement for them. But apparently it’s just regular ol’ join simplification.
Thanks for reading!
Brent says – don’t feel bad if you have to read this twice to understand what was going on. It took me three times before I caught that it was a left outer join.
15 Comments. Leave new
Hi,
Does it do that because the table variable now has stats available to it because you added the primary key? Before you added the key it also could have duplicate rows in the table but with the primary you can’t.
Also did you notice that the optimizer did full optimization to the second batch and called the first one trivial?
Thanks Erik!
Statistics? No, not on a table variable. I believe it’s the guaranteed uniqueness introduced by the PK/CX that gives us the moxie.
And yeah, I noticed at the time, but adding a WHERE 1 = 1 didn’t change the plan.
Thanks!
> I believe it’s the guaranteed uniqueness introduced by the PK/CX
Yeah that’s the reason. In the first example rows could be multiplied if the same ID was present more than once in the table variable – hence the scan and merge join.
Hey Erik,
Does it not happen because your TVF is inline, not a multi-line one (with a RETURN clause)?
I’d only expect to fool/smarten the optimizer by using the multi-line TVF
Constantine
There is no table-valued function in this script, only a table variable.
Indeed, thanks
This doesn’t take into account what happens if you try to insert a couple thousand records with an index/PK on the table variable, nor if you create the index after the insertion. From my experience that tends to be pretty expensive.
You are free to take the dearth of examples here and use that to fill in the blanks on my opinion of using table variables with that many rows in them 😉
Erik,
I’m new here, so can you enlighten my on your opinion regarding table variables with “that many rows”.
I’ve recently stumbled onto an issue with running a query (in Access) against a linked SQL table and a local Access table; obviously this is slow.
So I thought about using a pass-through query, but the Access table does not exist on the SQL Server and I figured out how to create either a temp table or a table variable and insert values into that table from within the pass-thru query. Then perform the join and return the recordset to Access.
But in some instances, the temp table, or table variable may contain several hundred to 1000 values. Thus the desire to understand your opinion on the issue of table variables with large number of records.
Dale — what matters most is what you’re doing with the temp object after. You mentioned a join, so… How many rows are you joining?
To make sure there’s no time wasted, you should ask your question with as much detail as possible over on dba.stackexchange.com
Thanks!
I think I found the answer to my question in the video: Table Variables, Temp Tables, and OPTION RECOMPILE
Looking forward to digging into your site!
The reason it doesn’t reference the table in the second query is because the index contains all the data it requires, i.e. the index covers the UserId. If you had a second column in your table, e.g. Name, and were referencing that, SQL would need to jump from index to table to get the data it requires, but it already has UserId from the index, hence it doesn’t need to make that extra jump.
I thought a Clustered Index contained all rows!!!
All columns sorry!!!
It does.