I have a web application where I often need to temporarily store data, do a
bunch of processing, counts, etc... and then the data is not needed anymore.
I know I could just use temp tables, but I'm wondering what are the
pros/cons of the alternatives. (Table variables are not an option in my
case)

1) using #temp tables
2) create/drop permanent tables on the fly
3) use a single permanent table and insert/delete records as needed

Ali
that depends on Disk Vs performance
if dont have disk constraints and logging is not an issue then go for
permanent table  with insert/truncate through sp.
if It is ok for you to work on tempdb in terms of performance go for #temp
tables
I dont go for 2nd option.

Regards
R.D


Show quoteHide quote

R.D,

Are you talking about option #3?  With a single permanent table, it would
have to be an insert/delete not insert/truncate because the table would
store information for other sessions.

It depends.  Temporary tables--that is, local temporary tables--are local to
the connection, and therefore don't suffer from lock contention or
escalation.  On the other hand, creating and dropping tables can be
expensive because of all of the activity in the tempdb system tables
(sysobjects, syscolumns, sysindexes, etc.).  If the application initiates a
separate connection per session, then the create/drop activity can
significantly limit scalability.  The same holds true if you create/drop
permanent tables on the fly, but another limitation of that alternative is
that you have to generate distinct permanent table names for each
connection.  With a single permanent table you run the risk of lock
escalation and contention, and if the database uses the full recovery model,
then the transaction log can grow rapidly.  The disk allocations as the
transaction log grows can also stifle performance and scalability.  Another
solution would be to create a separate database for semi-permanent tables
that uses the simple recovery model.  Each semi-permanent table would
contain a spid column which would be used to differentiate between rows
belonging to different connections.  The first and last operations on a
connection would have to be a delete of any currently existing rows for the
spid.  This approach minimizes, but does not eliminate, lock contention, and
also minimizes the activity in the system tables.  Your best bet is to try
the different approaches and see which performs and scales best.

Show quoteHide quote

Hi

I don't think table variables have been mentioned in the replies so far as
an alternative when using SQL 2000, also you may be able to eliminate the
use of some temporary tables completely by using derived tables in your
queries. It will depend on what/why you are doing this, it could be that you
may need to eventually implement a full OLAP system.

John

Show quoteHide quote

Test for numerics?
Update Lock
select variable in from statement
Date/String format
PRIMARY KEY