What is the difference between a Temporary Table and a Table Variable



What is the difference between a Temporary Table and a Table Variable?
Or
When do you use Table Variable over a Temporary Table and vice versa?


1. Table variable is created in the memory where as a temporary table is created in the TempDB. But, if there is a memory pressure, the pages belonging to a table variable may be pushed out to tempdb.


2. Table variables cannot be involved in transactions, logging or locking. This makes table variable faster than a temporary table.


3. You can pass table variable as parameter to functions and stored procedures, where as you cannot do the same with temporary table.


4. A temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.


Read this article for more SQL Server Interview Questions on Temporary Tables.


If you know of any other differences between a Temporary Table and a Table Variable, please post them below.

16 comments:

  1. How do we create Temp Variables? How do we use it? can u explain with an Example?

    ReplyDelete
    Replies
    1. Declare @tablevariable Table(id int,price money)

      Delete
    2. Please refer Kudvenkat sir youtube channel for sql server there you can find lot of videos by parts wise. First of all watch those videos even takes time then will able to get good knowledge and you should not ask next time these type of questions.

      Delete
  2. we cannot pass a table parameter into the UDF - only return table as result

    ReplyDelete
  3. There is one more difference.. Table variable do not participate in transaction where temporary tables can participate in transaction.

    ReplyDelete
  4. Use the '@' character with the name when creating table as a variable

    CREATE TABLE @tempTableVariable

    For Temp table use '#'(local) or '##' (Global) table

    ReplyDelete
  5. What is difference between CTE and Table variable??

    ReplyDelete
  6. you can directly create temporary table from another table by using select * into #temp1 from tblA
    but table variable you can't.

    ReplyDelete
  7. 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.

    ReplyDelete
  8. I don't want to create index on my table but if i want to fetch data from my table and store it to temporary table where i want to create index for searching my query faster way then how to write query....please reply anybody if you know how to do Thanks


    By Shoaib

    ReplyDelete
  9. Table variables in SQL Server
    Table variables are alternative of temporary tables. As,for instance, temporary tables are table variables store a set of record. The syntax of table variable shown below:



    DECLARE @EmpForm TABLE

    (

    EmpId int NOT NULL,

    EmpName varchar,

    Bonus decimal(15, 3)

    )



    We can insert values using INSERT INTO clause in table variable. We can use SELECT to fetch data and UPDATE, DELETE for manipulate data in table variable. In table variable the constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY constraints are not allowed. Table variable Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. We can return a table variable from user-defined function.



    Syntax of INSERT, UPDATE, DELETE of table variable:


    INSERT INTO @EmpForm (EmpID,EmpName,Bonus)

    Select EmpId, EmpName, (Sal*0.10)

    FROM

    Employee;


    UPDATE @EmpForm SET Bonus=( Sal*0.20)

    WHERE EmpId=1431;


    DELETE FROM @EmpForm WHERE EmpId=1343;



    It is just like temporary table (#EmpForm) but some differences are there.



    A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable we create with a DECLARE statement. Temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed.

    Table variable data will not be rolled back when a transaction rolled back. Temporary table data will be rolled back when a transaction rolled back.

    The variable will no longer exist after the procedure exits, there will be no table to clean up with a DROP statement. Temporary tables are automatically dropped when they go out of scope unless explicitly dropped using DROP TABLE.

    The restricted scope of a table variable gives SQL Server some liberty to perform optimizations.

    A table variable will generally use fewer resources than a temporary table.

    Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure.

    We cannot use a table variable as an input or an output parameter.

    We cannot truncate a table variables whereas we can truncate a temporary table.

    ReplyDelete
  10. Hi Venkat Sir
    What is the life time of local and Global temporary tables??

    ReplyDelete
    Replies
    1. Life time for Local and Global temporary Tables are until that created session available.

      Delete
    2. I would like to correct the above answer.
      Local temporary tables can be used by only the session which has created it. It will be dropped when that session closes.
      Whereas global temporary tables are live till the last referenced session is active.

      Delete
  11. Table variable is wiped after the query execution is completed, unlike temp table where the data is available till the session is closed.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us