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

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

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

Временные таблицы существуют на протяжении сессии базы данных. Если такая таблица создается в редакторе запросов (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 -- здесь ошибка

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

SQL-Ex blog

Временные таблицы в MySQL: высокоуровневый обзор

Добавил Sergey Moiseenko on Среда, 19 января. 2022

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

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

Что такое временные таблицы?

В MySQL временная таблица представляет собой специальный тип таблицы, которая (как вы можете догадаться) содержит временные данные. Этот тип таблиц обычно создается автоматически и, как правило, в случае возникновения определенного типа проблем — например, когда выполняются операторы ALTER TABLE на огромных наборах данных.

Скажем, мы выполняем запрос ALTER TABLE для добавления индекса к таблице с 100 миллионами или более записей. MySQL создает временную таблицу (назовем ее temp_table) и копирует туда все данные из исходной таблицы (назовем ее demo_table). Затем воссоздаются данные из исходной таблицы (demo_table) во временной таблице (temp_table), и создаются все индексы, необходимые для demo_table в temp_table, прежде чем поменять их местами. Сбивает с толку? Так не должно быть. Видите ли, MySQL выполняет все эти операции, чтобы добиться максимальной эффективности! Эффективность — часто одна из главных причин, почему администраторы MySQL упоминают временных таблицы в разговоре со своими коллегами-разработчиками, некоторые из которых замечают, что нет единого способа узнать, когда MySQL создаст временные таблицы, что не совсем неверно.

Когда создаются временные таблицы?

1. Выполняются операторы ALTER TABLE для огромных наборов данных (обратитесь к примеру выше).
2. Выполняются операторы UPDATE сразу на нескольких таблицах.
3. Вам потребуются некоторые уникальные (DISTINCT) значения, и потребуется упорядочить их определенным образом.
4. Потребуется подсчитать число уникальных (DISTINCT) значений, существующих в таблице.
5. Если обратиться к документации MySQL, мы увидим, что MySQL использует временные таблицы в некоторых других сценариях.

ALTER TABLE demo_table ADD INDEX demo_idx(demo_column);

UPDATE [LOW_PRIORITY] [IGNORE] demo_table, demo_table2 SET demo_table.demo_column = 'Demo Value', demo_table2.column = 'Demo'

SELECT DISTINCT demo_column ORDER BY id;

SELECT id, COUNT(DISTINCT order) FROM demo_table;

Избежать создания временных таблиц?

Некоторые инженеры MySQL могли бы сказать, что было бы неплохой идеей вообще предотвратить создание временных таблиц. Однако это проще сказать, чем сделать — особенно, если вы запускаете экземпляр базы данных на медленных дисках и (или) с большим количеством данных. Тем не менее, все же имеется пара вещей, которые вы можете сделать. Например, если вы хотите разобраться с этим, то можете использовать диск, назначенный как «RAM-диск», и сказать MySQL, чтобы он помещал туда все свои временные данные. Поскольку объем диска должен быть больше, чем объем имеющейся у вас памяти, операции, как правило, выполняются быстрее. Укажите в этом параметре путь к размещаемому RAM-диску:

Другой способ — использовать только необходимые данные перед выполнением каких-либо операций, требующих использования временных таблиц. Например, если у вас имеется сотня миллионов или более записей, и вы уверены, что не будете использовать некоторые из них (скажем, вы не будете использовать данные из конкретного столбца, но вы не слишком уверены, как пропустить эту операцию, поэтому вы так или иначе загружаете данные в столбец). Тут, вероятно, было бы целесообразно загружать данные только в определенный столбец, а не во все сразу — для этого вы могли бы использовать функцию, предлагаемую LOAD DATA INFILE, и загрузить данные только в один или пару столбцов, например, так:

LOAD DATA INFILE ‘/directory/here/file.txt’ IGNORE INTO TABLE demo_table FIELDS TERMINATED BY ‘:’ (demo_column);

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

1. Создайте новую таблицу с именем demo_table_new.
2. Вручную переместите данные из исходной таблицы в demo_table_new. Для более быстрого и массового импорта вы можете использовать также LOAD DATA INFILE.
3. Удалите demo_table: DROP TABLE demo_table.
4. Переименуйте demo_table_new в demo_table:
   RENAME demo_table_new TO demo_table.

1.2.9. Создание временных таблиц

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

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

Чтобы таблица стала временной локально видимой, перед именем нужно поставить символ #, например, в следующем примере создается временная таблица #TestTable с одним только полем идентификатором, который является первичным ключом:

CREATE TABLE #TestTable ( id INT PRIMARY KEY )

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

Попробуйте создать таблицу. Теперь попробуйте получить все данные из таблицы с помощью запроса:

SELECT * FROM #TestTable

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

Закройте соединение с базой данных или просто перезапустите программу, которую вы используете для отладки запросов. Снова выполните запрос выборки данных из временной таблицы. В ответ должна появиться ошибка: «Invalid object name ‘#TestTable'» (неправильное имя объекта #TestTable).

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

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

CREATE TABLE ##TestTable ( id INT PRIMARY KEY )

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

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

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

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

• таблица интенсивно пополняется или обновляется;
• отчет создается продолжительное время и все это время, обрабатываемые данные должны быть статичными.

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

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

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

Во всех рассмотренных ранее примерах значения столбцов сравниваются с выражением, константой или набором констант. Кроме таких возможностей сравнения язык 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.