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


Comparing Table Variables with Temporary Tables


Comparing Table Variables with Temporary Tables

Author
Message
cy-dba
cy-dba
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2617 Visits: 963
Good job. Very informative and usefule.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223885 Visits: 33569
WayneS (6/10/2009)
Grant Fritchey (6/10/2009)
Well done. Extremely thorough, accurate, well written... but what's with the attached Word file?


I wrote the article in Word, and I had asked Steve to include it as an attachment to the article so that the viewers could download it for easier printing.... I guess he interpreted that to mean to just make the article this way. I'm sorry for any confusion about that, and it's my fault that my desires weren't communicated clearly.


I'm not complaining. I was just confused for a couple of moments. But then, I am easily confused.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
thisisfutile
thisisfutile
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1039
Excellent article. I'm sure this took some time to produce.

Thank you for debunking the "only in memory" myth for temp tables and table variables. I've never had time or resources to explore them. We have a small production server (Windows 2003 SBS) and I've always avoided using tables variables because memory is an expensive commodity in an SBS environment. I'll actually consider these now.

Thank you for taking time to write and share this.

Gabe
Dean Cochrane
Dean Cochrane
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 641
Damn good article. I knew that table variables didn't have stats computed for them, but I hadn't fully considered the implications of that.

One point which your article doesn't make explicitly is that table variables live outside transactions. I realize that you show this in the comparison table at the bottom, but I think an article as good as this one should give this point at least a mention in the Other Differences section, because this behavior is 1. potentially useful and 2. an (admittedly) rare source of difficult-to-find bugs.
Goldie Lesser
Goldie Lesser
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3574 Visits: 1513
Excellent article!
Very clear and informative!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)SSC Guru (332K reputation)

Group: Administrators
Points: 332023 Visits: 20117
I've put it back in the article text, so you can read it online with colored formatting.

My apologies to Wayne for not getting this done sooner. I thought I had saved it, but apparently I didn't.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49682 Visits: 10851
Dean Cochrane (6/10/2009)
Damn good article. I knew that table variables didn't have stats computed for them, but I hadn't fully considered the implications of that.

Thank you.

One point which your article doesn't make explicitly is that table variables live outside transactions. I realize that you show this in the comparison table at the bottom, but I think an article as good as this one should give this point at least a mention in the Other Differences section, because this behavior is 1. potentially useful and 2. an (admittedly) rare source of difficult-to-find bugs.

Excellent point. While the article doesn't explicitly state that table variables live outside of transactions, at the beginning of the article, under the "Table Variables" section, points 3) and 4) both talk about how table variables interact with transactions.

Edit: clarification on transactions within the article.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Adam Haines
Adam Haines
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13592 Visits: 3135
Great job Wayne! This article is definitely a good read. :-)



My blog: http://jahaines.blogspot.com
WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49682 Visits: 10851
Steve Jones - Editor (6/10/2009)
I've put it back in the article text, so you can read it online with colored formatting.

My apologies to Wayne for not getting this done sooner. I thought I had saved it, but apparently I didn't.


Thanks Steve. I personally think that the online article looks better now.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Andy Lennon
Andy Lennon
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 826
This is the perfect resource for Temp Table/Table Variable comparisons as far as i'm concerned. The graph is a particularly useful quick reference.
Well done, and many thanks.
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