Sql создать временную таблицу: MS SQL Server и T-SQL

Не удаляйте временные таблицы, умоляю / Хабр

Мне часто приходится видеть чужой код на T-SQL. Я уже привык видеть в конце процедур привычное

drop table #a
drop table #b

Таблица сама себя не удалит, видимо думает автор. Ну или это делает «на всякий случай». Вдруг 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 называются по-разному, то все будет хорошо. Все будет хорошо до первого случайного пересечения имен.

Временные таблицы в функциях

← →
NewDelpher ©
 
(2004-10-04 15:35)
[0]

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

← →
Fay ©
 
(2004-10-04 15:39)
[1]

>> MS SQL не разрешает создавать временные таблицы в хранимых функциях
Это очень спорное утверждение. Откуда такая информация? Вы сами проверяли? Думаю, нет.

← →
Ega23 ©
 
(2004-10-04 15:41)
[2]

Она тебе набор данных возвратит.

1. Можешь временные таблицы ##ttt типа использовать — они «живут» пока сервер не перезапустят
2. Можешь временные таблицы #ttt типа использовать, если всё в рамках одной транзакции.

← →
NewDelpher ©
 
(2004-10-04 15:50)
[3]

в функции пишу:

SELECT number INTO #tmp_numbs
FROM mytable WHERE status= 1 and datenumb = @date_numb

при проверке синтаксиса
выдает:
Cannot accsess temporary tables from within a function

← →
Polevi ©
 
(2004-10-04 15:50)
[4]

>MS SQL не разрешает создавать временные таблицы в хранимых функциях

не разрешаются DDL выражения,
заменяй CREATE TABLE #T(…) на DECLARE @T TABLE (..)

← →
NewDelpher ©
 
(2004-10-04 15:58)
[5]

> не разрешаются DDL выражения,
> заменяй CREATE TABLE #T(. ..) на DECLARE @T TABLE (..)

тогда так мой запрос будет выглядеть:

SELECT number INTO @tmp_numbs
FROM mytable WHERE status= 1 and datenumb = @date_numb

← →
Fay ©
 
(2004-10-04 16:03)
[6]

Почему бы не почитать BOL?

← →
NewDelpher ©
 
(2004-10-04 16:08)
[7]

> Fay ©   (04.10.04 16:03) [6]
> Почему бы не почитать BOL?

Почитал, только не понял, их удалять получается не надо при выходе из функции?

← →
Nikolay M. ©
 
(2004-10-04 16:14)
[8]

> Почитал, только не понял, их удалять получается не надо
> при выходе из функции?

@T TABLE (..) — это не таблица, а переменные табличного типа, поэтому удалять их не нужно.

← →
NewDelpher ©
 
(2004-10-04 16:17)
[9]

спасибо, разобрался

← →
Polevi ©
 
(2004-10-04 16:24)
[10]

>Nikolay M. ©   (04.10.04 16:14) [8]
нет таблица 🙂

← →
Nikolay M. ©
 
(2004-10-04 17:25)
[11]

> Polevi ©   (04.10.04 16:24) [10]

Аргументы? DECLARE @T TABLE (…) просто по конструкции декларирует переменную и в БОЛ о DECLARE TABLE написано:

Defines the table data type. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, NULL, and CHECK.

← →
Polevi ©
 
(2004-10-05 11:27)
[12]

внутрях все равно это временная таблица

DECLARE @T TABLE (ID INT PRIMARY KEY CLUSTERED)
INSERT @T SELECT 1
INSERT @T SELECT 1

← →
Nikolay M.  ©
 
(2004-10-05 11:36)
[13]

> Polevi ©   (05.10.04 11:27) [12]

Где конкретно внутрях? В оперативке? В темпдб не увидел.

CREATE TEMPORARY TABLE AS (CTTAS)

Появилось в версии: 1.10
Вы можете использовать команду CREATE TEMPORARY TABLE AS (CTTAS) для сохранения результатов запроса во временной таблице. Вы можете ссылаться на временную таблицу в последующих запросах в рамках того же сеанса, тем самым повышая производительность запросов. Данные, записываемые во временную таблицу, не сохраняются в файловой системе постоянно. Drill автоматически удаляет временную таблицу после завершения сеанса или сбоя процесса Drillbit. Таким образом, вам не нужно вручную удалять таблицу.

