Create table t sql: Инструкция CREATE TABLE (Transact-SQL) — SQL Server

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

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

Инструкция CREATE TABLE (Transact-SQL)

Default – задает значение по умолчанию для входных параметров.

OUTPUT – ключевое слово,  которое определяет,  что данный параметр является выходным. Параметр, описанный этим словом возвращает информацию при  выполнении хранимой процедуры (выполнении оператора execute).

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE – ключевое слово, которое заставляет  SQL Server каждый раз при вызове данной хранимой процедуры на исполнение заново ее перекомпилировать и строить новый план исполнения.

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

  Надо помнить, что при восстановлении БД после сбоев требуется заново компилировать тексты хранимых процедур.

FOR REPLICATION – ключевое слово, которое определяет, что данная процедура будет использована при репликации. Существует специальный режим репликации, которые позволяет не пересылать данные по сети, а послать процедуру, которая в результате своего выполнения сгенерирует новые данные.  Этот режим не может быть использован совместно с опцией RECOMPILE (перекомпиляции).

AS – ключевое слово, после которого идет собственно текст процедуры.

Создание триггера

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
 { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
 [ WITH APPEND ]
 [ NOT FOR REPLICATION ]

 AS
 [ { IF UPDATE ( column )
 [ { AND | OR } UPDATE ( column ) ]
 [ …n ]
 | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
 { comparison_operator } column_bitmask [ . ..n ]
 } ]
 sql_statement […n ]
 }
}

Описание аргументов:

INSTEAD OF – определяет триггер, который запускается вместо операции его вызвавшей. Этот тип триггера имитирует  работу стандартных триггеров  типа Befor (предшествующих событию.)

 Триггер типа INSTEAD OF  для операций INSERT, UPDATE  или DELETE  может быть задан как для таблицы так и для представления. Возможно  даже определить  триггеры для представления, которое само было создано на базе представления, и при этом у каждого будут свои собственные триггеры.

 INSTEAD OF  триггеры не поддерживают обновление представлений с  WITH CHECK OPTION.

Для триггеров типа  INSTEAD OF  операция DELETE  не поддерживается для таблиц, которые связаны ссылочной целостностью с установленным  свойством каскадирования при удалении  (cascade action ON DELETE). Аналогично,  для операций  UPDATE  не поддерживается создание триггеров данного типа  для таблиц, где задана каскадная операция обновления  ( cascade action ON UPDATE).

Для описания  внешних моделей в реляционной модели могут использоваться представление. Представление (View) – это SQL-запрос на выборку, который пользователь воспринимает как некоторое виртуальное отношение.

Создание представления:

CREATEVIEW <имя представления>

[ (<список столбцов>)]  AS <SQL-запрос>

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

Оператор изменения структуры таблицы

ALTER TABLE table
{ [ ALTER COLUMN column_name            — изменить описание столбца
 { new_data_type [ ( precision [ , scale ] ) ]
 [ COLLATE < collation_name > ]  — указывает новое сопоставление для столбцов
 [ NULL | NOT NULL ]
 | {ADD | DROP } ROWGUIDCOL }  — предписывает установить или удалить
 ]                                                                столбец

суникальнымидентификатором
 | ADD                                                         — добавить новый столбец
 { [ < column_definition > ]
 |  column_name AS computed_column_expression
 } [ ,. ..n ]
 | [ WITH CHECK | WITH NOCHECK ] ADD
 { < table_constraint > } [ ,…n ]
 | DROP                                                       — удалить
 { [ CONSTRAINT ] constraint_name     — удалить ограничение
 | COLUMN column } [

,…n ]               — удалить столбец
 | { CHECK | NOCHECK } CONSTRAINT     — обеспечить или отменить проверку
 { ALL | constraint_name [ ,…n ] }
 | { ENABLE | DISABLE } TRIGGER   — разрешить или запретить выполнение триггера
 { ALL | trigger_name [ ,…n ] }
}

Удаление объектов

Удаление таблиц

Drop table <имя таблицы>

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

Как создать временную таблицу в SQL Server — данные для сбора данных

Вот два подхода к созданию временной таблицы в SQL Server:

(1) Подход SELECT INTO :

 SELECT column_1, column_2, column_3, . ..
