Не удаляйте временные таблицы, умоляю
Таблица сама себя не удалит, видимо думает автор. Ну или это делает «на всякий случай». Вдруг SQL server забудет ее удалить? Впрочем, эти удаления не столь страшны, так как SQL server их оптимизирует (не делает перекомпиляции, о чем мы поговорим дальше, так как удаление происходит строго в конце). Кроме того, если отладка тела процедуры производится скриптом, то удаления нужны, и когда в самом конце этот скрипт оборачивают в процедуру, то удаления остаются.
Но недавно я стал встречать совершенно жуткий антипаттерн. Не знаю, откуда он распространился.
if object_id('tempdb..#mytemp') is not null DROP TABLE #mytemp create table #mytemp (. )
Мне обидно, что SQL server считают идиотом со стекающими от вырождения слюнями, идиотом, неспособным заботиться о контексте выполнения. Но важнее то, что это код — потенциальная бомба с часовым механизмом.
Покажем это на примере. Создадим внешнюю процедуру:
create procedure ALPHA as create table #mytemp (n int, ALPHA varchar(128)) insert into #mytemp select 1, 'ALPHA' select 1 as point, * from #mytemp exec BETA select 2 as point, * from #mytemp GOКак вы видите, этот код вызывает внутреннюю процедуру BETA:
create procedure BETA as create table #mytemp (n int, BETA varchar(128)) insert into #mytemp select 1, 'BETA' select 3 as point, * from #mytemp GO Выполним процедуру ALPHA и увидим, что каждая процедура видит объекты в своей области видимости. Временные таблицы также доступны, если они не экранированы созданными локально:
две таблицы сосуществуют вместе, в чем можно убедиться добавив оператор — ***
create procedure BETA as create table #mytemp (n int, BETA varchar(128)) insert into #mytemp select 1, 'BETA' select 3 as point, * from #mytemp select * from tempdb.dbo.sysobjects where name like '%mytemp%' -- *** GO
Вот они, две наши таблички мирно сосуществуют. Мы можем усложнить задачу SQL так:
Я привел скриншот, чтобы обратить внимание на то, что редактор подозревает, что тут ошибка: таблица #mytemp используется после удаления. Но мы знаем, что делаем:
В 3-й отладочной печати выводится локальная таблица, а в 4-й — внешняя, из ALPHA. После drop SQL server вынужден перекомпилировать хвост процедуры, потому что у другой таблицы могут быть другие поля, как в данном случае.
Теперь вас не должно удивить, что произойдет при использовании антипаттерна:
create procedure BETA as if object_id('tempdb..#mytemp') is not null DROP TABLE #mytemp create table #mytemp (n int, BETA varchar(128)) insert into #mytemp select 1, 'BETA' select 3 as point, * from #mytemp GOПроверьте себя
Так как проверка и удаление таблицы производится вначале, то удаляется внешняя таблица, созданная процедурой ALPHA. Дальше все идет хорошо до момента, пока ALPHA не пытается прочитать из уже несуществующей таблицы.
Если временные таблицы в процедурах ALPHA и BETA называются по-разному, то все будет хорошо. Все будет хорошо до первого случайного пересечения имен.
Где хранятся временные таблицы 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 -- здесь ошибка
В отличие от временных таблиц табличные выполнения хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет это табличное выражение.
Отличие способов хранения результирующих данных в T-SQL
Временные таблицы бывают двух видов. Таблицы переменные(@Table), временные таблицы(#table), ещё есть таблицы вида ##table, отличаются от #table областью видимости.
В связи с этим, можно дать краткое описание:
- Таблицы переменные — хранятся в оперативной памяти(если её хватает). Доступна в блоке кода, т.е. её можно переиспользовать в разных запросах. имеет локальную область видимости, так же как любая другая локальная переменная.
- Временные таблицы. Хранятся в tempdb, имеют более широкую область видимости, а именно по всему стеку вызовов. Т.е. если процедура А создала таблицу #A, потом вызвала процедуру B, которая создала таблицу #B — то и А имеет доступ к #B(после вызова В) и В имеет доступ к #A. Так же на временные таблицы можно создавать индексы, триггеры и прочее, в отличии от таблиц переменных. Таблицы ##Table имеют глобальную область видимости. Если кто-то создал таблицу ##Table — её видят все сессии, а существует она до тех пор, пока «жива» хоть одна сессия, которая обращалась к этой таблице.
- СТЕ. Тут область вилимости только внутри одного запроса! Т.е. переиспользовать результат нельзя. Более того, если вы обращаетесь к СТЕ несколько раз внутри одного апроса — она будет вычислена столько же раз! Есть недокументированные способы заставить оптимизатор запомнить СТЕ в оперативной памяти для повторного использования, но это совсем другая история:)
- Курсоры. В общем это немного из другой оперы. Курсоры позволяют построчно обрабатывать данные и предназначены не для хранения. Внутри курсора можно вызывать выполнение процедур, чего нельзя делать в запросе.
Добавлю ещё своё субъективное мнение когда что нужно использовать.
- Таблицы переменные. Когда нужно использовать небольшое количество данных. Например промежуточный результат сложного запроса записать в таблицу переменную, разбив тем самым сложный запрос на два простых.
- Временные таблицы. Когда информации довольно много и/или её нужно передать в другое место выполнения. Эти таблицы ничем не отличаются от обычных таблиц, кроме того, что не нужно беспокоиться о их очищении и удалении.
- СТЕ — когда нельзя использовать временные таблицы(т.е. такие места, которые обязывают нас использовать только один SQL запрос), Например, внутри тела табличной функции.
- Курсоры — когда нельзя обойтись другими способами. Например, когда для каждой строки временного результата нужно запустить выполнение хранимой процедуры. В MS 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.