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
WayneS
WayneS
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48955 Visits: 10847
Comments posted to this topic are about the item Comparing Table Variables with Temporary Tables


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, 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

David McKinney
David McKinney
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5711 Visits: 2096
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
IN_Sandeep
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3181 Visits: 1255
Yes, Thats what I call the Comparison...
It's Really Informative.

Thank you,

Cheer!
Sandy.

--
Todd Johnson-227559
Todd Johnson-227559
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 13
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
Grant Fritchey
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220840 Visits: 33549
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
OCTom
OCTom
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5969 Visits: 4152
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
WayneS
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48955 Visits: 10847
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, 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

WayneS
WayneS
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48955 Visits: 10847
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, 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

GSquared
GSquared
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138471 Visits: 9731
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
GSquared
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138471 Visits: 9731
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
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