Tutorials
Search…
Lesson 34. Temporary Tables
Sometimes you need to create a table with working data to perform intermediate transformations. The table does not need to continue to exist once you have done your work. Instead of creating a permanent table to do this, you can create a temporary table that exists in memory or in a special database.
You have three options to do this.
Table Variables
Table variables exist in memory and are created in a process that is a cross between creating variables and creating tables. Just like normal variables, table variables are destroyed automatically when the script ends.
Table variables have limitations and do not perform well with more than a handful of records. They are so limited that I will usually just create a temporary table instead for anything over ten records.
Temporary Tables
Temporary tables are actual physical tables but stored in a database called Tempdb which does not show up in the SSMS object explorer.
Ostensibly, temporary tables are also destroyed when the script ends. However, in my experience, it is better to clean up after yourself by explicitly destroying the table.
Temporary tables are created by the normal table creation process. You specify the table as a temp table by putting a hashtag in front of the table name.
Global Temporary Tables
Global temporary tables exist across connections. You use these when you need more than one script to access the same data. Here again is a thing that exist in SQL Server, but I rarely use.
You create global temp tables by putting two hashtags before the table name.

Examples

Table Variable
In [ ]:
USE demo
DECLARE @Person TABLE(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))
Temporary Table
In [ ]:
USE demo
DROP TABLE IF EXISTS #Person
CREATE TABLE #Person(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))
DROP TABLE #Person
Global Temporary Table
In [ ]:
USE demo
CREATE TABLE ##Person(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))
DROP TABLE ##Person
Copy link