intro
Let's explore everything you need to know about SQL Server temp table mechanisms to master the art of breaking complex tasks into smaller steps with temporary tables!
In programming, it is common to store values in temporary data structures. In databases, the same concept is implemented through temporary tables, which are especially useful for simplifying complex procedures or SQL queries. SQL Server fully supports temporary tables, both local and global. Follow this guide to master the SQL Server temp table mechanism!
What Is an SQL Server Temporary Table?
In SQL Server, a temporary table is a special type of table that exists only for the duration of a session or until it is no longer needed. When a temp table goes out of scope, the database engine automatically drops it.
This mechanism is commonly used to store intermediate results, perform query optimization, or manipulate data without affecting permanent tables in the database.
In particular, SQL Server supports two types of temporary tables:
When and Why to Use SQL Server Temp Tables
The SQL Server temp table mechanism is ideal for handling intermediate data without impacting permanent tables. Specifically, temporary tables are useful in the following scenarios:
In general, temporary tables in SQL Server are a powerful tool for performance tuning and modular query design.
How to Create a Temp Table in SQL Server
The syntax for creating a temporary table is nearly identical to the standard CREATE TABLE statement in SQL Server. The key difference is that the table name must be prefixed with either # or ##:
For example, you can create a sample local temporary table with:
1
CREATE TABLE #MyLocalTempTable (
2
Id INT PRIMARY KEY,
3
Name NVARCHAR(100)
4
);
This temp table is visible only within the current session and is automatically dropped when the session ends.
Similarly, you can define a global temporary table with:
1
CREATE TABLE ##MyGlobalTempTable (
2
ID INT PRIMARY KEY,
3
Name NVARCHAR(100)
4
);
This temp table is accessible to all sessions and remains until all referencing sessions have ended.
For more, see our full guide on how to create temporary tables in SQL Server. Keep in mind that SQL Server temporary tables behave just like regular tables. You can perform all standard operations on them—such as SELECT, INSERT, UPDATE, and DELETE.
Notes:
How SQL Temporary Tables Work
SQL Server stores all temporary tables on disk in the tempdb system database. When you create a temp table in SQL Server, the database engine physically writes its structure and data to tempdb — similar to a regular table:
Local temporary tables are scoped to the current session or procedure, and are dropped when it ends. Global temporary tables remain until all sessions referencing them are closed.
Now, let's explore some specific scenarios to better understand how the SQL Server temp table mechanism works.
Creation in Triggers, Stored Procedures, and Modules
When a SQL Server local temporary table is created within a stored procedure or a SQL module that can be executed simultaneously by multiple sessions, the database engine must be able to distinguish between the tables created by each session.
To do that, the database engine automatically appends a unique suffix to each local temporary table name. As a result, the full name of the temporary table stored in the tempdb database consists of the table name specified in the CREATE TABLE statement, along with the system-generated suffix:
Note: The suffix is only visible internally, and you can still query a local temporary table without using its system-generated suffix.
A local temporary table created within a stored procedure or SQL trigger can share the same name as a temporary table created earlier. At the same time, if multiple temporary tables with the same name exist at the same time, it is undefined which table a query will reference.
In cases where nested stored procedures are involved, each procedure can create a temporary table with the same name as one created by a calling procedure. To ensure modifications are directed to the correct table in a nested procedure, the temporary tables must have identical structures, including matching column names.
Drop Conditions
SQL Server temporary tables are automatically dropped when they go out of scope, unless explicitly removed using DROP TABLE.
There are a couple of key aspects you need to keep in mind:
SQL Temp Table: Complete Example
Now that you know what SQL Server temporary tables are and how they work, let's see them in action with a real-world example.
Assume you want to retrieve a list of customers who have placed orders in the last 30 days, calculate the total order value for each customer, and filter those with a total order value greater than 500. The resulting query to achieve that goal can be quite complex:
1
SELECT c.CustomerID, c.CustomerName, SUM(o.OrderAmount) AS TotalOrderValue
2
FROM Customers c
3
JOIN Orders o ON c.CustomerID = o.CustomerID
4
WHERE o.OrderDate >= DATEADD(DAY, -30, GETDATE())
5
GROUP BY c.CustomerID, c.CustomerName
6
HAVING SUM(o.OrderAmount) > 500
7
ORDER BY TotalOrderValue DESC;
As you can see, the query involves a GROUP BY, HAVING, ORDER BY, and aggregate functions, making it harder to follow.
If you execute this query in a SQL Server database client like DbVisualizer, you will get this result:
Time to simplify the complex query by using a SQL Server temp table!
Step #1: Create a Temp Table
Start by creating a local SQL Server temporary table that will store the aggregated order data:
1
CREATE TABLE #AggregatedOrders (
2
CustomerID INT,
3
TotalOrderValue DECIMAL(10, 2)
4
);
As you can verify in DbVisualizer, the table was added to tempdb as expected:
Step #2: Populate the Temp Table
Next, populate the temporary table with the aggregated data:
1
INSERT INTO #AggregatedOrders (CustomerID, TotalOrderValue)
2
SELECT o.CustomerID, SUM(o.OrderAmount) AS TotalOrderValue
3
FROM Orders o
4
WHERE o.OrderDate >= DATEADD(DAY, -30, GETDATE())
5
GROUP BY o.CustomerID
6
HAVING SUM(o.OrderAmount) > 500;
This should populate 3 rows as the original query selects 3 customers:
Step #3: Query the Temp Table
Now, you can query the temporary table with a simple JOIN query to get the results:
1
SELECT c.CustomerID, c.CustomerName, t.TotalOrderValue
2
FROM Customers c
3
JOIN #AggregatedOrders t ON c.CustomerID = t.CustomerID
4
ORDER BY t.TotalOrderValue DESC;
Launch the query within the same session and you will get:
Et voilà! The same result as before, but achieved through a simpler and more manageable approach using a temporary table.
Conclusion
In this article, you learned what SQL Server temporary tables are, how they work, when and why to use them, and how. You also saw a complete example to help you truly understand what benefits they bring to the table.
As shown here, working with SQL Server temp tables becomes much easier when using a powerful, feature-rich, multi-database client like DbVisualizer. This advanced tool also offers features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
What is the naming convention for SQL Server temporary tables?
In SQL Server, temporary tables follow a specific naming convention:
How do you select records into a temporary table in SQL Server?
To select records into a temporary table in SQL Server, you can use the SELECT INTO statement with a temp table name prefixed by # or ##. For example:
1
SELECT * INTO #TempEmployees
2
FROM Employees
3
WHERE Department = 'HR';
The above SQL Server SELECT INTO temporary table syntax creates the #TempEmployees table and inserts the result of the SELECT query into it. The table is stored in tempdb and behaves like a regular table for the duration of the session.
Do SQL Server temporary tables support indexes?
Yes, SQL Server temp tables support indexes. You can create both clustered and non-clustered indexes on them manually using the CREATE INDEX statement. This improves performance for large datasets or complex queries, just like with regular tables. Temporary tables in SQL Server also support primary keys and other constraints.
How do you drop a SQL Server temporary table if it exists?
In SQL Server 2016 or greater, the SQL Server drop temp table if exists syntax is:
1
DROP TABLE IF EXISTS #TempTable;
Where TempTable is the name of your local temp table. Otherwise, you can achieve the same result using the OBJECT_ID() function to check if the table exists before attempting to drop it:
1
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
2
DROP TABLE #TempTable;
This is a more general syntax to drop a temporary table if it exists in SQL Server.
Why use a visual database client to work with temporary tables in SQL Server?
Using a visual database client like DbVisualizer makes managing SQL Server temporary tables much easier. Since temp tables exist only within the scope of a session, it can be confusing to track them without proper visibility. Without a tool that fully supports SQL Server's features, you might not understand why a temp table disappears or where it is stored. This is just one of many advanced features DbVisualizer supports. Grab a 21-day trial to explore all its features!
