Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Comparing Table Variables with Temporary Tables Expand / Collapse
Author
Message
Posted Tuesday, June 9, 2009 9:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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
Post #732005
Posted Wednesday, June 10, 2009 12:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:36 AM
Points: 652, Visits: 1,901
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.
Post #732047
Posted Wednesday, June 10, 2009 1:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Yes, Thats what I call the Comparison...
It's Really Informative.

Thank you,

Cheer!
Sandy.


--
Post #732056
Posted Wednesday, June 10, 2009 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 1, 2010 10:41 AM
Points: 2, 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
Post #732141
Posted Wednesday, June 10, 2009 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #732168
Posted Wednesday, June 10, 2009 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 2,667, Visits: 4,091
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.
Post #732195
Posted Wednesday, June 10, 2009 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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
Post #732197
Posted Wednesday, June 10, 2009 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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
Post #732205
Posted Wednesday, June 10, 2009 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #732210
Posted Wednesday, June 10, 2009 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #732218
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse