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 2008 Forums
 Transact-SQL (2008)
 Need an opinion of temp tables

Author  Topic 

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-05 : 02:36:56
I have understood that in SQL Server it is possible to create temp tables. Why would I do so and not just create a database named something like "temptabledatabase" and create temp tables to there just to be dropped seconds later? If I go with those actual temp tables (not create and drop -system), what differences do I need to make to my code of "select into - select * - drop table"?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 05:06:52
If you create a table it is visible to all spids.
Say you have an SP which can be called by many users - that couldn't create a permanent table as the next instance would get an error.

Also if a process creates a table then fails the table would be left there - the next run would get an error as it couldn't create the table.

With temp tables you do not need to code drop statements (but you can). They will be created in tempdb - there can be issues if system databases have a different collation to the user databases so try to make sure that doesn't happen.

Also look at table variables for small amounts of data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-05 : 05:26:37
What is SP in this context?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 05:28:22
quote:
Originally posted by KilpAr

What is SP in this context?


Stored Procedure (SP)

--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-05 : 05:31:02
Also, since I'm creating the "custom kind of temp tables" with SELECT INTO, can I SELECT INTO to temp tables?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 06:07:10
Yes.
Try it and see.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-05 : 08:04:30
Thanks, I will try this as an optimization.
Go to Top of Page
   

- Advertisement -