1

What is the difference between different types of temporary table's in Sql Server 2008?

1.) #table
2.) ##table
3.) CTE
4.) @table variable

Daljeet Singh
  • 31
  • 3

1 Answers1

1

here are some good article on TempTable VS Table Variable

  1. What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

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

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

as a conclusion what i found :

Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.

Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.

Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

but at last i would also recommend that You should improve your internet searching.

Community
  • 1
  • 1
Vikash Singh
  • 804
  • 1
  • 10
  • 11
  • 1
    Local temporary tables are deleted when they go out of scope - a temp table created within an `EXEC()` will not exist when the `EXEC` has completed. A temp table created inside a stored procedure is deleted when the stored procedure returns. – Damien_The_Unbeliever Nov 15 '13 at 11:32