Temp Tables Vs Temp variables

[Rick Hein]

I have an application that I am working on that uses some small temp
tables. I am considering moving them to Table Variables - Would this
be a performance enhancement?

Some background information: The system I am working on has numerous
tables but for this exercise there are only three that really matter.

Claim, Transaction and Parties.

A Claim can have 0 or more transactions.
A Claim can have 1 or more parties.
A Transaction can have 1 or more parties.

A party can have 1 or more claim.
A party can have 1 or more transactions. Parties are really many to
many back to Claim and transaction tables.

I have three stored procs
insertClaim
insertTransaction
insertParties

From an xml point of view the data looks like this
<claim>
<parties>

<info />
insertClaim takes 3 sets of paramters - All the claim level
information (as individual parameters), All the parties on a claim (as
one xml parameter), All the transactions on a claim(As one xml
parameter with Parties as part of the xml)

insertClaim calls insertParties and passes in the parties xml -
insertParties returns a recordset of the newly inserted records.

insertClaim then uses that table to join the claim to the parties. It
then calls insertTransaction and passes the transaction xml into that
sproc.

insertTransaciton then inserts the transactions in the xml, and also
calls insertParties, passing in the XML snippet

[WangKhar]

"Helmut Wöss" <h.*****@iis-edv.at> wrote in message news:<3f***********************@newsreader02.highw ay.telekom.at>...

yes, table variables are faster than temp tables because they are
used like normal local variables, so no lockings are necessary
and they are not created in tempdb (only running in ram)
But there are some other points to take care:
- table variables are not included in transactions, so no rollback possible
- you can't do something like:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements
- and you can't use them in subprocedures (because they are local).

Not strictly true - I believe that th table variables are on hard
drive as appropriate - try sticking a gig of data in one..
Check your query plans as well - the Table Variable dont get
statistics. This may have a relevance for you. I found that
paralellism _seems_ to be destroyed with table variables. Eg
inserting into a #table is fine with loads of parallelism, yet insert
into a seemingly identical @table and the paralelism disapears - in my
case turning the insert into 20 minutes instead of 30 seconds.

[Erland Sommarskog]

Rick Hein (rh***@mutualofenumclaw.com) writes:

I have an application that I am working on that uses some small temp
tables. I am considering moving them to Table Variables - Would this
be a performance enhancement?

In additions to other answers, see also
http://support.microsoft.com/default...b;en-us;305977.

I can confirm WangKhar statement that parallelism is not possible
when you insert into a table variable.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp