Using #TEMP Tables

Question

Using #TEMP Tables

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
September 14, 2012 at 1:16 am

#281539

Is it good to use Temporary Tables in the Stored procedure or is it better to use actual tables instead of Temp tables????
************************************
Every Dog has a Tail !!!!! :-D

Viewing 6 posts - 1 through 5 (of 5 total)

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

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 1

Depends what you need.

#Temporary tables can only be accessed by the instance of stored procedure that is running. If two instances of the stored proc are running they each get their own version of the #Tmp table.

If you create dbo.Tmp in tempdb - there is only one tempdb.dbo.Tmp table - your stored proc will have to check if it exists, if not create it, and deal with all the issues of possibly sharing the table with other instances of the proc.

But if you want the data to be available outside the proc, just use a real table in the database, because tempdb is recreated when the server restarts, and it is not backed up.

SQL Show SSCertifiable Points: 6143 More actions · Answer 2

SQL Show

SSCertifiable

Points: 6143

September 14, 2012 at 7:39 am

#1537823

Do #tables cause stored procedure recompilation???

SQLmountain SSCommitted Points: 1505 More actions · Answer 3

Creating and dropping temporary tables forces a recompile.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Partial recompile.

Statement-level, not proc-level.

Example here: http://sqlserverplanet.com/sql-optimization/temp-table-recompiles/

Excellent article on table variables and temp tables and other options: http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 5

GSquared (9/14/2012)
Partial recompile.
Statement-level, not proc-level.
Example here: http://sqlserverplanet.com/sql-optimization/temp-table-recompiles/
Excellent article on table variables and temp tables and other options: http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

Thanks: very useful information. 🙂