INTO #name_of_temp_table
ОТ имя_таблицы
ГДЕ условие
 

(2) Подход CREATE TABLE :

 CREATE TABLE #name_of_temp_table (
тип данных столбец_1,
тип данных column_2,
тип данных столбец_3,
        .
        .
        
столбец_n тип данных
)
 

Обратите внимание, что после того, как вы создали таблицу в соответствии со вторым подходом, вам нужно будет вставить записи в таблицу, используя запрос INSERT INTO :

 INSERT INTO #name_of_temp_table (column_1, column_2, column_3,...)
ВЫБЕРИТЕ столбец_1, столбец_2, столбец_3,...
ОТ имя_таблицы
ГДЕ условие
 

При обоих подходах перед именем временной таблицы необходимо включить символ решетки (#).

Вы можете удалить временную таблицу с помощью DROP TABLE запрос (или просто закрыв соединение, которое использовалось для создания временной таблицы):

 DROP TABLE #name_of_temp_table
 

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

(1) Создание временной таблицы в SQL Server с использованием подхода SELECT INTO

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

Текущая таблица «products» содержит следующие столбцы и данные:

product_id	имя_продукта	цена
1	Ноутбук	1200
2	Принтер	200
3	Планшет	350
4	Клавиатура	80
5	Монитор	400

Конечной целью является создание временной таблицы (из таблицы «продукты») для всех записей , где цена больше 300.

Затем вы можете создать временную таблицу (называемую #products_temp_table ) с использованием подхода SELECT INTO :

 SELECT product_id, product_name, price
INTO #products_temp_table
ИЗ продуктов
ГДЕ цена > 300
 

После выполнения запроса вы заметите, что затронуты 3 строки:

 (затронуты 3 строки)
 

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

 SELECT * FROM #products_temp_table
 

Как видите, сейчас в таблице есть 3 строки, где цена больше 300:

product_id	имя_продукта	цена
1	Ноутбук	1200
3	Планшет	350
5	Монитор	400

Вы можете удалить временную таблицу с помощью запроса DROP TABLE:

 DROP TABLE #products_temp_table
 

После удаления таблицы попробуйте снова выполнить запрос SELECT:

 SELECT * FROM #products_temp_table
 

Обратите внимание, что таблицы больше не существует:

 Сообщение 208, уровень 16, состояние 0, строка 1
Недопустимое имя объекта '#products_temp_table'. 
 

(2) Создание временной таблицы в SQL Server с использованием подхода CREATE TABLE

Давайте воссоздадим временную таблицу, используя второй подход CREATE TABLE :

 CREATE TABLE #products_temp_table (
product_id int первичный ключ,
product_name nvarchar(50),
цена инт
)
 

После того, как вы создали временную таблицу, вам нужно будет вставить записи в таблицу, используя ВСТАВИТЬ В запрос:

 ВСТАВИТЬ В #products_temp_table (product_id, product_name, price)
ВЫБЕРИТЕ product_id, product_name, цена
ИЗ продуктов
ГДЕ цена > 300
 

Вы заметите, что были затронуты 3 записи:

 (затронуты 3 строки)
 

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

 SELECT * FROM #products_temp_table
 

Как видите, есть 3 записи, где цена больше 300:

product_id	имя_продукта	цена
1	Ноутбук	1200
3	Планшет	350
5	Монитор	400

Чтобы удалить таблицу, используйте:

 DROP TABLE #products_temp_table
 

Создание глобальной временной таблицы в SQL Server

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

Глобальная временная таблица будет доступна через различные соединения.

Вот запрос для создания глобальной временной таблицы с использованием подхода SELECT INTO:

 SELECT product_id, product_name, price
INTO ##products_temp_table
ИЗ продуктов
ГДЕ цена > 300
 

Будут затронуты 3 записи:

 (затронуты 3 строки)
 

Затем вы можете запустить следующий запрос SELECT:

 SELECT * FROM ##products_temp_table
 

Вы получите те же 3 записи, где цена больше 300:

product_id	имя_продукта	цена
1	Ноутбук	1200
3	Планшет	350
5	Монитор	400

Чтобы удалить таблицу, используйте:

 DROP TABLE ##products_temp_table
 

Дополнительно можно использовать подход CREATE TABLE для создания глобальной временной таблицы:

 CREATE TABLE ##products_temp_table (
product_id int первичный ключ,
product_name nvarchar(50),
цена инт
)
 

