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


I may need to re-think the use of table variables


I may need to re-think the use of table variables

Author
Message
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9725 Visits: 1092
Comments posted to this topic are about the item I may need to re-think the use of table variables
Sean Redmond
Sean Redmond
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4889 Visits: 1086
I don't really see the point of table variables.
How are they an improvement on temporary tables? Is the absence of CREATE TABLE (or INTO, if you that way inclined) and DROP TABLE really a clinching argument?
Kev Riley
Kev Riley
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8519 Visits: 2724
One of the benefits of table variables is that they don't cause recompiles - whereas adding data to a temp table can. Now, this is the crux of the issue - without a recompile there is no way of knowing how many rows are in the table (hence bad stats), but the saving in overhead of doing the recompile can be a performance boost. This is why when they were introduced in SQL 2000, many people flocked to them in droves to 'improve' the speed of procedures that were using temp tables. It's more likely that statement level recompilation in SQL 2005 had a better effect.
So as ever, you need to weigh up the options of all possibilities.

Another benefit is that they survive transactions - so if you are logging some progress and then have to rollback, the table variable is unaffected (as are all variables)
Dave Poole
Dave Poole
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64126 Visits: 4053
If you understand the limitations of table variables then you have a tool you understand. You will understand when they will cause problems and when they won't. I don't shy away from chisels because I cut myself trying to use one as a flat blade screwdriver, I now know what chisels are for and that they are useful, nay nay essential, in certain situations.

LinkedIn Profile
www.simple-talk.com
Knut Boehnert
Knut Boehnert
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 449
As a rule of thumb I use table variables like scrap paper: Write a few lines, do not depend on it, use for a small set of data only.
Say less then 10,000 rows of data, no real indexing needed, then a table variable will do the job just fine.

Anything above that needs a proper bound notepad, ehm, temporary table. Might look a bit rough around the edges in naming scheme, just absolute necessary indexes only.
And a table is like a book - properly bound with a good cover and back, has one or more indexes (whereas the book has indices) and much thought has gone into the naming (grammar rules).
DesNorton
DesNorton
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20290 Visits: 8772
Wayne Sheffield posted a good article here on SSC
Comparing Table Variables with Temporary Tables


How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Brent Ozar
Brent Ozar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 616
Thanks Ben, glad you liked the post!



Mark DAgosta
Mark DAgosta
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 12
This article is just a few days late (how inconsiderate of you). Last week I inherited a monster stored proc with major performance problems (SQL 2012). After a few wasted hours of analysis, a coworker suggested replacing the table variables with temp tables. Run time went from 11.5 minutes to 6 minutes! And we're not talking millions of rows in the table variables; more like tens of thousands. Lesson learned. Will revisit in 2019. Thanks for a useful article!

roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9174 Visits: 2026
When articles like this come up (i.e. "this feature sucks at scale") I shake my head.

First, why weren't they testing the feature at scale?

But more importantly, it brings up the KISS principle yet again. All these niche features might solve one (miniscule) class of problem but there are so many of these little specialty features! As a result SQL Server is becoming more bloated all the time with a large chunk of features that are more death trap than savior, performance wise.

At the same time the basic engine (the old CRUD statements) tend to have a lot of attention poured on them, meaning they tend to be blazingly fast. It's the same old same old, developers want to write new flashy features that are interesting and fun and tend not to want to polish the old stuff.

The result is a big messy glob of features that half-work, piled on top of the core engine.

I have always said a few well chosen tools are worth a toolbox full of junk. Keep it simple. Avoid flashy new gimmicks. I mean, a 50% speed increase simply from shifting from table variables to temp files? Really?

A lot of SQL Server features are like that, half-baked or incredibly narrow in scope. The SQL Server team needs to focus on elegance, not a laundry list.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113982 Visits: 15224
I only use table variables for stored procedures that I know will query only a relatively small number of rows. For example, a procedure that gets called by a high traffic web application or call center application. With few exceptions, it has no place in the realm of reporting or ETL where a temp table is a more appropriate choice.

The new cardinality estimator for table variables is an improvement, but perhaps Microsoft could also extend the OPTIMIZE FOR query hint, allowing us to override the default cardinality assumption of 1 for table variables (ie: optimize for X number of rows).


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
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