SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need an opinion of temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/05/2012 :  02:36:56  Show Profile  Reply with Quote
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"?

Edited by - KilpAr on 12/05/2012 02:37:29

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/05/2012 :  05:06:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 - 12/05/2012 :  05:26:37  Show Profile  Reply with Quote
What is SP in this context?

Edited by - KilpAr on 12/05/2012 05:30:50
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/05/2012 :  05:28:22  Show Profile  Reply with Quote
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 - 12/05/2012 :  05:31:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/05/2012 :  06:07:10  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 - 12/05/2012 :  08:04:30  Show Profile  Reply with Quote
Thanks, I will try this as an optimization.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000