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

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

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

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

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

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

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

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

1 2 3 4 5 6 7 8 9 10

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

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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:

1 2 3 4 5 6 7 8 9 10

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.

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

1 2 3 4 5 6 7 8 9

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 позволяет создавать производные таблицы, которые в плане производительности являются более эффективным решением, чем временные. Производная таблица задается с помощью ключевого слова WITH:

1 2 3 4 5 6 7 8 9 10 11 12

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 -- здесь ошибка

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