1 Introduction
In this post I would like to explain the use of Local and Global Temporary Tables within SQL Server.
2 Common SQL Tables
As always, I will start by creating my own database in which I will then create a sample table.
SET LANGUAGE ENGLISH
CREATE DATABASE Temp_Tables;
USE Temp_Tables;As we can see, there is no table stored in the database yet:
SELECT *
FROM SYSOBJECTS
WHERE xtype = 'U';
2.1 Creation of a Normal Table
Below I will create a table as usual and include some sample data:
CREATE TABLE Customer
(Name VARCHAR(100) NOT NULL,
Age INT NOT NULL)
;
INSERT INTO Customer VALUES ('Klara', 35)
INSERT INTO Customer VALUES ('Emily', 44)
INSERT INTO Customer VALUES ('Jessy', 22)
INSERT INTO Customer VALUES ('Max', 50)
INSERT INTO Customer VALUES ('Tom', 22)2.2 Accessibility of the Table
The generated sample table can be called (as usual) as follows:
SELECT * FROM Customer;
We can also see them in this way now:
SELECT *
FROM SYSOBJECTS
WHERE xtype = 'U';
3 Local Temporary Tables
So far, nothing new has come up that I haven’t already described in one of my previous posts. Now let’s get to the actual topic for which reason I wrote this post.
Local Temporary Tables are, as the name suggests, tables that can be created that are only temporarily available. They are automatically deleted as soon as they are no longer used.
3.1 Creation of a Local Temporary Table
The name of Local Temporary Tables are prefixed with the # character:
CREATE TABLE #local_temp_Customer
(Name VARCHAR(100) NOT NULL,
Age INT NOT NULL)
;Now let’s add some sample data here:
INSERT INTO #local_temp_Customer VALUES ('Sven', 35)
INSERT INTO #local_temp_Customer VALUES ('Mike', 28)3.2 Accessibility of the Local Temporary Table
The local temporary table can be called like any other ordinary table:
SELECT * FROM #local_temp_Customer;
Let’s see if it can be done this way as well:
SELECT *
FROM SYSOBJECTS
WHERE xtype = 'U';
No here, of course, it does not appear. But we can find them in the Object Manager:
3.2 Accessibility of the Tables via Another Database
For this example, I create and use another sample database and try to access the tables created so far from there.
CREATE DATABASE Temp_Tables2;
USE Temp_Tables2;Now we try to retrieve data from the Customer table:
SELECT * FROM Customer;
As we can see, from the new database we cannot reach the normal table, because it is stored on another database.
But data from the Local Temporary Table we can retrieve:
SELECT * FROM #local_temp_Customer;
4 Global Temporary Tables
Now in SQL Server we also have the possibility to create Global Temporary Tables. Compared to Local Temporary Tables, in SQL Server the Global Temporary Tables are visible to all sessions.
The difference becomes immediately clear when you look at the accessibility part. First, however, let’s create an example Global Temporary Table.
For this example, I’ll switch back to the first database I created.
USE Temp_Tables;4.1 Creation of a Global Temporary Table
The name of Global Temporary Tables are prefixed with ## characters:
CREATE TABLE ##global_temp_Customer
(Name VARCHAR(100) NOT NULL,
Age INT NOT NULL)
;Likewise, we include a sample observation:
INSERT INTO ##global_temp_Customer VALUES ('John', 38)4.2 Accessibility of the Global Temporary Table
SELECT * FROM Customer;
SELECT * FROM #local_temp_Customer;
SELECT * FROM ##global_temp_Customer;
As expected, from this point we can reach all three tables.
4.3 Accessibility of the Tables via Another Session
At this point the difference between a Local Temporary Table and a Global Temporary Table becomes clear.
Here I open a new session:
Now I am trying to execute a query on the Local Temporary Table:
SELECT * FROM #local_temp_Customer;
And now a query on the Global Temporary Table:
SELECT * FROM ##global_temp_Customer;
5 Recap
- Normal tables in SQL Server can be accessed from any session as soon as you reference the database (with
USE table_name). - Local Temporary Tables can be accessed from within a session, regardless of which database is being referenced.
- Global Temporary Tables can be accessed regardless of the session and database being referenced.
Both Local and Global Temporary Tables are automatically deleted again when they are no longer used. This is the case, for example, when the SQL Server is shut down.
6 Conclusion
In this post I have explained what is the difference between Local Temporary Tables and Global Temporary Tables and how to use them respectively what advantages they have over the other types of tables.