Difference between Table Variables and Temporary Tables in SQL Server

Hello Folks,

You would have seen my blog-post which was about the Temporary Tables. If you want to follow to that post, please click the link:

This blog-post is all about Table Variables and the difference between the Table Variables and Temporary Tables in SQL Server.

Table Variables:

With the arrival of SQL Server 2000, Microsoft has introduced table variables as an alternate to use temporary tables.

  • Table variables store a set of records; therefore it will be declared as variables rather than created with SQL DDL statements. For e.g.,

Here, we are creating a table variable ‘@Student’, as shown below:

DECLARE @Student TABLE(
S_ID INT PRIMARY KEY,
StudName VARCHAR(50), 
StudBranch VARCHAR(50))

Now, if we want to insert some values, then:

INSERT INTO @Student VALUES ( 1, 'Piyush','Computer-Science');

If you want to see the records in the table, then:

SELECT * FROM @Student

1_SQL_Server_Difference_between_Table_Variables_and_Temporary_Tables

  • The one and only way that we can pass a table variable to another stored procedure as an input is in table-valued parameters, and then they are read-only in the called routine.
  • A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable as we create with a DECLARE statement.

Differences between Table Variables and Temporary Tables:

  • A table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
  • Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.
  • The table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in EXEC statements. Also, they cannot be used in an insert/exec statement.
  • Table variables are a superior alternative to using temporary tables in many situations. The ability to use a table variable as the return value of a UDF is one of the best uses of table variables.
  • Table variables can offer performance benefits and flexibility when compared to temporary tables.

Well, this was all about the Table Variables and the difference with the Temporary Tables.

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

8 Comments on “Difference between Table Variables and Temporary Tables in SQL Server”

  1. little addition

    table variable is out of transaction scope.

    Example:

    DECLARE @Student TABLE(

    S_ID INT PRIMARY KEY,

    StudName VARCHAR(50),

    StudBranch VARCHAR(50))

    CREATE TABLE #Student

    (

    S_ID INT PRIMARY KEY,

    StudName VARCHAR(50),

    StudBranch VARCHAR(50))

    Begin transaction

    INSERT INTO @Student VALUES ( 1, ‘Piyush’,’Computer-Science’);

    INSERT INTO #Student VALUES ( 1, ‘Piyush’,’Computer-Science’);

    RollBack transaction

    SELECT * FROM #Student

    SELECT * FROM @Student

  2. Hi Piyush,

    If we want to keep millions of record, that I think temporary table could be the good option as we create indexes on any of the column of temp tables however tables variables can have only primary key.

    Correct me if I am wrong here.

    -Abhishek

  3. Yes Abhishekh, you are very much right, for large data set we tend to use Temporary Table. It is the best option… 🙂

  4. No statistics is collected for table variables therefore estimated number of records selected from variable is always 1, so using them can lead to sub-optimal query plan and drastically decrease performance. To avoid it you have to use RECOMPILE OPTION which annihilates one of the benefits of table variables.

  5. Alexander’s comment is very important. The QO’s rowcount estimate of 1 quickly leads to bad plans if the table variable has more than a few rows. You may not see this until the code is deployed to production, unless you test with realistic data. The fixed 1 row estimate can throw off the QO’s costing pretty dramatically, leading to poorly-performing plans that seem to show up and then disappear at random. For this reason I tend to default to temp tables unless I anticipate that the proc will be called frequently. The rule of thumb I use is that if the proc will not be called more frequently than a few dozen executions per second, I default to temp tables. If the proc will be executed more frequently than that, I use table variables, but only in places where I can be confident that I will not be storing more than a few dozen rows in them.

    Re: the collation issue, you can avoid hardcoding a particular collation by using “collate database_default”:
    Create table #table1 (column1 varchar(30) collate database_default)

Leave a Reply to Alexander.Suprun Cancel reply

Your email address will not be published. Required fields are marked *