|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869,
Visits: 963
|
|
| Good job. Very informative and usefule.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,381,
Visits: 25,172
|
|
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 2012 Query Performance Tuning SQL Server 2008 Query Performance Tuning Distilled and SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:15 AM
Points: 56,
Visits: 487
|
|
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
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 3:15 PM
Points: 85,
Visits: 607
|
|
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.
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 6:34 AM
Points: 476,
Visits: 1,361
|
|
Excellent article! Very clear and informative!
|
|
|
|
|
SSC-Dedicated
           
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
|
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370,
Visits: 8,235
|
|
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 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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 2,278,
Visits: 2,999
|
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370,
Visits: 8,235
|
|
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 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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386,
Visits: 823
|
|
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.
|
|
|
|