June 9, 2009 at 9:15 pm
Comments posted to this topic are about the item Comparing Table Variables with Temporary Tables[/url]
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
June 10, 2009 at 12:33 am
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.
June 10, 2009 at 1:05 am
Yes, Thats what I call the Comparison...
It's Really Informative.
Thank you,
Cheer!
Sandy.
--
June 10, 2009 at 5:24 am
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
June 10, 2009 at 6:15 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2009 at 6:45 am
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.
June 10, 2009 at 6:46 am
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
June 10, 2009 at 6:52 am
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
June 10, 2009 at 6:55 am
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
June 10, 2009 at 7:00 am
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
June 10, 2009 at 7:34 am
Good job. Very informative and usefule.
June 10, 2009 at 7:38 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2009 at 8:31 am
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
June 10, 2009 at 8:34 am
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.
June 10, 2009 at 8:40 am
Excellent article!
Very clear and informative!
Viewing 15 posts - 1 through 15 (of 163 total)
You must be logged in to reply to this topic. Login to reply