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

Posted on 2012-09-03
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

    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:

    MDSN blog article:

    LVL 9

    Expert Comment

    LVL 30

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now