Temporary tables and transactions in sql 2005 [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the problem by editing this post.

开发者_StackOverflow社区

Closed 2 years ago.

Improve this question

I write app in c#. I use temporary tables inside transaction. My server is sql 2005. Is there any performance threat, I read somewhere that using temporary tables inside transactions should be avoided ( http://www.sql-server-performance.com/tips/temp_table_tuning_p1.aspx post at the bottom of the screen added at 2-24-2003 ).

---

This is quite easy to test.

In one Query window run the following

BEGIN TRAN

CREATE TABLE #T1(I INT)
INSERT INTO #T1 VALUES (1)

Then in another Query Window run the same. You will find that there is no blocking.

So the claim in that tip that

it would prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

seems untrue.

---

The answer is in the first row: "In general, temp tables should be avoided, if possible." Look at your application if is fast enough. Try to avoid a design based only on temp tables. Using of temp tables should be an exception inside of an application not a rule. Try to find an alternative design without increasing the cost ( time spent to build the new design ).
Look at this article to see how performance is influenced by the amount of data: http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx