Lokalne i globalne tabele tymczasowe w SQL Server

Jaka jest różnica między lokalnymi i globalnymi tabelami tymczasowymi w programie SQL Server?

Uważam, że to wyjaśnienie jest dość jasne (to czysta kopia od Technet ):

Istnieją dwa typy tabel tymczasowych: lokalne i globalne. Lokalne tabele tymczasowe są widoczne tylko dla ich twórców podczas tego samego połączenia z wystąpieniem programu SQL Server, jak podczas pierwszego tworzenia lub przywoływania tabel. Lokalne tabele tymczasowe są usuwane po odłączeniu użytkownika od wystąpienia programu SQL Server. Globalne tabele tymczasowe są widoczne dla każdego użytkownika i każdego połączenia po ich utworzeniu i są usuwane, gdy wszyscy użytkownicy odwołujący się do tabeli odłączą się od wystąpienia SQL Server.

• Zmienne tabeli ( DECLARE @t TABLE) są widoczne tylko dla połączenia, które je tworzy, i są usuwane po zakończeniu procedury wsadowej lub procedury składowanej.

• Lokalne tabele tymczasowe ( CREATE TABLE #t) są widoczne tylko dla połączenia, które je tworzy, i są usuwane po zamknięciu połączenia.

• Globalne tabele tymczasowe ( CREATE TABLE ##t) są widoczne dla wszystkich i są usuwane po zamknięciu wszystkich połączeń, które się do nich odwoływały.

• Stałe tabele Tempdb ( USE tempdb CREATE TABLE t) są widoczne dla wszystkich i są usuwane po ponownym uruchomieniu serwera.

1.) Lokalna tabela tymczasowa istnieje tylko na czas trwania połączenia lub, jeśli została zdefiniowana w instrukcji złożonej, na czas trwania instrukcji złożonej.

Lokalne tabele tymczasowe są dostępne tylko dla sesji lub połączenia programu SQL Server (czyli pojedynczego użytkownika), które utworzyło tabele. Są one automatycznie usuwane po zamknięciu sesji, która utworzyła tabele. Nazwa lokalnej tabeli tymczasowej jest oznaczona pojedynczym znakiem krzyżyka („#”).

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Name','Address');
GO
Select * from #LocalTemp

Zakres Lokalnej tabeli tymczasowej istnieje do bieżącej sesji bieżącego użytkownika, czyli do bieżącego okna zapytania. Jeśli zamkniesz bieżące okno zapytania lub otworzysz nowe okno zapytania i spróbujesz znaleźć powyższą utworzoną tabelę tymczasową, spowoduje to błąd.

2.) Globalna tabela tymczasowa pozostaje w bazie danych na stałe, ale wiersze istnieją tylko w ramach danego połączenia. Po zamknięciu połączenia dane w globalnej tabeli tymczasowej znikają. Jednak definicja tabeli pozostaje w bazie danych w celu uzyskania dostępu, gdy baza danych zostanie otwarta następnym razem.

Globalne tabele tymczasowe są dostępne dla wszystkich sesji lub połączeń programu SQL Server (czyli dla wszystkich użytkowników). Mogą być tworzone przez dowolnego użytkownika połączenia SQL Server i są one automatycznie usuwane po zamknięciu wszystkich połączeń SQL Server. Nazwa globalnej tabeli tymczasowej jest oznaczona podwójnym znakiem krzyżyka („##”).

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Name','Address');
GO
Select * from ##GlobalTemp

Globalne tabele tymczasowe są widoczne dla wszystkich połączeń programu SQL Server, a lokalne tabele tymczasowe są widoczne tylko dla bieżącego połączenia programu SQL Server.

Cytowanie z Books Online:

Lokalne tabele tymczasowe są widoczne tylko w bieżącej sesji; globalne tabele tymczasowe są widoczne dla wszystkich sesji.

Tabele tymczasowe są automatycznie usuwane, gdy wychodzą poza zakres, chyba że jawnie usuwane przy użyciu DROP TABLE:

• Lokalna tabela tymczasowa utworzona w procedurze składowanej jest usuwana automatycznie po zakończeniu procedury składowanej. Do tabeli mogą się odwoływać dowolne zagnieżdżone procedury składowane wykonywane przez procedurę składowaną, która utworzyła tabelę. Proces, który wywołał procedurę składowaną, która utworzyła tabelę, nie może odwołać się do tabeli.
• Wszystkie inne lokalne tabele tymczasowe są automatycznie usuwane na koniec bieżącej sesji.
• Globalne tabele tymczasowe są automatycznie usuwane, gdy sesja, która utworzyła tabelę, kończy się i wszystkie inne zadania przestają się do nich odwoływać. Skojarzenie między zadaniem a tabelą jest utrzymywane tylko przez cały czas trwania pojedynczej instrukcji języka Transact-SQL. Oznacza to, że globalna tabela tymczasowa jest usuwana po zakończeniu ostatniej instrukcji języka Transact-SQL, która aktywnie odwoływała się do tabeli po zakończeniu sesji tworzenia.

Lokalne tabele tymczasowe : jeśli utworzysz lokalne tabele tymczasowe, a następnie otworzysz inne połączenie i spróbujesz wykonać zapytanie, pojawi się następujący błąd.

tabele tymczasowe są dostępne tylko w ramach sesji, która je utworzyła.

Globalne tabele tymczasowe : Czasami możesz chcieć utworzyć tabelę tymczasową, która będzie dostępna dla innych połączeń. W takim przypadku możesz użyć globalnych tabel tymczasowych.

Globalne tabele tymczasowe są niszczone tylko wtedy, gdy wszystkie sesje, które się do nich odnoszą, są zamknięte.

Warto wspomnieć, że istnieją również: globalne tabele tymczasowe o zasięgu bazy danych (obecnie obsługiwane tylko przez Azure SQL Database).

Globalne tabele tymczasowe dla SQL Server (zainicjowane nazwą tabeli ##) są przechowywane w tempdb i współużytkowane przez wszystkie sesje użytkowników w całej instancji SQL Server.

Azure SQL Database obsługuje globalne tabele tymczasowe, które są również przechowywane w tempdb i mają zakres do poziomu bazy danych. Oznacza to, że globalne tabele tymczasowe są udostępniane dla wszystkich sesji użytkowników w tej samej Azure SQL Database. Sesje użytkowników z innych baz danych nie mogą uzyskać dostępu do globalnych tabel tymczasowych.

-- Session A creates a global temp table ##test in Azure SQL Database testdb1
-- and adds 1 row
CREATE TABLE ##test ( a int, b int);
INSERT INTO ##test values (1,1);

-- Session B connects to Azure SQL Database testdb1 
-- and can access table ##test created by session A
SELECT * FROM ##test
---Results
1,1

-- Session C connects to another database in Azure SQL Database testdb2 
-- and wants to access ##test created in testdb1.
-- This select fails due to the database scope for the global temp tables 
SELECT * FROM ##test
---Results
Msg 208, Level 16, State 0, Line 1
Invalid object name '##test'

ZMIANA KONFIGURACJI ZAKRESU BAZY DANYCH

GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }

DOTYCZY: Azure SQL Database (funkcja jest w publicznej wersji zapoznawczej)

Umożliwia ustawienie funkcji automatycznego upuszczania globalnych tabel tymczasowych. Wartość domyślna to WŁĄCZONE, co oznacza, że ​​globalne tabele tymczasowe są automatycznie usuwane, gdy nie są używane przez żadną sesję. Po ustawieniu na OFF globalne tabele tymczasowe muszą być jawnie usuwane za pomocą instrukcji DROP TABLE lub zostaną automatycznie usunięte po ponownym uruchomieniu serwera.

W przypadku pojedynczych baz danych Azure SQL Database i pul elastycznych tę opcję można ustawić w poszczególnych bazach danych użytkowników na serwerze SQL Database. W wystąpieniu zarządzanym programu SQL Server i Azure SQL Database ta opcja jest ustawiona w TempDB, a ustawienie baz danych poszczególnych użytkowników nie ma żadnego wpływu.

Nie widziałem żadnych odpowiedzi, które wskazują użytkownikom, gdzie możemy znaleźć tabelę Global Temp. Podczas nawigacji w ramach SSMS można wyświetlać lokalne i globalne tabele tymczasowe w tej samej lokalizacji. Poniższy zrzut ekranu pochodzi z tego linku .

Bazy danych -> Systemowe bazy danych -> tempdb -> Tabele tymczasowe