Temporary Tables in SQL Server and # vs ##
In this article, I’ll mention temporary tables in SQL Server and differences between Local Temporary Tables (#) and Global Temporary Tables (##).
When additional tables are needed on the database, temporary tables are created and used with T-SQL. Especially when working on large databases and working with a small part of the data, we filter the data to create subsets with temporary tables and return faster query results.
At the same time, when writing critical queries on necessary tables, tests are made on temporary tables, and then they are applied to real tables. It is pretty standard and highly performance, especially in writing complex queries.
** We can use “select * into …” or “create table …” statements for creating a table.
Temporary tables are stored inside “tempdb” which is a system database in SQL Server.
There are two separate groups which are Local Temporary Table (#tempTable) and Global Temporary Table (##tempTable).
A Local Temporary Table’s name starts with single hash value “#” and has a random value at the end of the table name.
Global Temporary Tables start with ‘##’, its name is always unique, and there is no random number appended to the name.
A local temporary table is visible only in the current session. It’s automatically dropped when the creator closes the existing connection to SQL Server.
Global temporary tables are visible to all connections of SQL Server as soon as the last active connection is closed. In other words, more than one session, along with the session who created the temporary table, can connect to SQL Server at that time.
In this case, after the person who created the table closes his connection with SQL Server, the temporary table is stored until there is no one connected to SQL Server, and the table is deleted when the last connected session terminates the connection.
Additionally, we can also drop with the following statements.
Thank you!
