Where Does Local Temp Tables and Global Temp Tables Stored

By:   |   Updated: 2019-05-09   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Temp Tables

Job

Temp tables are temporary in the sense that they exist for atomic number 3 long as the database connection which created and populated them exists.  Subsequently the connection is closed, the temp table drops from SQL Server.  You can also explicitly drop a temp table even when the association cadaver open (for example, when the values in the temporary worker table are nary longer needed).  There are two varieties of temp tables.  Local temporary worker tables are only accessible from their creation context, such as the connection.  Planetary temp tables are accessible from different connection contexts.  Both topical anaestheti and orbicular temporary worker tables repose in the tempdb database.  In that section we bequeath cover each of these concepts.

Answer

For the examples below we are using the AdventureWorks2014 database. Download a copy and restore to your instance of SQL Server to test the below scripts.

Creating and Populating SQL Server Localized Temporary worker Tables

A local anesthetic temporary worker table name begins with a unvarying # sign.  Although you stern create a local anesthetic temporary table from any database linguistic context, a section temp table always resides in the tempdb database.  Because a topical temp table is a database table, you must drop whatever prior version of a local temp table in front you hind end create and populate a energising version of a local temporary table.

You canful make over and populate a local temp put over just equal any else database table.  For instance, you can use a select financial statement  with an into clause.  All other select statement clauses likewise the into clause define a result prepare for populating a local temporary worker table.  The select list items specify columns for the local temp table.  The into clause argument specifies the constitute of the local temporary postpone.

The following script indicates some sentence structure for creating three local temp tables.

• The use statement indicates the code operates in the context of the Temporary_Data_Stores_Tutorial database.  Several sections of this report use this database.  In the following T-SQL script, the name of the database is non critical to the procedure of the encrypt.  Therefore, you can use any other database that you prefer.
• Next, trio attempt…apprehension blocks drop preceding local temporary table versions if they exist with the names of #married_male, #married_female, and #not_married.
• Each of the three local temp tables are based happening subsets from the Employee table of the HumanResources outline in the sample. The subsets are specified past setting(s) in the where clause for the select statement creating and populating each local temp table.
• The into clause within all select statement designates the local temporary remit appoint for the result set developed by the rest of the select statement syntax.

USE [Temporary_Data_Stores_Tutorial] GO  -- drop local temp tables BEGIN TRY   DROP TABLE #married_male END TRY BEGIN Catch up with   PRINT '#married_male not available to drop' Remnant CATCH  BEGIN Essa   DROP TABLE #married_female Stop TRY Get CATCH   Mark '#married_female not available to drop' END CATCH  Set out TRY   DROP TABLE #not_married END Examine BEGIN CATCH   Black and white '#not_married non available to drop' END CATCH  -- people local temp board for married, male employees SELECT   [BusinessEntityID] INTO #married_male FROM [AdventureWorks2014].[HumanResources].[Employee] WHERE [MaritalStatus] = 'M' AND sexuality = 'M'  -- people localized temp table for married, feminine employees SELECT   [BusinessEntityID] INTO #married_female FROM [AdventureWorks2014].[HumanResources].[Employee] WHERE [MaritalStatus] = 'M' AND gender = 'F'  -- populate local anaesthetic temporary table for not married employees SELECT   [BusinessEntityID] INTO #not_married FROM [AdventureWorks2014].[HumanResources].[Employee] WHERE [MaritalStatus] != 'M'

The next screen shot displays the contents of the Messages tab from SSMS.  There is none Results tab because the SELECT statements create and people local temporary tables instead of populate result sets that bottom be displayed.  The code generates three result sets for creating and populating local temp tables.  The script creates 3 result sets with row counts of 97, 49, and 144 rows each.  The sum of these words counts equals the total number of rows in the Employee table within the HumanResources outline of the AdventureWorks2014 database.

The propaedeutic script kit and boodle with deuce-ac databases.

• The Utilize statement designates the Temporary_Data_Stores_Tutorial database.  This is the default database for the operation of the code in the script.
• A link at the finish of this section designates one place for downloading the AdventureWorks2014 database along with releases of the AdventureWorks databases for other versions of SQL Server in any case SQL Host 2014.  The AdventureWorks2014 database is explicitly relevant to the past script because the code uses a three-part name to designate a source table name in the from clause.  Without the database name start out of the three-disunite name in the from clause, the code would default to probing the HumanResources schema for the Employee table in the Temporary_Data_Stores_Tutorial database.
• The third database implicitly referenced in the preceding script is the tempdb database.  Any table whose table name begins with a # symbol resides in the tempdb as local temp table for the current database connexion.

Creating and Populating SQL Server Global Temp Tables

