SQL Server temp tables are very useful tools available to us when it comes to querying and developing SQL Server databases.
Temp tables offer a very easy way to create a table for a singular purpose and then remove that table when we are finished with it.
In this very brief tutorial, we’ll discuss everything you need to know about SQL Server temp tables:
- What is a SQL Server temp table?
- Examples of local SQL Server temp tables
- Examples of global SQL Server temp tables
- Tips and tricks
Let’s get into it:
1. What is a SQL Server temp table?
A SQL Server temp table is a temporary table we create ourselves and populate with data. The temp table can then be used like any other table while it is in scope. Temp tables fall out of scope when the connection that created the temp table is closed.
Temp tables are an excellent way to create a table for a specific singular purpose where you don’t want that table to be created as a permanent object in the database. The impermanence of temp tables is obviously their biggest advantage.
I usually see temp tables created in stored procedures, for example. It might be useful within a stored procedure to create a simple one-off table to do work against.
2. Examples of local SQL Server temp tables
There are two types of temp tables: Local and Global. We’ll talk about local temp tables first.
Here is the general syntax for creating a local temp table:
CREATE TABLE #<table-name> ( <table column list> )
As you can see, there’s not much to it! It’s extremely similar to the regular CREATE TABLE statement. The only real difference is the ‘#‘ symbol in front of the table name. That symbol is what tells SQL Server to create the table as a temp table.
For example, let’s create a simple Books temp table:
CREATE TABLE #Books ( BookID INT IDENTITY(10,5), Title VARCHAR(35), Author VARCHAR(15), Pages INT )
Again folks, all we need to do is put the ‘#‘ symbol in front of the table name to make it a temporary table. Everything else is just like a regular table. For example, we can use many different data types for our columns, or we can create constraints, or indexes, or whatever!
If we run this statement, our #Books table is created and we can add a few rows to it by running a regular INSERT statement:
INSERT #Books (Title, Author, Pages)
VALUES ('As a man thinketh', 'Allen', 45),
('Eat that frog', 'Tracy', 108),
('The war of art', 'Pressfield', 165),
('Deep work', 'Newport', 263),
('The Pragmatic Programmer', 'Thomas', 283),
('The Education of a Bodybuilder', 'Schwarzenegger', 256),
('Debt Free Degree', 'O''Neil', 224)
Here’s what the content of the table should look like:
Again folks, we can treat this temp table just like we would any other table. We can INSERT rows as you’ve seen, or we can UPDATE rows, DELETE rows, use it in a JOIN, whatever!
Why is this considered a local temp table?
This temp table is considered a local temp table because it is only accessible in the connection in which it was created.
For example, I created this temp table in a query window called SQLQuery2:
This window represents a unique connection to the databases. This connection is given it’s own unique SPID number (Server Process ID). The temp table is only accessible in this connection.
For example, I’ll open a second query window which will be given it’s own separate SPID number. In this separate connection, we cannot access the temp table:
So again, this #Books table is only accessible in the connection in which it was created!
The same idea is true if we create a temp table within a stored procedure, for example. The life of the temp table is only as long as the life of the stored procedure. When the stored procedure ends, the temp table is dropped.
Temp tables live in a database called ‘tempdb’
We need to talk about where this temp table lives. It does not live in the database you are currently connected to. It lives in a separate database called ‘tempdb‘ which ships with Microsoft SQL Server. You can find the temp table in your object explorer by navigating to Databases | System Databases | tempdb | Temporary Tables, like so:
Notice the name of the temp table is a bit strange. When looking at the temp table in the object explorer, the full name is this:
This is done to create an unambiguous name for the #Books temp table. SQL Server needs this name to be unambiguous because a separate connection could also create a different instance of #Books. That second instance of #Books would also be in it’s own connection and given an unambiguous name in tempdb.
For example, here’s a screenshot of me creating a completely different #Books temp table in my other query window, SQLQuery4. After it’s created, we see a second instance of #Books in tempdb , but it’s full name is different from the first instance created earlier in the other connection:
So again, this is how SQL Server makes the names unambiguous.
3. Examples of global SQL Server temp tables
Now that we know a thing or two about local temp tables, it will be easy to understand global temp tables. Global temp tables are accessible in any connection.
Here’s the syntax to create a global temp table:
CREATE TABLE ##<table-name> ( <table column list> )
Notice all we need to do is use two pound symbols (a double-pound, if you will) instead of one.
As an example, we’ll create a global temp table called ##Employees in a new connection and give it some rows:
Then we’ll open a separate connection and see that we can access the global temp table:
Superb!
Temp tables fall out of scope when the connection that created it is closed
As mentioned earlier, a temp table is dropped when the connection that created it is closed.
For example, we created our new ##Employees temp table in a new query window called SQLQuery7, which is in it’s own connection. If we close that connection, then try to access the temp table, we get an error message:
Remember, we could access this temp table before in the same connection (SQLQuery8) but now we cannot!
And again, this same idea is true if you create a temp table in a stored procedure. When the procedure ends, the temp table is dropped!
4. Tips and tricks
Here is a list of a few tips and tricks you should know when working with SQL Server temp tables:
Tip # 1: You shouldn’t rely on the automatic dropping of a temp table
If you’re being a good developer, you’ll clean up your messes. If you create a temp table, you really should run your own DROP TABLE statement when you are done with it. The syntax is exactly the same as a regular table:
DROP TABLE <temp-table-name>
This is simply a good habit to get into.
Tip # 2: Temp tables are created in a different database (tempdb), so you might not have access to custom objects created in your regular database
When we create a temp table, we might not have access to the custom objects we created in our regular database because the temp table exists within a completely separate database.
Think about user defined data types, for example Those are database-specific. Take a look at the list of user defined data types in my SimpleSQLTutorials database:
Since these data types are local to the SimpleSQLTutorials database, it means I cannot use them when creating a temp table:
SQL Server is trying to create this table in the tempdb database, which does not have a BigDecimal data type!
Tip # 3: You cannot use temp tables in user defined functions
User defined functions are a great tool available to us in Microsoft SQL Server. Unfortunately, we cannot use temp tables in the definition of any user defined function.
Next Steps:
Leave a comment if you found this tutorial helpful!
Temp tables are used commonly in stored procedures. Click the link for a full beginner-friendly tutorial on stored procedures!
You might also benefit from the following FREE GUIDE:
FREE 1-page Simple SQL Cheat Sheet on the Top 10 Data Types you need to know!
This guide discusses the most common data types you will likely encounter during your career as a database professional. You should definitely understand these common data types if you want to perform your job well. This will be a great resource for you to reference throughout your career. Download the guide today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
