Особенности скоупа временных таблиц и переменных в SQL Server

Быстрый ответ

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

Пример локальной таблицы:

CREATE TABLE #LocalTemp (ID INT);
-- Эта таблица схожа с личным дневником: доступ к нему есть только у вас

Пример глобальной таблицы:

CREATE TABLE ##GlobalTemp (ID INT);
-- Эта таблица подобна публичной публикации в социальной сети: её видят все, пока вы её не удалите

Локальные временные таблицы ассоциируются с приватными зонами, подобно отдельной комнате, где возможно укрыться от шумного кафе, изолироав данные о #УпотребленииКофе от любопытных взглядов на #ХипстерскиеШляпы. Для таких задач, как импорт большого объема данных, может быть более эффективным использовать табличные переменные или временные переменные таблицы, рассчитывая на улучшение производительности и оптимизацию использования ресурсов.

Визуализация

Схематичное восприятие областей видимости временных таблиц в SQL Server таково:

Локальная временная таблица ( #tableName ): подобна вашему личному офису 🏢
- Она видна только вам (вашей сессии).
- Исчезает, когда вы заканчиваете работу (сессия закрывается).

Глобальная временная таблица ( ##tableName ): подобна объявлению публичного характера 📋
- Она видна всем (доступна для всех сессий).
- Удаляется по окончании работы всех пользователей (последняя сессия закончивается).

Переменная таблицы ( @tableName ): подобна ваши личные запискам 📝
- Видимость ограничена вами (существует только в рамках хранимой процедуры или функции).
- "Убирается" по окончании работы (удаляется в конце вашей операции).

Область и доступность:

Локальная 🔒 | Используется только текущей сессией
Глобальные 🌐 | Доступна всем сессиям
Переменная таблицы 🔏 | Ограничена хранимой процедурой/пакетом/функцией

Таким образом, понимание области видимости имеет не меньшее значение, чем умение писать код! 🌟

Работа с временными таблицами

Создание уникальных имен сессий

Настраивая процесс импорта данных, стоит проявить креативность в создании имен таблиц. Если вы не стремитесь клонировать себя, то дубликаты временных таблиц в вашей сессии также будут лишними.

Пример:

DECLARE @tableName NVARCHAR(128) = '#Import_' + REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), ':', '_') + '_' + CONVERT(NVARCHAR(10), @@SPID);
EXEC('CREATE TABLE ' + @tableName + ' (ID INT)');
-- Создает уникальную временную таблицу специально для вашей сессии

Подробнее об этом расскажет наш спикер на видео

Удаление существующих временных таблиц

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

IF OBJECT_ID('tempdb..#LocalTemp') IS NOT NULL
  DROP TABLE #LocalTemp;
CREATE TABLE #LocalTemp (ID INT);
-- Это подобно владению камнем времени в мире SQL

Изоляция внутри EXEC

Команда EXEC инициирует новую сессию при каждом её вызове. Помните, что после "вечеринки", т.е. выполнения команды, сессия заканчивается, и локальная временная таблица удаляется вместе с ней.

Выбор между переменными таблицами и временными таблицами

Переменные таблицы (@table) могут обладать ограниченными возможностями по сравнению с временными таблицами, однако они имеют свои преимущества:

• Они "легче" в освоении из-за меньшего числа логирования, благодаря чему экономят память.
• Прекрасно подходят для сред с небольшим уровнем конфликтности доступа, когда требуется обработка данных многопользовательским режиме.

Не каждый случай подходит для использования переменных таблиц — при обработке больших объемов данных и операциях, требующих сложного анализа, более предпочтительными будут временные таблицы из-за их возможностей для статистики и индексирования.

Полезные материалы

1. Создание таблицы (Transact-SQL) – SQL Server | Microsoft Learn
2. Временные таблицы в SQL Server – Simple Talk
3. Стандартизированная обработка ошибок SQL Server и централизованное ведение журналов
4. Блог Брента Озара
5. Обсуждения временных таблиц на Stack Overflow