By: Rick Dobson
Overview
In this part of the tutorial, we will look at how to reference local and global temp tables when using the same connection.
Referencing Local and Global SQL Server Temp Tables in the Same Connection
When you create local temp tables in a connection, then you can reference local temp tables in the same connection. Local temp tables created in one connection cannot be referenced from a different connection. Global temp tables can be referenced from the same connection or a different connection so long as the global temp table has not gone out of scope.
The database connection is the same when a temp table is created and referenced from a script within the same SSMS tab. The database connection is different when it is created in one SSMS tab, but it is referenced in a different SSMS tab. Because each T-SQL script file resides in its own SSMS tab, you can load and run scripts from the same or different T-SQL script files and evaluate the impact of connections on 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 tab.
The following script gives simple examples of querying local temp tables and global temp tables. The queries for both local and global temp tables, respectively, counts the rows across all local tables and all global tables. As you can see from the corresponding Results pane both queries return the same count of employees – namely, 290. This implies the local temp tables are referenced from the same connection as the one used to create the local temp tables. The global temp tables can be referenced in the same or a different tab so long as the connection used to create the global temp table is still open or there is at least one other active programmatic reference to the global temp table.
-- count of rows for three local temp tables
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_local_temp_tables
FROM
(SELECT * FROM #married_female
UNION
SELECT * FROM #married_male
UNION
SELECT * FROM #not_married
) married_female_married_male_not_married
-- count of rows for two global temp tables
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_global_temp_tables
FROM
(SELECT * FROM ##not_married
UNION
SELECT * FROM ##married
) not_married_married
