Comparing Table Variables with Temporary Tables

  • WayneS

    SSC Guru

    Points: 95363

    Comments posted to this topic are about the item Comparing Table Variables with Temporary Tables[/url]


    Article errata:

    1. In the article is this paragraph:

    Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘<objectname>’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.

    The first sentence does apply to indexes; the last two sentences do not.

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • David McKinney

    SSChampion

    Points: 10358

    Looks to me like you've pretty much covered the A-Z of the subject.

    Well done!

    I never realised, for instance, that temporary tables are still in scope to nested procedures. I'm going to rewrite some code based on this realisation.

    Very useful indeed.

    David McKinney.

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Yes, Thats what I call the Comparison...

    It's Really Informative.

    Thank you,

    Cheer!

    Sandy.

    --

  • Todd Johnson-227559

    SSC Enthusiast

    Points: 118

    I enojyed reading your article. It verfied some of the rumors I have heard over the years.

    I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."

    Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?

    I was wondering if I have been doing something wrong over the years. I typically used global temp tables to build cross-tab queries in the database for large amounts of data.

    Thanks again,

    T-Odd

  • Grant Fritchey

    SSC Guru

    Points: 396476

    Well done. Extremely thorough, accurate, well written... but what's with the attached Word file?

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • OCTom

    SSChampion

    Points: 11755

    Hey. I really like the article. It brought the subject together in a nice format. I love the use of the word document. I have a folder for this type of documentation and I will be saving it for future reference.

    I recommend that other relatively complex subjects be offered in this manner.

    Nice job.

  • WayneS

    SSC Guru

    Points: 95363

    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.

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS

    SSC Guru

    Points: 95363

    Todd Johnson (6/10/2009)


    I enojyed reading your article. It verfied some of the rumors I have heard over the years.

    Thank you

    I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."

    Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?

    If you look at 3.b, I go through an example of how the global temp table goes away once the connection is ended.

    Now, if you're running this from a query window, and the code in that window "crashes" (but the window aka the connection is still active), then the table will still reside. As long as the connection that created the temp table (local or global) is still open, then the temp table will stay around until explicitely dropped.

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • GSquared

    SSC Guru

    Points: 260824

    Todd Johnson (6/10/2009)


    I enojyed reading your article. It verfied some of the rumors I have heard over the years.

    I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."

    Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?

    I was wondering if I have been doing something wrong over the years. I typically used global temp tables to build cross-tab queries in the database for large amounts of data.

    Thanks again,

    T-Odd

    Try this:

    Open two sessions in SSMS, in this first one, run this:

    create table ##T (

    ID int primary key);

    insert into ##T (ID)

    select 1;

    raiserror('Deliberate error', 20, 1) with log;

    In the second one, run this:

    select *

    from ##T;

    You'll find that the global temp table is not there, and will get an "Invalid object" error on the second query.

    I don't have an SQL 2000 server to try that on, but I've tested it on 2005 and 2008 and it does what it's supposed to there. I think I've tested it on 2000 before and found that it also works there, but I can't verify that today.

    The key is that the error has to be severe enough to kill the connection. Try it with severity 16,

    and the connection will still be open, so the global temp will still be valid.

    Edit: WARNING: Do not play with high-severity errors on a production server!!! Probably shouldn't have to mention that, but I'm going to anyway, just in case. I have a desktop instance of Dev Edition, with a ProofOfConcept database that's throwaway, and I do this kind of testing there.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared

    SSC Guru

    Points: 260824

    Good article. I like the idea of making it available in Word format. Kind of odd that it's only available that way, but I'm sure Steve can modify that, if it actually matters.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    Good job. Very informative and usefule.

  • Grant Fritchey

    SSC Guru

    Points: 396476

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • thisisfutile

    Hall of Fame

    Points: 3496

    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

    SSC Eights!

    Points: 962

    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

    SSCertifiable

    Points: 6155

    Excellent article!

    Very clear and informative!

Viewing 15 posts - 1 through 15 (of 164 total)

You must be logged in to reply to this topic. Login to reply