What is #temp and @table variable in SQL server?

Kumar Bhimsen

Kumar Bhimsen

Jan 08, 2016

    One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.Temporary Tables honor the explicit transactions defined by the user. Table variables doesn’t participate in the explicit transactions defined by the user.Temporary Tables are not allowed in User Defined Functions. Table Variables can be used in User Defined Functions.Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint. Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.

    Keerthi Venkatesan
    June 08, 2016

    Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

    Anil Kumar Murmu
    August 24, 2016

    #temp = is temporary variable available for a particular scope and stored in tempdb of sql server. @table = is a table variable available for particular scope and stored in memory.

    Vishal Jadav
    August 05, 2016

    #temp used tem table declare and @table Used declare table as retrun values

    Chetan Raiyani
    June 05, 2016

    This link explains difference between the 2 vert clearly.http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

    Rajeev Punhani
    January 27, 2016

    #temp refers to a local (visible to only the user who created it) temporary table. @table refers to a variable which can hold values depending on its type.

    Rafnas T P
    January 11, 2016

    #Temp table : it is temporary table that is generally created to store session specific data. #Temp table is visible only to the current scope. Generally ,the table gets clear up automatically when the current procedure goes out of scope. @Table variable : @Table variable is similar to temporary table except with more flexibility. It is not physically stored in hard disk. We should choose it when we need to store less than 100 records.

    Kumar Bhimsen
    January 08, 2016