Затем выполните запрос INSERT INTO, чтобы вставить записи в таблицу:

 ВСТАВИТЬ В ##products_temp_table (product_id, product_name, цена)
ВЫБЕРИТЕ product_id, product_name, цена
ИЗ продуктов
ГДЕ цена > 300
 

3 записи будут вставлены в таблицу.

Повторите запрос SELECT:

 SELECT * FROM ##products_temp_table
 

Как и прежде, вы увидите 3 записи, в которых цена больше 300:

product_id	имя_продукта	цена
1	Ноутбук	1200
3	Планшет	350
5	Монитор	400

Табличные переменные В T-SQL

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

Табличные переменные хранят набор записей, поэтому, естественно, синтаксис объявления очень похож на оператор CREATE TABLE, как вы можете видеть в следующем примере:

 DECLARE @ProductTotals TABLE
(
  ID продукта целое,
  Выручка
)
 

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

 ВСТАВЬТЕ В @ProductTotals (ProductID, Доход)
  ВЫБЕРИТЕ ProductID, СУММА (цена за единицу * количество)
    ОТ [Детали заказа]
    СГРУППИРОВАТЬ ПО ProductID
 

Табличные переменные можно использовать в пакетах, хранимых процедурах и пользовательских функциях (UDF). Мы можем ОБНОВИТЬ записи в нашей табличной переменной, а также УДАЛИТЬ записи.

 ОБНОВЛЕНИЕ @ProductTotals
  УСТАНОВИТЬ доход = доход * 1,15
ГДЕ ProductID = 62


УДАЛИТЬ ИЗ @ProductTotals
ГДЕ ProductID = 60


ВЫБЕРИТЕ ТОП 5 *
ОТ @ProductTotals
ЗАКАЗАТЬ ПО доходу DESC
 

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

Прицел

В отличие от большинства других типов данных в SQL Server, вы не можете использовать табличную переменную в качестве входного или выходного параметра. На самом деле табличная переменная относится к хранимой процедуре, пакету или определяемой пользователем функции точно так же, как и любая локальная переменная, которую вы создаете с помощью инструкции DECLARE. Переменная больше не будет существовать после выхода из процедуры — не будет таблицы для очистки с помощью инструкции DROP.

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

Ограниченная область действия табличной переменной дает SQL Server некоторую свободу для выполнения оптимизации.

Производительность

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

Использование временной таблицы внутри хранимой процедуры может привести к дополнительным повторным компиляциям хранимой процедуры. Табличные переменные часто могут избежать этой перекомпиляции. Дополнительные сведения о том, почему хранимые процедуры могут перекомпилироваться, см. в статье 243586 базы знаний Майкрософт (INF: устранение неполадок при перекомпиляции хранимых процедур).

Другие функции

Ограничения — это отличный способ убедиться, что данные в таблице соответствуют определенным требованиям, и вы можете использовать ограничения с табличными переменными. Следующий пример гарантирует, что значения ProductID в таблице будут уникальными, а все цены меньше 10,0.

 ОБЪЯВИТЬ СТОЛ @MyTable
(
  ProductID целое УНИКАЛЬНОЕ,
  Цена денег CHECK(Цена < 10.0)
)
 

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

 ОБНОВЛЕНИЕ @ProductTotals
ОБЪЯВИТЬ ТАБЛИЦУ @MyTable
(
  ProductID int IDENTITY(1,1) ПЕРВИЧНЫЙ КЛЮЧ,
  Имя varchar(10) НЕ NULL ПО УМОЛЧАНИЮ('Неизвестно')
)
 

До сих пор казалось, что табличные переменные могут делать все то же, что и временные таблицы в рамках хранимой процедуры, пакета или UDF), но есть некоторые недостатки.

Ограничения

Вы не можете создать некластеризованный индекс для табличной переменной, если индекс не является побочным эффектом ограничения PRIMARY KEY или UNIQUE для таблицы (SQL Server применяет любые ограничения UNIQUE или PRIMARY KEY с помощью индекса).

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

