Interviews - C# Corner

M SK
Difference temdb table and table variable?
By M SK in SQL Server onAug 22 2014
  • Vishal Jadav
    Sep, 2016 9

    #temp table = data stored in tempdb and scope is limited to sp. ##temp table =globle temp table stored in tempdb scope is in all connections. @temp table variable = stored in memory scope upto sp executes @temp table is good option.

    • 0
  • Madhuri Mishra
    May, 2015 4

    Tempdb store all your temp or global table, cursor, table variables and temp sp. Table variable is just like temporary table but its performance is better in terms of compilation and use of resource than temp table.

    • 0
  • Kml Surani
    Apr, 2015 15

    The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanismAfter declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.Finally, 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(string) statements. Also, they cannot be used in an insert/exec statement.

    • 0
  • Lalit Raghuvanshi
    Feb, 2015 5

    There is good article on: 16 main differences between Temporary Table and Table Variable in Sql Server http://www.webcodeexpert.com/2015/02/difference-between-temporary-table-and.html

    • 0
  • Virendra Gaur
    Sep, 2014 7

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

    • 0

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS