Локальные и глобальные временные таблицы в SQL Server

Question

В чем разница между локальными и глобальными временными таблицами в SQL Server?

Answer 1

I find this explanation quite clear (it's pure copy from Technet):

There are two types of temporary tables: local and global. Локальные временные таблицы видны только их создателям при том же подключении к экземпляру SQL Server, что и при первом создании или обращении к таблицам. Локальные временные таблицы удаляются после того, как пользователь отключается от экземпляра SQL Server. Глобальные временные таблицы видны любому пользователю и любому соединению после их создания и удаляются, когда все пользователи, ссылающиеся на таблицу, отключаются от экземпляра SQL Server.

Answer 2

• Табличные переменные (DECLARE @t TABLE) видны только соединению, которое его создает, и удаляются при завершении пакета или хранимой процедуры.

• Локальные временные таблицы (CREATE TABLE #t) видны только соединению, которое его создает, и удаляются при закрытии соединения.

• Глобальные временные таблицы (CREATE TABLE ##t) видны всем и удаляются, когда все соединения, на которые они ссылаются, закрыты.

• Постоянные таблицы Tempdb (USE tempdb CREATE TABLE t) видны всем и удаляются при перезапуске сервера.

Answer 3

1.) Локальная временная таблица существует только на время соединения или, если определено внутри составного оператора, на время составного оператора.

Локальные временные таблицы доступны только для сеанса или соединения SQL Server (имеется в виду один пользователь), которые создали таблицы. Они автоматически удаляются, когда сеанс, создавший таблицы, был закрыт. Имя локальной временной таблицы помечается одинарным хешем ("#").

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

Область действия локальной временной таблицы существует для текущего сеанса текущего пользователя, значит для текущего окна запроса. Если вы закроете текущее окно запроса или откроете новое окно запроса и попытаетесь найти созданную выше временную таблицу, это выдаст вам ошибку.

---

2.) Глобальная временная таблица остается в базе данных постоянно, но строки существуют только внутри данного соединения. Когда соединение закрыто, данные в глобальной временной таблице исчезают. Однако определение таблицы остается в базе данных для доступа при следующем открытии базы данных.

Глобальные временные таблицы доступны для всех сеансов или соединений SQL Server (означает всех пользователей). Они могут быть созданы любым пользователем подключения к SQL Server, и они автоматически удаляются, когда все подключения к SQL Server были закрыты. Имя глобальной временной таблицы помечается двойным хеш-символом ("##").

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

Глобальные временные таблицы видны всем соединениям SQL Server, а локальные временные таблицы видны только текущему соединению SQL Server.

Answer 4

Цитирование из Книги Онлайн:

Локальные временные таблицы видны только в текущем сеансе; глобальные временные таблицы видны всем сеансам.

Временные таблицы автоматически удаляются, когда они выходят из области видимости, если только они явно не удалены с помощью DROP TABLE

• Локальная временная таблица, созданная в хранимой процедуре, автоматически удаляется после ее завершения. На таблицу могут ссылаться любые вложенные хранимые процедуры, выполняемые хранимой процедурой, создавшей таблицу. На таблицу нельзя ссылаться процессом, который вызвал хранимую процедуру, которая создала таблицу.
• Все остальные локальные временные таблицы автоматически удаляются в конце текущего сеанса.
• Глобальные временные таблицы автоматически удаляются, когда сеанс, создавший таблицу, заканчивается и все другие задачи перестают ссылаться на них. Связь между задачей и таблицей поддерживается только в течение жизни одного оператора Transact-SQL. Это означает, что глобальная временная таблица удаляется по завершении последнего оператора Transact-SQL, который активно ссылался на таблицу, когда завершался сеанс создания.

Answer 5

Я не видел ответов, показывающих пользователям, где можно найти таблицу Global Temp. Вы можете просматривать локальные и глобальные временные таблицы в одном месте при навигации в SSMS. Снимок экрана ниже взят по этой ссылке.

Базы данных -> Системные базы данных -> tempdb -> Временные таблицы

Answer 6

Локальные временные таблицы: если вы создадите локальные временные таблицы, а затем откроете другое соединение и попытаетесь выполнить запрос, вы получите следующую ошибку.

временные таблицы доступны только в рамках сеанса, в котором они были созданы.

Глобальные временные таблицы: иногда вам может потребоваться создать временную таблицу, доступную для других подключений. В этом случае вы можете использовать глобальные временные таблицы.

Глобальные временные таблицы уничтожаются только тогда, когда все связанные с ними сеансы закрываются.

Answer 7

Стоит отметить, что есть также: глобальные временные таблицы в области базы данных(в настоящее время поддерживается только базой данных SQL Azure).

Глобальные временные таблицы для SQL Server (инициируемые с помощью имени таблицы ##) хранятся в базе данных tempdb и совместно используются всеми сеансами пользователей по всему экземпляру SQL Server.

База данных SQL Azure поддерживает глобальные временные таблицы, которые также хранятся в базе данных tempdb и находятся на уровне базы данных. Это означает, что глобальные временные таблицы являются общими для всех сеансов пользователей в одной и той же базе данных SQL Azure. Сеансы пользователей из других баз данных не могут получить доступ к глобальным временным таблицам.

-- 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'