The following cipher demonstrates how to create and populate global temporary tables.  If you contrast the following playscript with the preparative book, it is effortless to spot that table names start out with two leading hasheesh signs (##).  In the following script, the global table names are ##not_married and ##married.  Otherwise the leading double up hash sign, the process for creating and populating a global temp table is the same American Samoa for a local temp table.

-- drop global temporary tables Set out Essay   DROP TABLE ##not_married Oddment Hear Set about CATCH   PRINT '##not_married not available to drop' END CATCH  Menachem Begin TRY   Spend TABLE ##married END TRY BEGIN CATCH   PRINT '##married non available to drop' END CATCH  -- not married employees in globular temp SELECT [BusinessEntityID] INTO ##not_married FROM [AdventureWorks2014].[HumanResources].[Employee] WHERE [MaritalStatus] != 'M'  -- married employees in global temp SELECT [BusinessEntityID] INTO ##married FROM [AdventureWorks2014].[HumanResources].[Employee] WHERE [MaritalStatus] = 'M'          

After running the preceding script, you return two global tables.  One of these is for all married employees; this table has 146 rows.  The other global remit is for completely employees who are not married; this table has 144 rows.  The sum of row counts for these two global temp tables matches the sum of row counts for the leash local temporary worker tables.

Itemization SQL Server Local anaesthetic and Global Temporary Tables

As an administrator or even an advanced user, you may sometimes witness it useful to heel altogether the global and temporary tables in the tempdb database.  Anyone tush interrogation objects in tempdb (unless permission is expressly revoked, which is not recommended).  The set of every tables in tempdb put up be obtained by referencing tempdb.sys.tables in the from article of a select statement.  The tables in tempdb can be more than just global and local temp tables.  Thus, you need to limit the result set with a where clause to list just global temp tables OR local temp tables.

The following script provides the sentence structure for itemization just worldwide temporary tables operating theatre local temp tables.  The filter for listing global temp tables is to find all tempdb tables beginning with ##.  A two-part filter is required for list local temp tables.  The local temp table names must begin with # but not begin with ##.

-- list world temporary worker tables SELECT   name,   type,   type_desc,   create_date FROM tempdb.sys.tables WHERE name LIKE '##%'  -- list only local anaesthetic temp tables SELECT   nominate,   type,   type_desc,   create_date FROM tempdb.sys.tables WHERE name LIKE '#%' AND key NOT LIKE '##%'

The following display shows the final result sets from the pre-existing two SELECT statements.  The crown event set is for global temp tables with names of ##not_married and ##married.  The bottom outcome set is for local temp tables with names of #married_female, #married_male, and #not_married.

SQL Server allows users to assign the same cite to deuce dissimilar local temporary worker tables – so long American Samoa the tables are from opposite connections.  If this happens, and then the first component part of the both local temp tables will induce the same name.  However, the trailing part of the name will be different for each local temp table with the same beginning part.  SQL Server tracks the two different local temp tables for for each one connection based happening the trailing part of the name.  In this means, SQL Waiter give notice use the proper origin local temp table from cardinal different connections where for each one local temp table may have different data in its connection.

Referencing SQL Server Local and Global Temporary worker Tables in the Aforesaid Connective

When you produce local temp tables in a connection, then you tush reference local temp tables in the same connexion.  Local temp tables created in one connection cannot be referenced from a different connectedness.

Global temp tables tush be documented from the same connection or a different connection so long As the global temporary worker table has not gone out of scope.

The database connection is the same when a temp table is created and referenced from a script inside the same SSMS tab.  The database connection is different when it is created in one SSMS tab, but it is documented in a different SSMS tab.  Because each T-SQL script file resides in its own SSMS pill, you can lading and running play scripts from the same or different T-SQL script files and evaluate the impact of connections connected the ability to reference local and global temp tables.  A global temp goes out of scope when the tab in which it was created is closed, and there is no other active programmatic reference to it from another lozenge.

The following playscript gives simple examples of querying topical temp tables and worldwide temp tables. The queries for some local anaesthetic and global temporary worker tables, severally, counts the rows across all local tables and all global tables.  As you can see from the in proportion to Results pane both queries return the same count of employees – namely, 290.  This implies the local temporary tables are referenced from the same connective as the unmatchable used to create the local temporary tables.  The global temp tables derriere atomic number 4 referenced in the selfsame or a different tab thus agelong arsenic the connection wont to make over the global temp postpone is still open or there is at least one other active programmatic extension to the world temp table.

-- count of rows for three local temp tables  -- with whol employees in AdventureWorks2014 SELECT COUNT(*) count_from_local_temp_tables FROM (SELECT * FROM #married_female       UNION       Quality * FROM #married_male       Labor union       SELECT * FROM #not_married) married_female_married_male_not_married  -- numeration of rows for deuce global temp tables  -- with all employees in AdventureWorks2014 SELECT COUNT(*) count_from_global_temp_tables FROM (Pick out * FROM ##not_married       UNION       SELECT * FROM ##married) not_married_married

Referencing SQL Server Section and Global Temp Tables in Another Connection

When the cardinal Prize statements are run from a assorted connection than the one in which they were created, then the upshot is different.

So, if the local and global temporary tables were created in one tab (one connection) and test in a different tab (second connection), so the outcome would constitute different.  A GO statement should follow the first SELECT statement to allow the connection to run both SELECTs flat after a failure in the original SELECT statement.  The Run statement facilitates re-starting after an error.

The first query counting the rows in the set of three local temp tables fails.  This is because the SELECT query referencing local temp tables runs from a different connection than the connection used to create the local temp tables.  The following Messages tab points attention to this nonstarter with Msg 208.   The fault message indicates that the #married_female local anaesthetic temp table is not available in the ordinal link.  This is because the #married_female local temporary table is only available in the connector for the one in which IT was created.  Of course, the unusual deuce local temp tables (#married_male and #not_married) are likewise missing from the second tab, merely the first error aborts the SELECT statement in the batch.

The indorse SELECT program line operates in a distinguishable clutch because of the Decease assertion favorable the starting time SELECT assertion.  Also, this second Prize argument queries 2 global temp tables.  Because the second SELECT statement operates on global temp tables, it succeeds although it is from a different connection than the one used to make up the global temp tables.  The pursuit Results tab image displays the outcome from the second query.  This interrogation returns a total count of employees of 290, which is illustrious to comprise the slump number of employees in the Employees shelve of the HumanResources schema in the AdventureWorks2014 database.

Localized and Orbicular Temporary Tables Inside and Outside of Stored Procedures

Even within the same tab, global and local anesthetic temp tables tooshie work other than.  A stored procedure is a familiar lesson of how the cardinal types of temp tables operate other than.  Local temporary worker tables created in a stored procedure within a connection cannot be documented outdoorsy of the stored procedure.  However, global temporary tables created inside a stored procedure bum follow referenced from outside the stored procedure.

The following script demonstrates how references to localized temporary tables and global temp tables operate when the temporary remit is created in a stored procedure, merely IT is referenced outside the stored procedure.  Notice that the for_local_and_global_temp_tables_demo stored subroutine creates two temp tables.  One table onymous #married_male_from_proc is a local anesthetic temp table.  Other remit named ##not_married_from_proc is a global temp table.

The script creates the for_local_and_global_temp_tables_demo stored procedure in the Temporary _Data_Stores_Tutorial database with a create proc statement.  The stored procedure is very simple.  Cardinal select into command creates and populates #married_male_from_proc, a local temp table.  Another select into program line creates and populates ##not_married_from_proc, a ball-shaped temp table.

After the make over proc statement, an exec statement invokes the for_local_and_global_temp_tables_demo stored subprogram in its own batch.

Next, a SELECT statement in its ain batch attempts to perform a Prime assertion on the local temp table created in the stored procedure.  Even though local temp table created in the stored procedure is from the Lapp tab as the select statement querying the temp tabular array right the stored procedure, the query fails.  The Messages tab shows the error is for an invalid object name.  This follows from the fact that the localized table mention, #not_married_from_proc, has a scope that is restricted to the stored procedure.

On the other hand, the SELECT financial statement for the global temp put of named ##not_married_from_proc succeeds right the stored procedure.  The orbicular temporary table is not restricted to a compass inside the stored proc.  It returns a count of the employees who are not wedded.  This count is validated past another query that runs directly against the Employee table in the HumanResources schema of the AdventureWorks2014 database.

Incoming Steps

Here are or s links to resources that you may find useful to help you grow your understanding of content from this section of the instructor.

• AdventureWorks sample databases
• Set u Your Own Copy of the SQL Server AdventureWorks2014 Database
• Differences between SQL Server evanescent tables and set back variables
• Launching to Temporary Tables in SQL Host
• Temporary Tables in SQL Host
• SQL Server Temporary Tables
• How to stick a list of global temporary tables in the current database?
• Is there a direction to get a list of all current temporary tables in SQL Server?
• tempdb database in Parallel Data Warehouse

Concerned Articles

Popular Articles

About the source

Wrick Dobson is a Microsoft Certified Technical Specialist and well accomplished SQL Server and Access author.

View all my tips

Clause Unalterable Updated: 2019-05-09

Where Does Local Temp Tables and Global Temp Tables Stored

Source: https://www.mssqltips.com/sqlservertip/6035/local-vs-global-sql-server-temporary-tables/

Share this post