Примечание

Вы не можете создать представление для временной таблицы.

Синтаксис

 CREATE TEMPORARY TABLE имя [ (список столбцов) ][ PARTITION BY (список столбцов) ] AS запрос
 

Параметры

имя — Уникальное имя каталога.

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

PARTITION BY — необязательный параметр, который можно использовать только для создания временных таблиц с форматом данных Parquet. При использовании он должен указывать столбец или список столбцов, которые должны быть разделены.

запрос — инструкция SELECT, которая должна включать псевдонимы для неоднозначных имен столбцов, таких как COLUMNS[0]. Использование SELECT * не рекомендуется при выборе данных CSV, TSV и PSV.

Замечания по использованию

Рабочая область для временных таблиц

По умолчанию Drill создает временные таблицы во временной рабочей области по умолчанию, dfs. tmp . Временная рабочая область по умолчанию должна быть доступной для записи, файловой и указывать на уже существующее расположение, иначе создание временной таблицы завершится ошибкой. Вы не можете создать временную таблицу за пределами временной рабочей области по умолчанию.

Пример рабочего пространства dfs.tmp в подключаемом модуле хранилища dfs:

 {
"тип": "файл",
"включено": правда,
"соединение": "maprfs:///",
"конфигурация": ноль,
"рабочие места": {
"корень": {
"расположение": "/",
"доступно для записи": ложь,
"DefaultInputFormat": ноль
},
"тмп": {
"местоположение": "/tmp",
"доступный для записи": правда,
"DefaultInputFormat": ноль
} ... Чтобы переопределить временную рабочую область по умолчанию, определите `drill.exec.default_temporary_workspace` в `drill-override.conf файле`, а затем перезапустите Drillbit. Дополнительные сведения см. в разделе [Введение в параметры конфигурации](/docs/configuration-options-introduction/#system-options).
 

Чтобы изменить подключение к временной рабочей области по умолчанию или путь к каталогу, обновите подключаемый модуль хранилища dfs на странице «Хранилище» в веб-интерфейсе Drill. Например, атрибут подключения dfs может иметь вид file:/// или maprfs:/// , а атрибут местоположения dfs.tmp может иметь вид /tmp или /tmp2 . Дополнительные сведения см. в разделе Основы настройки подключаемых модулей.

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

Настройка формата хранения

Формат хранения временных таблиц по умолчанию — паркет. Однако вы можете создавать временные таблицы в одном из следующих форматов:

• csv, tsv, psv
• паркет
• json

Чтобы изменить формат хранения, установите параметр store.format перед созданием временной таблицы. Например, вы можете использовать команду ALTER SESSION SET, чтобы установить для параметра store. format значение JSON:

 ALTER SESSION SET `store.format`='json';
 

Создание временных таблиц и доступ пользователей

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

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

Drill создает каталоги и файлы сеанса и временной таблицы со следующими разрешениями:

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

Авторизация

Когда авторизация включена, пользователь, запустивший Drillbit, будет владеть временными таблицами, созданными в рамках этого сеанса. Однако пользователь, создавший таблицу, сможет запрашивать данные. Например, mapr пользователь запускает Drillbit, а Sally запускает оболочку Drill. Когда авторизация включена, Салли может отправить запрос на создание временной таблицы и запросить данные временной таблицы. Однако пользователь mapr является владельцем каталога и файлов временной таблицы. В этом случае Том может получить доступ к временной таблице, если ему известен полный путь к каталогу временной таблицы и сессия все еще активна.

Олицетворение

Если олицетворение включено, только пользователь, создавший временную таблицу, может запрашивать временную таблицу. Например, если Drillbit был запущен пользователем mapr , а пользователь Tom запускает оболочку Drill и создает временную таблицу, Tom будет иметь разрешение на запрос временной таблицы. Том также может получить доступ к временным таблицам, созданным им в другом сеансе, если у него есть полный путь к каталогу временных таблиц и сеанс все еще активен. Однако Салли не может запросить временную таблицу, даже если у нее есть полный путь к каталогу временной таблицы и сеанс активен.

Выбор таблиц

До Drill 1.21, когда вы упоминали имя таблицы в операторе SELECT, любая временная таблица с таким именем имела приоритет над таблицей с таким же именем в текущей рабочей области. Начиная с Drill 1.21, временные таблицы ведут себя так же, как обычные имена таблиц.

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

 ИСПОЛЬЗОВАТЬ dfs.json;
ВЫБЕРИТЕ * ИЗ пончиков; // возвращает таблицу из dfs.json
ВЫБЕРИТЕ* ИЗ dfs.tmp.donuts; //возвращает временную таблицу из dfs.tmp
 

Удаление временной таблицы

После завершения сеанса или сбоя процесса бурения Drillbit удалит временное местоположение сеанса, которое включает все связанные каталоги и файлы временных таблиц. Однако вы также можете удалить временную таблицу с помощью команды DROP TABLE. Когда вы удаляете временную таблицу во время сеанса, Drill удаляет каталог временной таблицы и ее файлы.

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

В следующем примере cust_promotions — это имя постоянной таблицы в файле dfs.json, а также имя временной таблицы.

 ИСПОЛЬЗОВАТЬ dfs.json;
УДАЛИТЬ ТАБЛИЦУ cust_promotions;
|---------------------|------------------------------------------------------- ------------|
| хорошо | резюме |
|---------------------|------------------------------------------------------- ------------|
| правда | Временная таблица [cust_promotions] удалена |
|---------------------|------------------------------------------------------- ------------|
 

Несмотря на то, что рабочая область была установлена ​​на dfs. json, временная таблица из dfs.tmp была удалена.

Пример CTTAS

В следующем примере пользователь имеет файл .csv со следующими данными:

 "1","Боб","200","ложь"
"2", "Кейт", "150", "правда"
"3", "Тим", "20", "ложь"
«4», «Роджер», «500», «правда»
 

Первый столбец — идентификатор клиента, второй столбец — имя покупателя, третий столбец — сумма продажи, а четвертый столбец указывает, получил ли покупатель скидку:

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

 CREATE TEMPORARY TABLE total_amount_by_discount as SELECT cast(columns[3] as boolean ) Discount, sum(cast(columns[2] как double)) total_amount FROM dfs.`/emp/amount.csv` GROUP BY columns[3];

|----------|----------------------------|
| Фрагмент | Количество записанных записей |
|----------|----------------------------|
| 0_0 | 2 |
|----------|----------------------------|
 

Временная таблица total_amount_by_discount создана во временной рабочей области по умолчанию с форматом хранения по умолчанию.

Чтобы просмотреть временную таблицу, пользователь может выполнить оператор SELECT для всей таблицы:

 select * from total_amount_by_discount;
|----------|---------------|
| скидка | общая_сумма |
|----------|---------------|
| ложный | 220,0 |
| правда | 650,0 |
|----------|---------------|
 

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

← CREATE TABLE AS (CTAS)CREATE FUNCTION USING JAR →

Временная таблица в SQL — темы масштабирования

Обзор

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

Scope

В этом руководстве мы рассмотрим следующие темы:

1. Сначала мы обсудим основы временных таблиц и создание, выбор и вставку значений во временную таблицу.
2. Затем с помощью примеров разберемся с типами временных таблиц (т.е. локальные и глобальные временные таблицы) .
3. Наконец-то мы увидим различные методы удаления временной таблицы в SQL.

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

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

Для создания временной таблицы:

Для вставки значений во временную таблицу:

Для выбора значений из временной таблицы:

Вывод: 9 0004

Пример
Вот пример использования временной таблицы.

Вывод:

Примечание: При выполнении команды SHOW TABLES ваша временная таблица не появится в списке. Если вы выйдете из сеанса MySQL, а затем отправите команду SELECT, вы обнаружите, что в базе данных нет данных. Даже ваша временная таблица исчезнет.

Локальные временные таблицы

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

Например, , если мы создадим локальную временную таблицу с именем #my_table, пользователь сможет выполнять только функции в таблице до тех пор, пока не будет прервано последнее подключение к окну запроса. Они идентифицируются по префиксу #9.0004 , например, #имя таблицы , и одна и та же временная таблица может быть установлена ​​в нескольких окнах с одним и тем же именем.

Команда CREATE TABLE используется для создания локальной временной таблицы с именем таблицы, которому предшествует один знак числа (#имя таблицы) .

Синтаксис:

В SQL Server (Transact-SQL) синтаксис для создания ЛОКАЛЬНОЙ ВРЕМЕННОЙ ТАБЛИЦЫ :

Параметры или аргументы

Пример:

Давайте посмотрим, как создать ЛОКАЛЬНУЮ ВРЕМЕННУЮ ТАБЛИЦУ в SQL Server (Transact-SQL).

В этом примере создается ЛОКАЛЬНАЯ ВРЕМЕННАЯ ТАБЛИЦА в SQL Server с именем #employees с четырьмя столбцами.

Столбец employee_id является основным ключом для таблицы #employees.

Эта таблица #employees хранится в базе данных tempDB, и SQL Server уничтожит ее, когда сеанс SQL Server больше не нужен.

Примечание: Имена ЛОКАЛЬНЫХ ВРЕМЕННЫХ ТАБЛИЦ начинаются с символа # (например, #employees) .

Глобальные временные таблицы

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

Примечание: Они обозначаются префиксом ##, как в имени таблицы ##.

Команда CREATE TABLE создает глобальную временную таблицу, перед именем которой стоит двойной знак числа (##имя таблицы) .

Синтаксис:

В SQL Server (Transact-SQL) синтаксис для создания ГЛОБАЛЬНОЙ ВРЕМЕННОЙ ТАБЛИЦЫ :

Параметры или аргументы

Параметры	Описание
имя_таблицы	Это имя глобальной временной таблицы, которую вы хотите создать. Имя глобальной временной таблицы начинается с двойного символа ## .
столбец_1, столбец_2, столбец_3… столбец_n	Столбцы, которые вы хотите добавить в глобальную временную таблицу. Каждому столбцу должен быть назначен тип данных. Столбец должен быть объявлен либо как NULL, либо как NOT NULL , и если это значение оставить пустым, база данных примет значение NULL по умолчанию.

Пример:

Давайте посмотрим, как создать ГЛОБАЛЬНУЮ ВРЕМЕННУЮ ТАБЛИЦУ в SQL Server (Transact-SQL). В этом примере будет создана ГЛОБАЛЬНАЯ ВРЕМЕННАЯ ТАБЛИЦА в SQL Server с именем ##suppliers_temp с тремя столбцами.

Имя столбца	Описание
supplier_id	Первый столбец называется supplier_id, и он числовой тип данных с максимальной длиной 10 символов, которые не могут иметь значений NULL.
имя_поставщика	Второй столбец, имя поставщика, представляет собой тип данных Char с максимальной длиной 50 символов и не может содержать значения NULL.
contact_name	Третий столбец, contact name, представляет собой тип данных Char, который может состоять из значений NULL.

Следующий запрос используется для создания этой таблицы:

Удаление временных таблиц

Использование функции OBJECT_ID для проверки существования временной таблицы

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

Следующий запрос проверит наличие таблицы #LocalCustomer в базе данных tempdb и , если она существует, она будет удалена .

Для локальных временных таблиц:

Для глобальных временных таблиц:

Использование sys. table Таблица для проверки существования временной таблицы

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

Для локальных временных таблиц:

Для глобальных временных таблиц:

Как мы видим, мы проверяем, существует ли таблица #LocalCustomer в базе данных tempDB, и если она существует, мы должны ее удалить. На данный момент мы должны подчеркнуть один момент: имя таблицы ищется с помощью оператора LIKE , а в конце имени временной таблицы помещен подстановочный знак. Как указывалось ранее, локальные временные таблицы формируются со случайным суффиксом , поэтому фактическое имя неизвестно.

Использование оператора DROP TABLE IF EXISTS

Это последний способ удаления временной таблицы. MySQL удаляет все временные таблицы по умолчанию при разрыве соединения с базой данных. Тем не менее, если вы хотите удалить их тем временем, используйте оператор DROP TABLE IF EXISTS . Оператор DROP TABLE IF EXISTS проверяет существование таблицы и удаляет ее, если она существует. Мы должны подчеркнуть один аспект этого утверждения : оно применимо только к SQL Server 2016 или более поздней версии.

Для локальных временных таблиц:

Для глобальных временных таблиц:

Ознакомьтесь с этой статьей, чтобы узнать больше о SQL Server.

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