Переменные таблицы – это часто используемая функция в SQL Server, которая часто считается объектами в памяти, не использующими ничего из TempDB. Однако существуют некоторые недоразумения относительно переменных таблиц, которые нужно прояснить.
Одним из часто возникающих вопросов является то, создаются ли переменные таблицы в виде объектов, хранящихся в TempDB, и записываются ли они в журнал транзакций. Чтобы развеять это сомнение, давайте пройдем пошаговый процесс.
Сначала давайте очистим журнал транзакций TempDB:
USE tempdb GO CHECKPOINT GO
Затем мы создадим переменную таблицу и проверим количество добавленных строк в журнале транзакций TempDB:
DECLARE @TblVariable TABLE (
id INT NOT NULL IDENTITY (1, 1),
Name CHAR(30) NOT NULL,
DOJ DATE NOT NULL,
Company CHAR(400) NULL DEFAULT 'SQLAuth'
)
SELECT COUNT(*) [RowNums] FROM sys.fn_dblog(NULL, NULL)
GO
Этот простой эксперимент подтверждает, что что-то записывается в журнал транзакций при создании переменной таблицы. Мы также можем наблюдать, как добавляются дополнительные строки в журнал при вставке строк в переменную таблицу.
Теперь давайте попробуем найти ссылку на таблицу с помощью sysobjects:
SELECT * FROM sysobjects WHERE TYPE = 'U'
В этом случае мы не увидим никаких возвращенных строк, потому что переменные таблицы не хранятся как пользовательские таблицы в системной таблице sysobjects.
Однако мы все равно можем получить доступ к данным переменной таблицы, изучая метаданные таблиц. Чтобы продемонстрировать это, рассмотрим следующий скрипт:
DECLARE @TblVariable TABLE (
id INT NOT NULL IDENTITY (1, 1),
Name CHAR(30) NOT NULL,
DOJ DATE NOT NULL,
Company CHAR(400) NULL DEFAULT 'SQLAuth'
)
INSERT @TblVariable (Name, DOJ) SELECT 'SQLAuth', GETDATE()
SELECT * FROM @TblVariable
WAITFOR DELAY '00:02:00'
В этом скрипте мы вводим задержку в 2 минуты, чтобы предотвратить немедленное завершение пакета. В результате переменная таблица не уничтожается немедленно, что позволяет нам запросить таблицу sysobjects и наблюдать существование переменной таблицы.
Кроме того, мы можем изучить данные, хранящиеся в переменной таблице, получив доступ к выделенной странице. Вот пример:
SELECT allocated_page_file_id, allocated_page_page_id, *
FROM sys.dm_db_database_page_allocations(2, NULL, 1, NULL, 'DETAILED')
WHERE OBJECT_ID NOT IN (SELECT ID FROM sysobjects WHERE TYPE IN ('S', 'IT', 'SQ'))
AND page_type = 1
AND OBJECT_ID = -1098578155
Результат этого запроса показывает выделенную страницу для данных в переменной таблице. Чтобы дальше анализировать данные, мы можем использовать команду DBCC PAGE:
DBCC TRACEON(3604) GO DBCC PAGE(2, 1, 306, 3) GO
Это подтверждает, что переменные таблицы действительно записываются в TempDB и могут быть просмотрены и отлажены во время их существования, как любая обычная таблица.
Понимание поведения переменных таблиц в SQL Server может помочь оптимизировать их использование и избежать неожиданного потребления ресурсов в TempDB. Если у вас когда-либо была возможность просматривать и отлаживать переменные таблицы подобным образом, не стесняйтесь делиться своими впечатлениями.