Looking into temporary tables in SQL Server
I have been delivering a certified course in MS SQL Server 2012 recently and I was asked several questions about temporary tables, how to create them, how to manage them, when to use them and what are the limitations of them.
In this post I will try to shed light on this particular issue with lots of hands-on demos.
Temporary tables and table variables make use of the system tempdb database.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
First I am going to create a new temporary table and populate it. Execute the script below.
USE tempdb
GO
IF OBJECT_ID('tempdb..#footballer') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #footballer
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[#footballer] ON
GO
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
SELECT * FROM #footballer
As you can see there is a # prefix in front of the table. This table will be saved in the tempdb.
Finally I select everything from the temporary table.
If I open a new query window and try to select everything (see the query below) from the #footballer table.
USE tempdb
GO
SELECT * FROM #footballer
You will not receive any results. You will receive an error - Invalid object name '#footballer'.
This is a local temporary table and it is in scope only in the current connection-session.
We can also create global temporary tables. In a new query window execute the following script.
USE tempdb
GO
IF OBJECT_ID('tempdb..##footballernew') IS NOT NULL
DROP TABLE ##footballernew;
GO
CREATE TABLE #footballernew
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[##footballernew] ON
GO
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
SELECT * FROM ##footballernew
We denote the global temporary table with ## - ##footballernew
The global temporary table is deleted when all users referencing the table disconnect.
Both global and local temporary tables should be deleted in code rather than depending on automatic drop.
A temporary table created in a stored procedure is visible to other stored procedures executed from within the first procedure.
In a new query window type the following.
USE tempdb
GO
SELECT * FROM ##footballernew
In this case there will be no error. Global temporary tables persist across sessions-connections.
You can also add columns to temporary tables and alter the definition of existing columns.
In this script I add another column and then alter the definition of an existing column.
USE tempdb
GO
IF OBJECT_ID('tempdb..#footballer') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #footballer
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[#footballer] ON
GO
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
ALTER TABLE #footballer
ADD [is_retired] BIT NULL;
GO
ALTER TABLE #footballer
ALTER COLUMN [lastname] [nvarchar](50);
GO
You can use any data type for columns definition in a temporary table. You can also use user-defined data types.
You can also have constraints in temporary tables.If you execute the code below, it will work perfectly fine.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Movies') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #Movies
(
MovieID INT PRIMARY KEY ,
MovieName NVARCHAR(50) ,
MovieRating TINYINT
)
GO
ALTER TABLE #Movies
WITH CHECK
ADD CONSTRAINT CK_Movie_Rating
CHECK (MovieRating >= 1 AND MovieRating <= 5)
But you have to be careful when creating-applying foreign keys. FOREIGN KEY constraints are not enforced on local or global temporary tables.
Execute the script below to see what I mean.The foreign key will not be created.
USE tempdb
go
CREATE TABLE #Persons
(
P_Id INT NOT NULL ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255) ,
PRIMARY KEY ( P_Id )
)
CREATE TABLE #Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES #Persons(P_Id)
)
Please bear in mind that you can create temporary tables with clustered and non-clustered indexes on them.
Let's investigate the behavior of temporary tables and IDENTITY columns.
If you execute the script below , it will fail. This is the same behavior when executing the same script to regular tables. You cannot specify values for the IDENTITY column.If you choose to do so you must set IDENTITY_INSERT ON.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Persons') IS NOT NULL
DROP TABLE #Persons;
GO
CREATE TABLE #Persons
(
P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255)
)
--this will not work
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
SET IDENTITY_INSERT [#Persons] ON
GO
--this will work
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
Αlso note that transactions are honored in temporary tables. If I begin an explicit transaction -an insert- without committing it will insert the row of data but then if a rollback is issued the whole operation will be rolled back
Execute the script below.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Persons') IS NOT NULL
DROP TABLE #Persons;
GO
CREATE TABLE #Persons
(
P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255)
)
SET IDENTITY_INSERT [#Persons] ON
GO
--this will insert the value
BEGIN TRAN
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
GO
SELECT * FROM #Persons
--this will rollback the transaction
ROLLBACK TRAN
Hope it helps!!!