Local and global temporary tables in SQL Server
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.
When and Why to use global temporary table over local temporary table in SQL Server
Personally, I don't see a need for global temporary tables. My inclination is to store such data in tables along with other tables. But, clearly, they do fill a need.
The answer to your first question is that global temporary tables are instance specific -- if by instance you mean a SQL Server instance. They are available to all users and all connections on the server. Global temporary tables start with the prefix ##
. All references to such a table, say ##table
, are to the same table (within a server instance).
The answer to the second is that SQL Server supports both global and local temporary tables. Local temporary tables are more common. All temporary tables have the nice feature that they disappear when they are automatically deleted when the server restarts and under other circumstances.
Global temporary tables in SQL Server
Found an interesting reference here:
Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions). If two different sessions try the above code, if the first is still active, the second will receive the following:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##people' in the database.
I have yet to see a valid justification for the use of a global ##temp table. If the data needs to persist to multiple users, then it makes much more sense, at least to me, to use a permanent table. You can make a global ##temp table slightly more permanent by creating it in an autostart procedure, but I still fail to see how this is advantageous over a permanent table. With a permanent table, you can deny permissions; you cannot deny users from a global ##temp table.
When do Global Temporary Tables get destroyed in SQL Server?
From CREATE TABLE
:
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
Although as a personal nit-pick, I'd have used after
instead of when
in that last sentence.
So, per your example, session 57 keeps the global temporary table around so long as it's actually executing a statement that relies upon the table. As soon as that statement is complete - so the session has moved onto another statement or it's sitting idle - the global temp table will be dropped.
Deleting Global Temporary Tables (##tempTable) in SQL Server
Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.
Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them.
MS SQL Server - safe concurrent use of global temp table?
I'll start by saying that, on the long term, I will follow Gordon's advice, i.e. I will take the necessary steps to introduce a normal table in the database to store client application information which needs to be accessible in the triggers.
But since this was not really possible now because of time constrains (it takes weeks to get the necessary formal approvals for a new normal table), I came up with a solution for preventing SQL Server from dropping the global temp table between the check for its existence and the MERGE
statement.
There is some information out there about when a global temp table is dropped by SQL Server; my personal tests showed that SQL Server drops a global temp table the moment the session which created it is closed and any other transactions started in other sessions which changed data in that table are finished.
My solution was to fake data changes on the global temp table even before I check for its existence. If the table exists at that moment, SQL Server will then know that it needs to keep it until the current transaction finishes, and it cannot be dropped anymore after the check for its existence. The code looks now like this (properly commented, since it is kind of a hack):
-- Faking a delete on the table ensures that SQL Server will keep the table until the end of the transaction
-- Since ##VTT_CONTEXT_INFO_USER_TASK may actually not exist, we need to fake the delete inside TRY .. CATCH
-- FUTURE 2016, Feb 03: A cleaner solution would use a real table instead of a global temp table.
BEGIN TRY
-- Because schema errors are checked during compile, they cannot be caught using TRY, this can be done by wrapping the query in sp_executesql
DECLARE @QueryText NVARCHAR(100) = 'DELETE ##VTT_CONTEXT_INFO_USER_TASK WHERE 0 = 1'
EXEC sp_executesql @QueryText
END TRY
BEGIN CATCH
-- nothing to do here (see comment above)
END CATCH
IF OBJECT_ID('tempdb..##VTT_CONTEXT_INFO_USER_TASK') IS NULL
CREATE TABLE ##VTT_CONTEXT_INFO_USER_TASK (
session_id smallint,
login_time datetime,
HstryUserName VDT_USERNAME,
HstryTaskName VDT_TASKNAME,
)
MERGE ##VTT_CONTEXT_INFO_USER_TASK As target
USING (SELECT @@SPID, @HstryUserName, @HstryTaskName) as source (session_id, HstryUserName, HstryTaskName)
ON (target.session_id = source.session_id)
WHEN MATCHED THEN
UPDATE SET HstryUserName = source.HstryUserName, HstryTaskName = source.HstryTaskName
WHEN NOT MATCHED THEN
INSERT VALUES (@@SPID, @LoginTime, source.HstryUserName, source.HstryTaskName);
Although I would call it a "use it at your own risk" solution, it does prevent that the use of the global temp table in other sessions affects its use in the current one, which was the concern that made me start this thread.
Thanks all for your time! (from text formatting edits to replies)
SQL Server Global Temporary Table Locking
Why not include it inside a transaction block
like
begin transaction
SELECT *
INTO ##TempEmployee
FROM Employee
Related Topics
How to Detect Query Which Holds the Lock in Postgres
Best Way to Work with Transactions in Ms SQL Server Management Studio
Which Is the Best Way to Form the String Value Using Column from a Table with Rows Having Same Id
How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select
How to Copy Data from One Column to Another in the Same Table
Remove the Last Character in a String in T-Sql
What Does "&" Means in This SQL Where Clause
Convert One Row into Multiple Rows with Fewer Columns
How to Optimize MySQL Query (Group and Order)
Possible Recursive Cte Query Using Date Ranges
Global Temporary Tables in SQL Server
It's Possible to Create a Rule in Preceding Rows in Sum
Insert Xml File in SQL via Ssis
Perform Join Query in Google Cloud Firestore
Shows Blanks for Repeating Values in a Result Set
How to Have Multiple "With As" in Single SQL - Oracle SQL