Search code examples
sql-servertransactionstemp-tables

Temporary tables and transactions in sql 2005

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 ).

Solution

  • 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.