Temporary Tables

Question

Post reply

Temporary Tables

texpic

SSCertifiable

Points: 5887
More actions
March 1, 2007 at 7:16 am

#123131

I have a few questions about temporary tables:
1. Is there a way to know which tables have not been dropped? Some statement like "SELECT TempTableActive"
2. When does a temp table drop. Obviously when I do a DROP TABLE. I'm assuming if I reboot the computer it also goes away. Any other times?
3. How do I create an identity field in a temp table? Example:
SELECT Seq (identity), AccoutId, Amount INTO #TempA FROM MyTable
4. Any other tricks or tips that might be useful on temp tables would be appreciated.
Thanks!!!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Chris Jones SSC-Addicted Points: 445 More actions · Answer 1

IN 2005 this appears to answer point 1

Create

Table #XXXX(id int)

Select

* From tempdb.INFORMATION_SCHEMA.TABLES

where

TABLE_name like '%x%'

drop

table #XXXX

Select

* From tempdb.INFORMATION_SCHEMA.TABLES

where

TABLE_name like '%x%'

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

"When does a temp table drop. Obviously when I do a DROP TABLE. I'm assuming if I reboot the computer it also goes away. Any other times?"

A temp table (at least one that starts with one #) is only visible to the connection that created it. So as soon as your connection ends, the temp table disappears.

"How do I create an identity field in a temp table?"

Use the CREATE TABLE statement the same as you would for a normal table.

John

schleep SSChampion Points: 12583 More actions · Answer 3

Indices are allowed on #temp tables, and depending on the number of rows in the table, can dramatically affect performance.

Don't have a #temp table in one proc and call a proc with a #temp table of the same name. Worked in 6.5, doesn't work in 2000.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Well that still works.. depending on what you want to do with it. The child proc will be able to access the temp table as if it had created it.

Jeff Moden SSC Guru Points: 1004187 More actions · Answer 5

Sw...

I'd like to recommend that you look up "Temporary Tables" in Books Online as well as making a visit to the following web-site...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

Between the two, you should have just about all the information you could ever ask for on temporary tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

Not sure about

in this link metion about

Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

Thx.

Jeff Moden SSC Guru Points: 1004187 More actions · Answer 7

It's correct... there is a very minor amount of logging for Temp Tables and none for table variables. The fact that Temp Tables can use statistics and Table Variables cannot usually makes up for the small difference in performance that that bit of logging does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 8

The text has been made a bit confusing by the red/blue coloring... so just to make sure nobody misunderstands that : not table variables, but transactions (involving these table variables) last only for the duration of update.

Transactions that involve table variables last only for the duration of an update on the table variable.