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

Временные локальные и глобальные таблицы

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

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

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