Temp tables question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp tables question

Author

Topic

overthetop
Starting Member

18 Posts

Posted - 2006-08-10 : 09:43:56

Hi

what is the difference between these tables

DECLARE @table1 TABLE
( .... )

and a temp table

create table #table2
( ... )

jeroena
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 10:08:08

I know for one thing you can't create an index on the first one. There quite some info in BOL as well on this.

Jeroen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 10:30:06

It's a big topic but in short temp table is a table created in the tempdb database where TABLE is a new datatype added in SQL 2000 which allows you to create tables which are created in the SQL Server memory. Refer BOL for more details.

" I know for one thing you can't create an index on the first one. There quite some info in BOL as well on this"
Actually jeroena, you can indirectly create index on table variable by adding primary key or unique constraint on table columns. But yes, directly it is not possible.

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-10 : 10:56:18

table variable is created in memory if there is space - if it grows large then it will be held in tempdb.
Can create constraints on a table variable during creation but not non-unique indexes.
Temp tables have more optimistaion options and you can create indexes on them after creation / population.
table variables are not available to called procedures whereas temp tables are.

Normally table variables are used for small locally used datasets whereas temp tables are used for larger datasets or those which need to be accessible across batches.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 11:09:06

That was perfectly complete answer with added flavour of simplicity..