Где хранятся временные таблицы sql server

В дополнение к табличным переменным можно определять временные таблицы. Такие таблицы могут быть полезны для хранения табличных данных внутри сложного комплексного скрипта.

Временные таблицы существуют на протяжении сессии базы данных. Если такая таблица создается в редакторе запросов (Query Editor) в SQL Server Management Studio, то таблица будет существовать пока открыт редактор запросов. Таким образом, к временной таблице можно обращаться из разных скриптов внутри редактора запросов.

После создания все временные таблицы сохраняются в таблице tempdb , которая имеется по умолчанию в MS SQL Server.

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

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

Например, создадим локальную временную таблицу:

CREATE TABLE #ProductSummary (ProdId INT IDENTITY, ProdName NVARCHAR(20), Price MONEY) INSERT INTO #ProductSummary VALUES ('Nokia 8', 18000), ('iPhone 8', 56000) SELECT * FROM #ProductSummary

Временные таблицы в T-SQL и MS SQL Server

И с этой таблицей можно работать в большей степени как и с обычной таблицей — получать данные, добавлять, изменять и удалять их. Только после закрытия редактора запросов эта таблица перестанет существовать.

Подобные таблицы удобны для каких-то временных промежуточных данных. Например, пусть у нас есть три таблицы:

CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL ); CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );

Выведем во временную таблицу промежуточные данные из таблицы Orders:

SELECT ProductId, SUM(ProductCount) AS TotalCount, SUM(ProductCount * Price) AS TotalSum INTO #OrdersSummary FROM Orders GROUP BY ProductId SELECT Products.ProductName, #OrdersSummary.TotalCount, #OrdersSummary.TotalSum FROM Products JOIN #OrdersSummary ON Products.Id = #OrdersSummary.ProductId

Здесь вначале извлекаются данные во временную таблицу #OrdersSummary. Причем так как данные в нее извлекаются с помощью выражения SELECT INTO, то предварительно таблицу не надо создавать. И эта таблица будет содержать id товара, общее количество проданного товара и на какую сумму был продан товар.

Затем эта таблица может использоваться в выражениях INNER JOIN.

Temporary tables in T-SQL and MS SQL Server

Подобным образом определяются глобальные временные таблицы, единственное, что их имя начинается с двух знаков ##:

CREATE TABLE ##OrderDetails (ProductId INT, TotalCount INT, TotalSum MONEY) INSERT INTO ##OrderDetails SELECT ProductId, SUM(ProductCount), SUM(ProductCount * Price) FROM Orders GROUP BY ProductId SELECT * FROM ##OrderDetails

Глобальные временные таблицы в MS SQL Server

Обобщенные табличные выражения

Кроме временных таблиц MS SQL Server позволяет создавать обобщенные табличные выражения (common table expression или CTE), которые являются производными от обычного запроса и в плане производительности являются более эффективным решением, чем временные. Обобщенное табличное выражение задается с помощью ключевого слова WITH :

WITH OrdersInfo AS ( SELECT ProductId, SUM(ProductCount) AS TotalCount, SUM(ProductCount * Price) AS TotalSum FROM Orders GROUP BY ProductId ) SELECT * FROM OrdersInfo -- здесь нормально SELECT * FROM OrdersInfo -- здесь ошибка SELECT * FROM OrdersInfo -- здесь ошибка

Обобщенные табличные выражения CTE в MS SQL Server

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

База данных tempdb в параллельном хранилище данных

tempdb — это системная база данных SQL Server PDW, в которой хранятся локальные временные таблицы для пользовательских баз данных. Временные таблицы часто используются для повышения производительности запросов. Например, можно использовать временную таблицу для модульизации скрипта и повторного использования вычисляемых данных.

Дополнительные сведения о системных базах данных см. в разделе «Системные базы данных».

Ключевые термины и понятия

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

Каждый сеанс может просматривать метаданные для локальных временных таблиц во всех сеансах. Например, все сеансы могут просматривать метаданные для всех локальных временных таблиц с запросом SELECT * FROM tempdb.sys.tables .

глобальная временная таблица
Глобальные временные таблицы, поддерживаемые в SQL Server с синтаксисом ##, не поддерживаются в этом выпуске SQL Server PDW.

pdwtempdb
pdwtempdb — это база данных, в которой хранятся локальные временные таблицы.

PDW не реализует временные таблицы с помощью базы данных tempdb SQL Server. Вместо этого PDW сохраняет их в базе данных с именем pdwtempdb. Эта база данных существует на каждом вычислительном узле и невидима для пользователя через интерфейсы PDW. В консоли Администратор на странице хранилища вы увидите эти учетные записи в системной базе данных PDW с именем tempdb-sql.

tempdb
tempdb — это база данных tempdb SQL Server. В нем используется минимальное ведение журнала. SQL Server использует tempdb на вычислительных узлах для хранения временных таблиц, необходимых в ходе выполнения операций SQL Server.

SQL Server PDW удаляет таблицы из tempdb , когда:

Выполняется инструкция DROP TABLE.
Сеанс отключен. Удаляются только временные таблицы для сеанса.
(модуль) завершает работу.
Узел управления имеет отработку отказа кластера.

Общие замечания

SQL Server PDW выполняет те же операции с временными таблицами и постоянными таблицами, если явно не указано в противном случае. Например, данные в локальных временных таблицах, как и постоянные таблицы, распределяются или реплика между вычислительными узлами.

Ограничения

Ограничения и ограничения базы данных tempdb SQL Server PDW. Невозможно :

Создайте глобальную временную таблицу, начинающуюся с ##.
Выполните резервное копирование или восстановление tempdb.
Измените разрешения на tempdb с помощью инструкций GRANT, DENY или REVOKE .
Выполните DBCC SHRINKLOG для tempdb tempdb.
Выполнение операций DDL в tempdb. Существует несколько исключений для этого. Дополнительные сведения см. в следующем списке ограничений и ограничений для локальных временных таблиц.

Ограничения и ограничения для локальных временных таблиц. Невозможно :

Переименование временной таблицы
Создание секций, представлений или некластеризованных индексов во временной таблице. ALTER INDEX можно использовать для перестроения кластеризованного индекса для таблицы, созданной с помощью одной.
Измените разрешения на временные таблицы с помощью инструкций GRANT, DENY или REVOKE.
Запустите команды консоли базы данных во временных таблицах.
Используйте одно и то же имя для двух или нескольких временных таблиц в одном пакете. Если в пакете используется несколько локальных временных таблиц, они должны иметь уникальные имена. Если несколько сеансов выполняют один пакет и создают одну и ту же локальную временную таблицу, SQL Server PDW внутренне добавляет числовой суффикс к имени локальной временной таблицы, чтобы сохранить уникальное имя для каждой локальной временной таблицы.

Вы можете создавать и обновлять статистику во временной таблице.ALTER INDEX можно использовать для перестроения кластеризованного индекса.

Разрешения

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

Связанные задачи

Задачи	Description
Создайте таблицу в tempdb.	Можно создать временную таблицу пользователя с помощью инструкций CREATE TABLE и CREATE TABLE AS SELECT. Дополнительные сведения см. в статье CREATE TABLE and CREATE TABLE AS SELECT.
Просмотрите список существующих таблиц в tempdb.	SELECT * FROM tempdb.sys.tables;
Просмотрите список существующих столбцов в tempdb.	SELECT * FROM tempdb.sys.columns;
Просмотрите список существующих объектов в tempdb.	SELECT * FROM tempdb.sys.objects;

Связанный контент

Отличие способов хранения результирующих данных в T-SQL

Временные таблицы бывают двух видов. Таблицы переменные(@Table), временные таблицы(#table), ещё есть таблицы вида ##table, отличаются от #table областью видимости.

В связи с этим, можно дать краткое описание:

Таблицы переменные — хранятся в оперативной памяти(если её хватает). Доступна в блоке кода, т.е. её можно переиспользовать в разных запросах. имеет локальную область видимости, так же как любая другая локальная переменная.
Временные таблицы. Хранятся в tempdb, имеют более широкую область видимости, а именно по всему стеку вызовов. Т.е. если процедура А создала таблицу #A, потом вызвала процедуру B, которая создала таблицу #B — то и А имеет доступ к #B(после вызова В) и В имеет доступ к #A. Так же на временные таблицы можно создавать индексы, триггеры и прочее, в отличии от таблиц переменных. Таблицы ##Table имеют глобальную область видимости. Если кто-то создал таблицу ##Table — её видят все сессии, а существует она до тех пор, пока «жива» хоть одна сессия, которая обращалась к этой таблице.
СТЕ. Тут область вилимости только внутри одного запроса! Т.е. переиспользовать результат нельзя. Более того, если вы обращаетесь к СТЕ несколько раз внутри одного апроса — она будет вычислена столько же раз! Есть недокументированные способы заставить оптимизатор запомнить СТЕ в оперативной памяти для повторного использования, но это совсем другая история:)
Курсоры. В общем это немного из другой оперы. Курсоры позволяют построчно обрабатывать данные и предназначены не для хранения. Внутри курсора можно вызывать выполнение процедур, чего нельзя делать в запросе.

Добавлю ещё своё субъективное мнение когда что нужно использовать.

Таблицы переменные. Когда нужно использовать небольшое количество данных. Например промежуточный результат сложного запроса записать в таблицу переменную, разбив тем самым сложный запрос на два простых.
Временные таблицы. Когда информации довольно много и/или её нужно передать в другое место выполнения. Эти таблицы ничем не отличаются от обычных таблиц, кроме того, что не нужно беспокоиться о их очищении и удалении.
СТЕ — когда нельзя использовать временные таблицы(т.е. такие места, которые обязывают нас использовать только один SQL запрос), Например, внутри тела табличной функции.
Курсоры — когда нельзя обойтись другими способами. Например, когда для каждой строки временного результата нужно запустить выполнение хранимой процедуры. В MS SQL курсоры обычно работают медленнее запросов. Так что елси есть возможность — лучше их избегать.

SQL-Ex blog

Что использовать — табличную переменную или временную таблицу?

Добавил Sergey Moiseenko on Суббота, 26 августа. 2023

При работе с SQL Server нет ничего необычного в необходимости сохранять данные во временной таблице или табличной переменной. Хотя оба варианта могут использоваться для достижения одной и той же цели, они по-разному могут влиять на производительность и возможность написания эффективного кода. Давайте исследуем различия между табличными переменными и временными таблицами, и когда предпочтительно использовать ту или иную.

@Табличные переменные

Табличные переменные объявляются с использованием символа «@» и создаются в памяти. Они подобны обычным переменным в том, что их область действия ограничена пакетом или хранимой процедурой, в которой они объявлены, и их значениями можно манипулировать с помощью стандартных операторов SQL. Табличные переменные часто используются для хранения наборов данных небольших или средних размеров.

Преимущества табличных переменных:

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

Ограничения табличных переменных:

Табличные переменные не индексируются, а это значит, что они могут быть медленнее при запросах, чем временные таблицы.
Табличные переменные не могут использоваться для создания статистики, что может затруднить оптимизатору запросов строить эффективные планы выполнения.
Табличные переменные имеют фиксированное кардинальное число, что не позволяет SQL Server точно оценить число строк, которое в них содержится.

#Временные таблицы

Временные таблицы создаются с использованием символа «#» и сохраняются на диске. Доступ к ним возможен из любой сессии, которая имеет соответствующие разрешения, и они полезны для хранения больших наборов данных или данных, которые требуется сохранять для нескольких пакетов или сессий. Временные таблицы могут создаваться на уровне сессии, подключения или на глобальном уровне.

Преимущества временных таблиц:

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

Ограничения временных таблиц:

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

Когда использовать табличные переменные, а когда временные таблицы

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

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

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

Переменные SQL в скриптах, функциях, хранимых процедурах, SQLCMD и т.д.
Есть ли польза от удаления временной таблицы в хранимой процедуре?
Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще
Как использовать функциональность массивов в SQL Server?

Где хранятся временные таблицы sql server