What is the difference between a #Temp table and an @Table variable in MS SQL?

Posted on 2012-09-03
Medium Priority
Last Modified: 2012-09-04
What is the difference between a #Temp table and an @Table variable in MS SQL?

I understand that both are crated in the TempDB. I also understand the creation and scope difference between @Table, #Table and ##Table.

But in simple/straightforward terms, what is the real difference?

When would I use one over the other?

Question by:jxbma
LVL 13

Accepted Solution

p_nuts earned 1000 total points
ID: 38362468
There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

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.

Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.)

Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

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

You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).

Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

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.

Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:
MSDN FAQ on difference between the two: http://support.microsoft.com/default.aspx/kb/305977

MDSN blog article: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

Article: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047
LVL 31

Expert Comment

ID: 38363941
Simply said: If you don't have to worry about maintaing tables in database use #temp table.
It is created as a normal table but is managed by the database, will be cleared when you exit SQL.

@t is a temporary table variable returned from a table valued function. @t =@retuen_table in the following example
if object_id(N'ufn_multistatement_table_valued',N'TF') is not null
	drop function dbo.ufn_multistatement_table_valued  ;
create function dbo.ufn_multistatement_table_valued 
(@parameter_name  nvarchar(25))
RETURNS @retuen_table TABLE (a nvarchar(25) Primary Key,r nvarchar(25))
	declare @a nvarchar(25)
	set @a='3'
	INSERT @retuen_table SELECT a, r FROM A WHERE a>@parameter_name
SELECT * from dbo.ufn_multistatement_table_valued('1');

Open in new window

@v is a temporary variable, you can use to manage your table records.

declare @v Int;
select @v=a from A where a=1;
select @v; -- you can manipulate @v and modify the records.

Open in new window


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question