Табличное определение табличной переменной не может измениться после оператора DECLARE. Любой запрос ALTER TABLE, пытающийся изменить табличную переменную, завершится ошибкой синтаксиса. Точно так же вы не можете использовать табличную переменную с запросами SELECT INTO или INSERT EXEC. Если вы используете табличную переменную в объединении, вам потребуется псевдоним таблицы, чтобы выполнить запрос.

 ВЫБЕРИТЕ ProductName, Доход
ИЗ ПРОДУКЦИИ Р
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ @ProductTotals PT ON P.ProductID = PT.ProductID
 

Вы можете использовать табличную переменную с динамическим SQL, но вы должны объявить таблицу внутри самого динамического SQL. Следующий запрос завершится с ошибкой «Необходимо объявить переменную @MyTable».

 ОБЪЯВИТЬ СТОЛ @MyTable
(
  ID продукта целое,
  Имя varchar(10)
)


EXEC sp_executesql N'SELECT * FROM @MyTable'
 

Также важно отметить, что табличные переменные не участвуют в откате транзакций. Хотя это может быть преимуществом в производительности, оно также может застать вас врасплох, если вы не знаете о поведении. Чтобы продемонстрировать, следующий пакет запросов вернет 77 записей, несмотря на то, что INSERT был выполнен внутри транзакции с ROLLBACK.

 DECLARE @ProductTotals TABLE
(
  ID продукта целое,
  Выручка
)
 
НАЧАТЬ СДЕЛКУ
 
  ВСТАВЬТЕ В @ProductTotals (ProductID, Доход)
    ВЫБЕРИТЕ ProductID, СУММА (цена за единицу * количество)
    ОТ [Детали заказа]
    СГРУППИРОВАТЬ ПО ProductID
 
ОТМЕНА ТРАНЗАКЦИИ
 
ВЫБЕРИТЕ СЧЕТ(*) ИЗ @ProductTotals
 

Выбор между временными таблицами и табличными переменными

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

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

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

Пример: разделение

Табличные переменные — лучшая альтернатива использованию временных таблиц во многих ситуациях. Возможность использовать табличную переменную в качестве возвращаемого значения UDF — одно из лучших применений табличных переменных. В следующем примере мы рассмотрим общую потребность: функцию для разбора строки с разделителями на части. Другими словами, учитывая строку «1,5,9” — мы хотим вернуть таблицу с записью для каждого значения: 1, 5 и 9.

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

.

 SELECT * FROM fn_Split('foo,bar,widget', ',')
 

вернет следующий набор результатов.

 значение позиции
1 фу
2 бар
3 виджет

 

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

, если существует (выберите * из dbo.sysobjects, где id = ob-ject_id(N'[dbo].[fn_Split]') и xtype в (N'FN', N'IF', N'TF'))
функция сброса [dbo].[fn_Split]
ИДТИ  ВЫКЛЮЧИТЬ QUOTED_IDENTIFIER
ИДТИ
ВЫКЛЮЧИТЬ ANSI_NULLS
ИДТИ  СОЗДАТЬ ФУНКЦИЮ fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
ВОЗВРАТ @Strings ТАБЛИЦА
(
 позиция int ИДЕНТИФИКАЦИОННЫЙ ПЕРВИЧНЫЙ КЛЮЧ,
 значение varchar(8000)
)
КАК
НАЧИНАТЬ  ОБЪЯВИТЬ @index int
УСТАНОВИТЕ @индекс = -1  ПОКА (ДЛСТР(@текст) > 0)  НАЧИНАТЬ
 SET @index = CHARINDEX(@delimiter , @text)
 ЕСЛИ (@index = 0) И (LEN(@text) > 0)
 НАЧИНАТЬ
 ВСТАВИТЬ В @Strings VALUES (@text)
 ПЕРЕРЫВ
 КОНЕЦ  ЕСЛИ (@индекс > 1)
 НАЧИНАТЬ
 INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
 SET @text = RIGHT(@text, (LEN(@text) - @index))
 КОНЕЦ
 ЕЩЕ
 SET @text = RIGHT(@text, (LEN(@text) - @index))
 КОНЕЦ
 ВОЗВРАЩАТЬСЯ  КОНЕЦ
ИДТИ  ВЫКЛЮЧИТЬ QUOTED_IDENTIFIER
ИДТИ  УСТАНОВИТЕ ANSI_NULLS ВКЛ.
No related posts.