Temp table in sql server

Local Temporary Tables:

Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables starts with (“#”) hash sign.

Global Temporary Tables

Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of global temp tables start with (“##”) double hash sign.

Temp Variables:

Temp Variables are also used for holding the data fora temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gokhul Varman9y ago
Please refer the link more detailed explnation:https://www.tutorialspoint.com/sql/sql-temporary-tables.htm
Pankaj Kumar Choudhary9y ago
Local Temporary Tables:

Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables starts with (“#”) hash sign.

Global Temporary Tables

Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of global temp tables start with (“##”) double hash sign.

Temp Variables:

Temp Variables are also used for holding the data fora temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.
Anil Jha9y ago
Please check below link that explains well.
https://stackoverflow.com/questions/2219714/sql-server-tables-what-is-the-difference-between-and
Puneet Kankar9y ago
hi friend,

Read this article and watch this vedio you all doubt are clear.

http://www.dotnettricks.com/learn/sqlserver/difference-between-cte-and-temp-table-and-table-variable
https://www.youtube.com/watch?v=oGuS1rdfaMI