Temporary Tables In MS SQL
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(
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:
And select records from that temporary table:
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(
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. can 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
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
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
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
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 (
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:
About Ben Richardson
Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.
Где хранятся временные таблицы sql server
В дополнение к табличным переменным можно определять временные таблицы. Такие таблицы могут быть полезны для хранения табличных данных внутри сложного комплексного скрипта.
Временные таблицы существуют на протяжении сессии базы данных. Если такая таблица создается в редакторе запросов (Query Editor) в SQL Server Management Studio, то таблица будет существовать пока открыт редактор запросов. Таким образом, к временной таблице можно обращаться из разных скриптов внутри редактора запросов.
После создания все временные таблицы сохраняются в таблице tempdb , которая имеется по умолчанию в MS SQL Server.
Если необходимо удалить таблицу до завершения сессии базы данных, то для этой таблицы следует выполнить команду DROP TABLE .
Название временной таблицы начинается со знака решетки #. Если используется один знак #, то создается локальная таблица, которая доступна в течение текущей сессии. Ели используются два знака ##, то создается глобальная временная таблица. В отличие от локальной глобальная временная таблица доступна всем открытым сессиям базы данных.
Например, создадим локальную временную таблицу:
И с этой таблицей можно работать в большей степени как и с обычной таблицей — получать данные, добавлять, изменять и удалять их. Только после закрытия редактора запросов эта таблица перестанет существовать.
Подобные таблицы удобны для каких-то временных промежуточных данных. Например, пусть у нас есть три таблицы:
Выведем во временную таблицу промежуточные данные из таблицы Orders:
Здесь вначале извлекаются данные во временную таблицу #OrdersSummary. Причем так как данные в нее извлекаются с помощью выражения SELECT INTO, то предварительно таблицу не надо создавать. И эта таблица будет содержать id товара, общее количество проданного товара и на какую сумму был продан товар.
Затем эта таблица может использоваться в выражениях INNER JOIN.
Подобным образом определяются глобальные временные таблицы, единственное, что их имя начинается с двух знаков ##:
Обобщенные табличные выражения
Кроме временных таблиц MS SQL Server позволяет создавать обобщенные табличные выражения (common table expression или CTE), которые являются производными от обычного запроса и в плане производительности являются более эффективным решением, чем временные. Обобщенное табличное выражение задается с помощью ключевого слова WITH :
В отличие от временных таблиц табличные выполнения хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет это табличное выражение.
Local and global temporary tables in SQL Server
What is the difference between local and global temporary tables in SQL Server?
7 Answers 7
Table variables ( DECLARE @t TABLE ) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
Local temporary tables ( CREATE TABLE #t ) are visible only to the connection that creates it, and are deleted when the connection is closed.
Global temporary tables ( CREATE TABLE ##t ) are visible to everyone, and are deleted when all connections that have referenced them have closed.
Tempdb permanent tables ( USE tempdb CREATE TABLE t ) are visible to everyone, and are deleted when the server is restarted.
I find this explanation quite clear (it’s pure copy from Technet):
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
1.) A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash («#») sign.
The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
2.) A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash («##») sign.
Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
Quoting from Books Online:
Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
- A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
- All other local temporary tables are dropped automatically at the end of the current session.
- Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
I didn’t see any answers that show users where we can find a Global Temp table. You can view Local and Global temp tables in the same location when navigating within SSMS. Screenshot below taken from this link.
Databases —> System Databases —> tempdb —> Temporary Tables
Local temporary tables: if you create local temporary tables and then open another connection and try the query , you will get the following error.
the temporary tables are only accessible within the session that created them.
Global temporary tables: Sometimes, you may want to create a temporary table that is accessible other connections. In this case, you can use global temporary tables.
Global temporary tables are only destroyed when all the sessions referring to it are closed.
It is worth mentioning that there is also: database scoped global temporary tables(currently supported only by Azure SQL Database).
Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users’ sessions across the whole SQL Server instance.
Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL Database. User sessions from other databases cannot access global temporary tables.
ALTER DATABASE SCOPED CONFIGURATION
APPLIES TO: Azure SQL Database (feature is in public preview)
Allows setting the auto-drop functionality for global temporary tables. The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart.
With Azure SQL Database single databases and elastic pools, this option can be set in the individual user databases of the SQL Database server. In SQL Server and Azure SQL Database managed instance, this option is set in TempDB and the setting of the individual user databases has no effect.
SQL Server – Temporary Tables
It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there.
Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.
Temporary tables can be local or global.
- A local table is visible only to the session that it is created from and is dropped automatically when the connection is closed. Other users cannot access the table, although they may create their own temporary with the same name. In such situations the two temporary tables are in no way linked.
- Global temporary tables are available to all users and are automatically dropped when the last user accessing the table disconnects from the database. Although both types of table will be automatically dropped, it is common practice to delete them manually when no longer required.
Local Temporary Tables
Local Temporary Tables are distinct within modules and embedded SQL programs within SQL Server sessions. Local temporary tables are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.
If a local temporary table created in a stored procedure, it is dropped automatically when the stored procedure is finished. This means that this local temporary table can be referenced only by nested stored procedures.
The local temporary table cannot be referenced by the stored procedure or application that called the stored procedure that created the local temporary table.
For example, using CREATE TABLE approach, the syntax to create a local temporary tables in SQL Server (Transact-SQL) is:
Global Temporary Tables
The name of the SQL Global temporary table starts with the double hash (“##”) symbol and stored in the tempdb. Global temp tables in SQL Server are like permanent tables, and they are available to all the users in that instance.
If all the user disconnects from their session, the SQL global temp tables will automatically delete.
Let us create a SQL global temporary table called GlobalTemp.
Global temporary table is automatically dropped when the session that created the table ends and the last active Transact-SQL statement (not session) referencing this table in other sessions ends.
The following is an example to insert a few samples or random records into the global temporary table in SQL Server that we created inside the tempdb using the INSERT Statement.
Parameters or Arguments
- table_name: The name of the local temporary table that you wish to create. The name of the local temporary table starts with the # character.
- column1, column2: The columns that you wish to create in the local temporary table. Each column must have a datatype.
The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.
The name of local temporary tables are prefixed with the # character (ie: #employees).
Let’s look at an example of how to create a local temporary table in SQL Server (Transact-SQL).
This example would create a local temporary tables called #employees in SQL Server which has 4 columns.
- The first column is called example_id which is created as an INT datatype and can not contain NULL values.
- The second column is called trek1 which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
- The third column is called trek2 which is a VARCHAR datatype but can contain NULL values.
- The fourth column is called trek3 which is a MONEY datatype which can contain NULL values.
- The primary key for the #employees table is the example_id column.
- This #employees table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it.
SELECT INTO
Microsoft SQL Server provides two ways to create temporary tables: SELECT INTO and CREATE TABLE statements.
The first way to create a temporary table is to use the SELECT INTO statement as shown below:
The name of the temporary table starts with a hash symbol (#). For example, the following statement creates a temporary table using the SELECT INTO statement:
In this example, we created a temporary table named #trek_products with two columns derived from the select list of the SELECT statement. The statement created the temporary table and populated data from the production.products table into the temporary table.
Once you execute the statement, you can find the temporary table name created in the system database named tempdb, which can be accessed via the SQL Server Management Studio using the following path System Databases > tempdb > Temporary Tables.
CREATE TABLE
Creating a temporary table uses the same syntax used to create a standard table. To signify that the table is a local temporary table, a hash (or pound) symbol (#) is used as a prefix to the table name. The name may be up to 116 characters in length.
Add the following temporary table creation statement to the stored procedure code:
Populating the Initial Data
To initialise the data in the temporary table we will copy all of the customer numbers from the live data. All other columns in the will contain NULL values at this point. We can copy the customer numbers using an INSERT statement that sources its information from a query as follows:
Dropping Temporary Tables
Temporary tables are dropped automatically when the database connection or session in which they are created is closed. However, if want to delete them without closing the current session, you can use the DROP TABLE statement, as follow:
The above statement will delete the temporary table table_name from the database. After that, the original table_name base table becomes visible.
At the beginning of a SQL script we could even, for example, test if the temporary table already exist and then drop it if it is true. The following code is valid for SQL Server 2014 and below versions (but not only, also for above versions):
The following code instead, is valid only on SQL Server 2016 and above. In SQL Server 2016, Microsoft introduced DIY or DROP IF EXISTS functionality. By adding IF EXISTS to the drop statement, you can drop the object only when it exists in the database.
List of all Temporary Tables created on SQL Server
The following two statements will give us a complete list of all available temporary tables on SQL Server instance:
Add Columns to Temporary Tables
Adding new temporary columns to a temporary table is also another useful modification usually used to manipulate table schema without altering the production table or to test some new trasnformation from other existing columns. The following approach can be used to do this:
The following code creates a primary key on the ExampleId column:
The ALTER TABLE is called on the Ex_Temp table and the primary key is added on the ExampleId column.
And finally, here a simple approach to change only a single variable to create multiple columns into a temporary (not only) table: