Loading ...

Temp Tables vs Table Variables in sql server | CodeAsp.Net

Temp Tables vs Table Variables in sql server

4.82 
 /5
4.82 (1votes)

When writing SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have three table options: local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Temporary Tables

Both local and global temporary tables are physical tables within the tempdb database, indexes can be created .Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Create table #temp
(
[Id] Int,
[Name] Nvarchar(100)

)

You'll notice I prefixed the table with a #.This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server.

Table Variables

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

Declare @temp table
(
[Id] Int,
[Name] nvarchar(100)
)
You'll notice I write declare because @temp is a variable of type "Table".

The another difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

CREATE Table #temp([Id] Int,[Name] nvarchar(100))

DECLARE @temp table([Id] Int,[Name] nvarchar(100))

INSERT into #temp select 1,'India'

INSERT into @temp select 1,'India'

BEGIN transaction

UPDATE #temp set [Name]='Hindustan'

UPDATE @temp set [Name]='Hindustan'

ROLLBACK transaction

SELECT * from #temp

SELECT * from @temp

Here the out put

 IDName
11 India

 

 IDName
11Hindustan

After declaring our temporary table #temp and our table-variable @temp, we assign each one with the same 1,"India" . Then, we begin a transaction that updates their contents. At this point, both will now contain the same 1,"Hindustan" . But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

Which to use

  • If you have small record set use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.
  • If you need to create indexes on it then you must use a temporary table.

Comments (1)

   
Ajay
Ajay
Thankx dear.i was new to know of Temp varable and Table,Cleared now
Thankx a lot..........
11/3/2009
 · 
by

Top Posts