Are the any benefits to using a temp table vs a normal table?

Wouldn't it be better to create a normal table to minimize the load
on tempdb?

Yes, temp tables are for interim processing, normal tables are for permanent
storage.

One thing you get with temp tables is session independence.  E.g. two users
can create a table called "#foo" at the same time.  Not so with "normal"
tables.

Another is that they are automatically destroyed if you forget to.

That depends, is tempdb otherwise overloaded?

I guess if i had sufficent space allocated for tempdb it wouldn't be a
hit.

I was just wondering if there was a performance benifit for using temp
tables.  It sounds like there are none.

Take a peek, at least: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp


ML

Local temporary tables are visible only to the connection from which they
were created.  There is no lock contention for the table, nor any deadlocks
due to lock escalation.  In addition, tempdb uses the simple recovery model,
which means that only enough information is retained in the transaction log
to rollback any open transactions.  Once a transaction has been committed,
its log space is freed up for use by another transaction.  Production
databases should almost always use the full recovery model, which retains
log entries until the next log backup.  Using a normal table instead of a
temporary table can cause the log disk to fill up.

If you're concerned about the load on tempdb, put it on its own disk
subsystem, or add memory.  Temporary tables and table variables are created
and operated on within memory if it is available.

Show quoteHide quote

Transaction logging seems live a valid argument in favor of temp
tables!   One thing to note incase anyone else is reading this....
derived tables (from my limited experience) have the same issues as
table variables when dealing with very large datasets.

Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key
boolean programming
JDBC client - CURSOR as OUT parameter -supported ?
Creating a trigger using a cursor