which is better @ or # temp table declaration

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

SQL Server Development (2000)

which is better @ or # temp table declaration

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-31 : 07:15:05

vas writes "i have seen that we can declare a tempory table by
declare @temp table

also #temp

which is better
and i have seen in sql reporting #temp declaration causes problem
please can anyone give me the exact diff
and which is efficient method of declaration"

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 08:46:40

Hi vas, Welcome to SQL Team!

#temp will create a physical table in tempdb.
@temp will create the table in memory, but may create it on disk

#temp can therefore be slower, and possibly use up all available space in tempdb.

#temp will rollback [I'm pretty sure], @temp definitely won't

You can only have a PK on @temp, I'm pretty sure you can have regular indexes on #temp as well as a PK

##temp tables are global, no global equivalent of @@temp

You cannot do

SELECT *
INTO @temp
FROM ...

INSERT INTO @temp
EXEC MyStoredProcedure ...

BoL says that @temp used in SProc will cause fewer recompilations of the SProc than #temp

That's about all I can think of

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 08:57:22

Temp table can handle many rows when compared with table variable
You can alter temp table but you cannt alter table variable

Madhivanan

Failing to plan is Planning to fail

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 12:06:45

As for the effects in SQL Server Reporting Services, the designer will throw a funny message if your stored procedure uses a temp table, something to the effect of it cannot determine the columns. However, if you simply click on the Refresh Columns button and on the Execute Dataset button on the dataset tabs, it figures them out just fine.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources