Temporary Tables In MS SQL
There are four types of tables in MS SQL:
- Permanent: Normal tables you create and save
- #Local Temporary: Short term tables, only used in that procedure for that user
- ##Global Temporary: Short term tables available to all users
- @Variable: Similar to a local temporary table but for less data
A local temporary table, #table_name, exists only for the duration of the user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can’t share a local temporary table because it is local to one user session.
A global temporary table, ##table_name, also exists for the duration of the user session or the procedure that created the table. The difference is that multiple users or sessions can access the global temporary table. When the last user session that references the table disconnects, the global temporary table is lost.
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.
Full details of how to work with all these tables are covered in our SQL courses.
Creating a Temporary Table in SQL Server
The syntax given below is used to create a local temp table in SQL Server:
CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50),
UserLocation varchar(150))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a permanent table, for example:
INSERT INTO #LocalTempTable VALUES ( 1, ‘Serena’,’UK’);
And select records from that temporary table:
SELECT * FROM #LocalTempTable
After execution of all these statements, if you close the query window and again execute “Insert” or “Select” command, you will see an error similar to:
Msg 208, Level 16, State 0, Line 1
Invalid object name ‘#LocalTempTable’.
This is because the scope of local temp table is bounded with the current connection of current user.
Below is the syntax for creating a global temporary table:
CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50),
UserLocation varchar(150)),
The above script will create a temporary table in tempdb database. You can insert, update or delete records in the global temporary table similar to a permanent table, or local temporary table.
Local and global temporary tables are stored inside the Temporary Tables folder of the tempdb database.
If you look closely look at the name of the temp tables, you will see a ‘dash’ and ID as part of the name:
SQL server does this automatically to be able to differentiate between the different user sessions; you can ignore this and refer to the table name only.
Points to Remember when using Temporary Tables
-
- Temporary tables are created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
- Keep number of rows and columns to the minimum as needed.
- Temp Tables are deleted when the connection is closed. However, it is common practice to delete them at the end of the script that creates them, rather than relying on them being automatically deleted.
- Temporary tablescan be used in the SQL statement wherever the regular tables can be used. To identify a table being a temporary table in the SQL statement, prefix the table name with the ‘#’ character.
Example:
This example creates two temporary tables from an existing permanent table called WebContacts. The permanent table is a list of people and includes a column for their occupation and another column for their Country. The example calculates the percent of people in each country for each occupation.
Step 1. Create a temporary table named OccupationCount and at the same time populate it with summary data from the existing table:
SELECT Occupation , count(*) as NumOccupation
INTO #OccupationCount
FROM WebContacts
GROUP BY Occupation
Step 2. Create another temporary table named CountryCount which lists the number of people in each country for each occupation.
SELECT Occupation, CountryRegionName, count(*) as cnt
INTO #CountryCount
FROM WebContacts
GROUP BY Occupation, CountryRegionName
Step 3. Use the 2 temporary tables to list the percent of people in each country for each occupation:
SELECT a.Occupation, a.CountryRegionName,
a.cnt * 100 / b.NumOccupation As PercentCountry
FROM #CountryCount a inner join
#OccupationCount b on a.Occupation = b.Occupation
ORDER BY 1
Note: You can use Format to display the percentage symbol and you need to cast the count as a decimal to see the decimal places. If you would like to learn more about using joins in SQL see our article: Introduction To SQL Joins.
SELECT a.Occupation, a.CountryRegionName,
Format(cast(a.cnt as decimal) / b.NumOccupation,’p’) As PercentCountry
FROM #CountryCount a inner join
#OccupationCount b on a.Occupation = b.Occupation
ORDER BY 1
Table Variables vs. Temporary Tables
With Microsoft SQL Server, you can also create Table Variables, which can perform the same kind of operations that are performed with temp tables. Note: There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. Use table variables when you have just a few rows of data. Table variables are destroyed automatically, as soon as the script finishes.
Below is the syntax for creating and using Table variable.
Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50),
UserLocation varchar(150))
INSERT INTO @TempTableVariable values ( 1, ‘Serena’,’UK’);
SELECT * FROM @TempTableVariable
You need to run the three statements together. If you try and run them separately you’ll see a message similar to the following:
Must declare the table variable “@TempTableVariable”
To learn more about SQL, see these articles
