I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Biggest difference between Temp tables and Table variables in Sql Server

We've all heard about differences between temporary tables and table variables in SQL Server.

They include performance, storage in memory or disk, tempdb use, etc.

 

But the biggest and mostly overlooked difference is:

Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.

Temp tables behave same as normal tables and are bound by transactions.

 

A simple example shows this difference quite nicely:

 

BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )

insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

select * from #temp
select * from @var

ROLLBACK

select * from @var
if object_id('tempdb..#temp') is null
    select '#temp does not exist outside the transaction'

 

We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.

 

This little known fact can be very handy in a bunch of different scenarions.

I showed one with auditing here.

 

kick it on DotNetKicks.com

Print | posted on Sunday, May 13, 2007 5:11 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

good one
12/30/2007 11:15 AM | Sanjiv
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Superb Explanation...
7/18/2008 12:11 PM | mouli
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

very good explanation
10/13/2008 3:55 PM | Thanigainathan S
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Nice Example
12/17/2008 5:58 AM | Sivakumar
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Really a good and helpful explanation. Keep going.
3/5/2009 10:50 AM | Raghu
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Very well explained
4/10/2009 7:03 AM | Barnana
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Ultimate example.....
4/25/2009 11:56 AM | KK
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Amazing one
8/23/2009 1:46 PM | Munish
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Excellent One... 100% working ... Such a gr8 article
10/3/2009 12:56 PM | Kalyan
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Good diff.
11/23/2009 4:45 AM | Mukesh
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

I like the way of explanation. Very good one. Need to bookmark.....
11/29/2009 12:54 PM | Kiruthika
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Good one....i liked the way of explanation....
12/17/2009 8:27 AM | prasad
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

A nice example to show an important distinction.
3/7/2010 5:12 AM | Surojit
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Nice explanation....... Keep it up
3/12/2010 1:44 PM | Raj Thakur
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

1) Transaction logs are not recorded for the table variables, hence they are out of scope of the transaction mechanism.
While Transaction logs are recorded for the temporary table.

2) Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables
can be statically compiled in advance.

3/29/2010 4:45 PM | Pravin S. Ahire
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

good point.
4/1/2010 2:02 PM | mohideen
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Good Explanation. Really It clarified me.
4/5/2010 9:49 AM | Santosh Kumar
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

very good and simple example...

Table variables don't participate in transactions, logging or locking. This means they're faster.

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option

You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.
5/27/2010 12:20 PM | Pawan Kumar
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

very good
6/21/2010 4:44 PM | siva
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Excellent Example
7/9/2010 4:42 PM | Pushkar Gupta
Gravatar

# What is the service level difference between sql server 2000 and 2005

What is the service level difference between sql server 2000 and 2005
9/21/2010 1:20 PM | suba
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Excellent Explanation. It is very helpful.
10/1/2010 11:51 AM | Suresh Kala
Gravatar

# re: Biggest difference between Temp tables and Table variables in Sql Server

Amazing.....
10/4/2010 5:36 AM | Badri
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET