You must Sign In to post a response.
  • Category: SQL Server

    What is Difference between temp table and table variable


    Are you looking for a way to know Difference between temp table and table variable ? then read this thread to know more about it



    difference between temp table and table variable please tell me
  • #756160
    Please go through the below article for sample and details on the table variable and temp table



    dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #756161
    Hello,

    Please refer the below link ,it will help to know about the difference.

    http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table

    Regards
    Arul.R

  • #756165
    Hi,

    Both are stored in TempDB but the size will wary, table variable occupy less memory and temp table occupy more memory.

    It holds the information until complete that action in table variable.

    It holds the information until close the window/ drop that instance in table variable.

    We can't perform transactions using table variable that means we can't perform log action that means we can't rollback the deleted information.

    But using Temp table we can do that.

    These are main differences using this. If you want to know more about this then search in Google for the same.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #756168
    - Scope of table variable is limited to current batch, where as for tamp table it's Current session, nested stored procedures. Global: all sessions
    - Table variable can be created using DECLARE statement only, and for temp table you need CREATE TABLE statement
    - In table variable the length is limited to 180 char and it can use user-defined data types and temp table having Maximum 116 characters, which allows User-defined data types and XML
    - Temp variable Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement, where as temp table Indexes can be added after the table has been created.

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #756200
    Hi,

    Find the difference in the following article on the dotnet spider.

    http://www.dotnetspider.com/resources/45758-Difference-between-temporary-tables-and-table-variables.aspx


    Thanks.

  • #756331
    Dear gangadhararao,

    Kindly go through the following link and Defintion, it may help u lot

    http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

    Temporary Tables

    Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.

    The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):


    CREATE TABLE #TempTable

    (

    ID INT NOT NULL,

    Name VARCHAR(10),

    DOB DATETIME

    )



    Table Variables

    The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:


    DECLARE @TableVariable TABLE (

    ID INT NOT NULL,

    Name VARCHAR(10),

    DOB DATETIME

    )

  • #756652
    Table Variable - This should be used when the data quantity is less.

    Use temparary tables when there are huge data.

    If you want to get benefited from features like Indexing, SQL optimizer etc. then switch to temparary tables.

    Temparary tables will be there till the session ends but table variable is batch dependent.

    Thanks & Regards,
    Abhijith

    Mail - abhijith.pn@gmail.com
    Check My Blog - http://www.solvemytechissue.in/


  • Sign In to post your comments