July 17, 2008 at 9:49 am
nice article....2 limitations to add to the list:
1) as "sbateman" mentioned, Table Variables cannot be used in SELECT ... INTO statements
2) Table Variables cannot be used in sub-processes, such as: EXEC, sp_executesql, a Trigger on a table updated by this proc or batch, or sub-Procedure calls. But Temporary Tables are just fine in these situations.
Something to note about performance: I cannot remember where I read this but it was mentioned that Table Variables, not existing as a full table like Temporary Tables do, only ever appear to the Query Optimizier to have one row in them regardless of how many rows really exist. So, as the number of rows increase, the Optimizer is more likely to choose an inappropriate (i.e. inefficient) execution plan--one that is designed for a small set of rows instead of a larger set of rows.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 17, 2008 at 10:06 am
Thanks for the great article.
July 17, 2008 at 1:04 pm
One big difference not mentioned in the article, but which influences the choice of when to use which, is that autostats do not apply to table variables, but do apply to temp tables. This means that for temp tables, a lot of time can be spent creating statistics, which may or may not be valuable. On the flip side you save that time with table variables, but the same argument applies -- if they were needed you're in trouble, if not you're in better shape. I tend to favor table variables over temp tables A) for smaller sets of data (2-3 data pages, max), a B) for situations in which I'm defining a PK on the table on doing all joins on that key. In the former situation I don't care as much about plan choice because the table is tiny, and in the latter situation statistics generally won't influence plan choice and so are not as necessary.
--
Adam Machanic
whoisactive
July 17, 2008 at 2:15 pm
blandry (7/17/2008)
If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?
Not quite right... Temp Tables and Table Variables both hit memory first.
Although very well written, one of the big things missing in this article is that Table Variables do not and cannot be made to use statistics. Depending on what you're trying to do, that can be a huge disadvantage or a huge advantage.
The other thing about Table Variables is they die as soon as the run stops... that can make them difficult to troubleshoot because you can't see their contents by doing a simple manual SELECT like you can with Temp Tables... it has to be done in the code and you have to rerun to get the Table Variable to populate every time you want to see it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2008 at 3:29 pm
Thank you all for your great feedbacks.
In this article I tried to illuminate things I didn't find in any other article. In order to find common differences between Variable table to Temp table I added in the beginning of this article 2 links:
1. Frequently Asked Questions - SQL Server 2000 - Table Variables
2. Is a Temporary Table Really Necessary? [/url]
Is there really any difference or advantage between Temp Tables and Table variables?
When is it advantageous to use a table variable?
While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables?
Yes. there is a different between those 2 in:
- Table variables result in fewer recompilations of a stored procedure as compared to temporary tables
- Table variables do not maintain statistics like temporary tables can (good for large tables)
- Temp table definition can be altered by alter table
- Select into and Insert Exec can be used only with Temp table
- Table variables require less locking and logging resources
- You can't create non-clustered indexes on table variable
More...
As I said, i tried to mention unknown behaviors.
Adam Machanic mention few differences (as I wrote above) and he's absolutely right.
Anyway, you should pick between them respectively and think of what you need before.
I'd like to get feedbacks if you'd like to have an article about all differences between those 2, and give more details then the paragraph above.
July 17, 2008 at 3:33 pm
A couple of small corrections:
A) INSERT EXEC actually does work on table variables in 2005 and 2008.
B) You can create nonclustered indexes on table variables, in the form of UNIQUE constraints.
--
Adam Machanic
whoisactive
July 17, 2008 at 9:50 pm
Thanks for the Article Roi. There is one more thing you forgot to mention. You can't write a correlated subquery on a table varable where as you can on a temp table. I regularly use that feature in my coding
July 17, 2008 at 9:54 pm
Viswanath S. Dhara (7/17/2008)
Thanks for the Article Roi. There is one more thing you forgot to mention. You can't write a correlated subquery on a table varable where as you can on a temp table. I regularly use that feature in my coding
Of course you can. You just need to learn to use correlation names.
DECLARE @t TABLE (x INT)
DECLARE @U TABLE (x INT)
SELECT *
FROM @t t
WHERE EXISTS (SELECT * FROM @U u WHERE u.x = t.x)
--
Adam Machanic
whoisactive
July 17, 2008 at 10:04 pm
Taken all together, a good round up of the differences, but you all just ruined one of my favorite questions for DBA candidates - 'Describe the differences between Temp Tables and Table Variables'. :crazy:
Maybe some of them dont read SSC, but then that would be a big point against them anyway ...
July 18, 2008 at 12:27 am
Hi Adam,
Your query works, but that is not what I am tring to do!
I am trying to update a table using a correlated subquery.
If I use a table variable here is the message I get.
I can do this with a temp table though. I always prefer them.
DECLARE @tab TABLE (id int)
INSERT INTO @tab SELECT 10
UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id)
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@tab".
July 18, 2008 at 1:35 am
one thing I've found in tuning client applications is that table variables when you populate them with a large number of rows, for example 15k and then put them back into a join with maybe 6 real tables can seriously give you query plan issues tending to force scans and making optimisation very difficult - a #temp table doesn't do this. ( it's the stats stuff )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 18, 2008 at 3:20 am
Very informative article....
July 18, 2008 at 4:31 am
Viswanath S. Dhara (7/18/2008)
Hi Adam,Your query works, but that is not what I am tring to do!
I am trying to update a table using a correlated subquery.
If I use a table variable here is the message I get.
I can do this with a temp table though. I always prefer them.
DECLARE @tab TABLE (id int)
INSERT INTO @tab SELECT 10
UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id)
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@tab".
Hi Viswanath,
try this:
DECLARE @tab TABLE (id int)
INSERT INTO @tab SELECT 10
UPDATE Tab
SET id = ( SELECT id FROM sysobjects WHERE id = tab.id)
FROM @tab Tab
Best Regards,
Chris Büttner
July 18, 2008 at 8:21 am
Viswanath S. Dhara (7/18/2008)
I am trying to update a table using a correlated subquery.
Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...
--===== Right way to do a "Joined" update
UPDATE TableA
SET SomeTableAColumn = b.SomeTableBColumn
FROM TableB b
INNER JOIN TableA a
ON a.SomeOtherColumn = b.SomeOtherColumn
Christian Buettner (7/18/2008)
Viswanath S. Dhara (7/18/2008)
Hi Adam,Your query works, but that is not what I am tring to do!
I am trying to update a table using a correlated subquery.
If I use a table variable here is the message I get.
I can do this with a temp table though. I always prefer them.
DECLARE @tab TABLE (id int)
INSERT INTO @tab SELECT 10
UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id)
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@tab".
Hi Viswanath,
try this:
DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10
UPDATE Tab
SET id = ( SELECT id FROM sysobjects WHERE id = tab.id)
FROM @tab Tab
Again, I wouldn't use a correlated subquery for this. But that's not the big problem here... what does this query do to the @tab table? Nothing... ID remains the same.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2008 at 8:26 am
Jeff Moden (7/18/2008)
Viswanath S. Dhara (7/18/2008)
I am trying to update a table using a correlated subquery.Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...
Your "correctly formed" update is non-ANSI Compliant and is nondeterministic in some cases. The query optimizer can--and usually does--optimize out correlated subqueries so that they're not reevaluated on a row-by-row basis. Certainly not worse than a cursor.
--
Adam Machanic
whoisactive
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply