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

SQL-Ex blog

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Переменные SQL в скриптах, функциях, хранимых процедурах, SQLCMD и т.д.
2. Есть ли польза от удаления временной таблицы в хранимой процедуре?
3. Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще
4. Как использовать функциональность массивов в 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;

Обратная связь

Были ли сведения на этой странице полезными?

Подзапросы и временные таблицы

Во всех рассмотренных ранее примерах значения столбцов сравниваются с выражением, константой или набором констант. Кроме таких возможностей сравнения язык Transact-SQL позволяет сравнивать значения столбца с результатом другой инструкции SELECT. Такая конструкция, где предложение WHERE инструкции SELECT содержит одну или больше вложенных инструкций SELECT, называется . Первая инструкция SELECT подзапроса называется внешним запросом (outer query), а внутренняя инструкция (или инструкции) SELECT, используемая в сравнении, называется вложенным запросом (inner query). Первым выполняется вложенный запрос, а его результат передается внешнему запросу. Вложенные запросы также могут содержать инструкции INSERT, UPDATE и DELETE.

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

Независимый подзапрос может применяться со следующими операторами:

• операторами сравнения;
• оператором IN;
• операторами ANY и ALL.

Подзапросы и операторы сравнения

Использование оператора равенства (=) в независимом подзапросе показано в примере ниже:

USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber = (SELECT Number FROM Department WHERE DepartmentName = 'Исследования');

В этом примере происходит выборка имен и фамилий сотрудников отдела ‘Исследования’. Результат выполнения этого запроса:

В примере выше сначала выполняется вложенный запрос, возвращая номер отдела разработки (d1). После выполнения внутреннего запроса подзапрос в примере можно представить следующим эквивалентным запросом:

USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber = 'd1';

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

Подзапросы и оператор IN

Оператор IN позволяет определить набор выражений (или констант), которые затем можно использовать в поисковом запросе. Этот оператор можно использовать в подзапросах при таких же обстоятельствах, т.е. когда вложенный запрос возвращает набор значений. Использование оператора IN в подзапросе показано в примере ниже:

USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE DepartamentNumber IN (SELECT Number FROM Department WHERE DepartmentName = 'Исследования')

Этот запрос аналогичен предыдущему. Каждый вложенный запрос может содержать свои вложенные запросы. Подзапросы такого типа называются подзапросами с многоуровневым вложением. Максимальная глубина вложения (т.е. количество вложенных запросов) зависит от объема памяти, которым компонент Database Engine располагает для каждой инструкции SELECT. В случае подзапросов с многоуровневым вложением система сначала выполняет самый глубокий вложенный запрос и возвращает полученный результат запросу следующего высшего уровня, который в свою очередь возвращает свой результат запросу следующего уровня над ним и т.д. Конечный результат выдается запросом самого высшего уровня.

Запрос с несколькими уровнями вложенности показан в примере ниже:

USE SampleDb; SELECT FirstName, LastName FROM Employee WHERE ID IN (SELECT EmpId FROM Works_on WHERE ProjectNumber IN (SELECT Number FROM Project WHERE ProjectName = 'Apollo') )

В этом примере происходит выборка фамилий всех сотрудников, работающих над проектом Apollo. Самый глубокий вложенный запрос выбирает из таблицы ProjectNumber значение p1. Этот результат передается следующему вышестоящему запросу, который обрабатывает столбец ProjectNumber в таблице Works_on. Результатом этого запроса является набор табельных номеров сотрудников: (10102, 29346, 9031, 28559). Наконец, самый внешний запрос выводит фамилии сотрудников, чьи номера были выбраны предыдущим запросом.

Подзапросы и операторы ANY и ALL

Операторы ANY и ALL всегда используются в комбинации с одним из операторов сравнения. Оба оператора имеют одинаковый синтаксис:

Параметр operator обозначает оператор сравнения, а параметр query — вложенный запрос. Оператор ANY возвращает значение true (истина), если результат соответствующего вложенного запроса содержит хотя бы одну строку, удовлетворяющую условию сравнения. Ключевое слово SOME является синонимом ANY. Использование оператора ANY показано в примере ниже:

USE SampleDb; SELECT DISTINCT EmpId, ProjectNumber, Job FROM Works_on WHERE EnterDate > ANY (SELECT EnterDate FROM Works_on);

В этом примере происходит выборка табельного номера, номера проекта и названия должности для сотрудников, которые не затратили большую часть своего времени при работе над одним из проектов. Каждое значение столбца EnterDate сравнивается со всеми другими значениями этого же столбца. Для всех дат этого столбца, за исключением самой ранней, сравнение возвращает значение true (истина), по крайней мере, один раз. Строка с самой ранней датой не попадает в результирующий набор, поскольку сравнение ее даты со всеми другими датами никогда не возвращает значение true (истина). Иными словами, выражение «EnterDate > ANY (SELECT EnterDate FROM Works_on)» возвращает значение true, если в таблице Works_on имеется любое количество строк (одна или больше), для которых значение столбца EnterDate меньше, чем значение EnterDate текущей строки. Этому условию удовлетворяют все значения столбца EnterDate, за исключением наиболее раннего.

Оператор ALL возвращает значение true, если вложенный запрос возвращает все значения, обрабатываемого им столбца.

Настоятельно рекомендуется избегать использования операторов ANY и ALL. Любой запрос с применением этих операторов можно сформулировать лучшим образом посредством функции EXISTS, которая рассматривается далее в следующей статье. Кроме этого, семантическое значение оператора ANY можно легко принять за семантическое значение оператора ALL и наоборот.

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

— это объект базы данных, который хранится и управляется системой базы данных на временной основе. Временные таблицы могут быть локальными или глобальными. Локальные временные таблицы представлены физически, т.е. они хранятся в системной базе данных tempdb. Имена временных таблиц начинаются с префикса #, например #table_name.

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

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

USE SampleDb; CREATE TABLE #project_temp ( Number NCHAR(4) NOT NULL, Name NCHAR(25) NOT NULL ); -- Аналог предыдущей инструкции со вставкой -- данных во временную таблицу из существующей -- таблицы Project SELECT Number, ProjectName INTO #project_temp FROM Project;

Два этих подхода похожи в том, что в обоих создается локальная временная таблица #project_temp. При этом таблица, созданная инструкцией CREATE TABLE, остается пустой, а созданная инструкцией SELECT заполняется данными из таблицы Project.

Где хранятся временные таблицы 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

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

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

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.

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

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 позволяет создавать обобщенные табличные выражения (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 -- здесь ошибка

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