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

USE demo

DECLARE @Person TABLE(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))

Temporary Table

USE demo

DROP TABLE IF EXISTS #Person

CREATE TABLE #Person(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))

DROP TABLE #Person

Global Temporary Table

USE demo

CREATE TABLE ##Person(PersonID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20))

DROP TABLE ##Person