Where are temporary tables stored in SQL Server?

Many people ask: “Where are temporary tables stored in SQL Server?” and that’s a very good question.
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.

Types of Temporary Tables in SQL Server

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]);
GO

 

Code Example – Global Temporary Table

CREATE TABLE ##table_name ( 
column_name [DATATYPE]);
GO

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More


 

So, consider as an example that you have created the following temporary table:

 

CREATE TABLE #temp_table ( 
id INT, 
name VARCHAR(50) );
GO

 

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%');
GO

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, SQL Server knows it with the exact name. 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;
GO

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

 

Watch the Video with Live Demonstration

 

Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Administration articles.

Subscribe to our newsletter and stay up to date!

Check out Artemakis’s eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.67 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Where are temporary tables stored in SQL Server? Click to Tweet