Sunday, February 7, 2010

SQL Server 2008 Table Variables vs Temporary Tables

I was looking into difference between these. When to use which one. Got some ideas after reading here and there. Thought to compile this so that it might be helpful for others too.

Temporary Tables:

CREATE TABLE #T1(col1 INT NOT NULL);
INSERT INTO #T VAUES(1)
OR
SELECT ID INTO #T FROM dbo.Nums WHERE ..
  1. Temporary Tables are defined using #
  2. They are always stord in tempdb database
  3. Temporary tables take part in transactions
  4. Scope of temporary tables is current and inner queries.
  5. If tables has named constraints then DB never caches these. So try to use contraints like unique etc only for better performance
  6. Good for data set if it is big as SQL run statistics on these to optimize.
  7. Not good for small data set lot of recompile happens

Global Temporary variables:

  1. Defined using ##
  2. Have global scope
  3. Once nobody is using these SQL server cleans from tempDB.

Table Vairable:


DECLARE @t TABLE(ID INT, PERSON NVARCHAR(200) NOT null)
Select * from @t
Insert into @t Values(...)

  1. SQL doesn't compute statistics so generally slower if data is too big.
  2. Less recompiles so good if data is small.
  3. Stored in tempdb database
  4. Table definition cannot be changed once declared
  5. Doesn't take part in outetransactions
  6. Explicit indexes cannot be created for these.
  7. Only accessible in current contxt. Not in outer or inner queries.
  8. For Table Variables NSERT SELECT happens with minimal logging in SQL 2008.


No comments:

Post a Comment