Where are temporary tables stored in SQL Server?

Where are temporary tables stored in SQL Server?

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Good question right? 🙂
There are many cases where we need to create temporary tables in SQL Server and for various reasons.
Such reasons may include:

  • Breaking the logic of a large and complex SQL Statement in smaller portions of code.
  • Increase the performance of a SQL query, etc.

But what types of temporary tables does SQL Server provide and what is the meaning of each type? Last but not least, where are temporary tables stored and how can we get schema information about them?

There are two types of temporary tables:

  • Local temporary tables:
    • Only available to the current connection to the database for the current login
    • They are dropped when the connection is closed
  • Global temporary tables:
    • Available to any connection upon their creation
    • They are dropped when the last connection using them is closed

Code Example – Local Temporary Table CREATE TABLE #table_name (
column_name [DATATYPE] )

Code Example – Global Temporary Table CREATE TABLE ##table_name (
column_name
[DATATYPE] )

So, consider as an example that you create the following temporary table:
CREATE TABLE #temp_table (
id INT,
name VARCHAR(50) )

Then let’s say you want to find schema information regarding the above table. Where can you find this information in SQL Server?
The answer is that temporary tables (local and global) are stored in the tempDB database.
So, if you want to find schema information for the temporary table named temp_table you can use the following queries:
–Query 1(a): Get the exact name of the temporary table you are looking for
DECLARE @table_name AS VARCHAR(300)

SET @table_name = (SELECT TOP 1 [name]
FROM tempdb..sysobjects
WHERE name LIKE ‘#temp_table%’)
Explanation: When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table. Even though you can query the temporary table with its logical name, internally is known with the exact name SQL Server has set. To this end, you need to execute the above query for finding the exact name of the temporary table.
–Query 1(b): Get column information for the temporary table
— by using the sp_columns stored procedure

EXEC tempdb..sp_columns

@table_name

Explanation: The sp_columns stored procedure returns column information for the specified tables or views that can be queried in the current environment.


My Latest Projects:


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.