Справочное руководство по MySQL. Удаление столбцов NTEXT. Прочие ограничения – UNIQUE, DEFAULT, CHECK

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

Синтаксис

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name [ ({ precision [ , scale ] | max | xml_schema_collection }) ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ SPARSE ] | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE } } | [ WITH { CHECK | NOCHECK } ] | ADD { | | | } [ ,...n ] | DROP { [ CONSTRAINT ] { constraint_name [ WITH ( [ ,...n ]) ] } [ ,...n ] | COLUMN { column_name } [ ,...n ] } [ ,...n ] | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } | { ENABLE | DISABLE } CHANGE_TRACKING [ WITH (TRACK_COLUMNS_UPDATED = { ON | OFF }) ] | SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ] [ WITH () ] | SET (FILESTREAM_ON = { partition_scheme_name | filegroup | "default" | "NULL" }) | REBUILD [ [ WITH ( [ ,...n ]) ] | [ PARTITION = partition_number [ WITH ( [ ,...n ]) ] ] ] | | } [ ; ] -- ALTER TABLE options ::= column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ::= { MAXDOP = max_degree_of_parallelism | ONLINE = { ON | OFF } | MOVE TO { partition_scheme_name (column_name) | filegroup | "default" } } ::= { SET (LOCK_ESCALATION = { AUTO | TABLE | DISABLE }) } ::= { [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ] [ SET (FILETABLE_DIRECTORY = directory_name) ] } ::= { SORT_IN_TEMPDB = { ON | OFF } | MAXDOP = max_degree_of_parallelism | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} } | ONLINE = { ON [() ] | OFF } } ::= { WAIT_AT_LOW_PRIORITY (MAX_DURATION = [ MINUTES ], ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }) }

Аргументы

database_name
Имя базы данных, в которой создана таблица.

schema_name
Имя схемы, которой принадлежит таблица.

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

ALTER COLUMN
Указывает, что именованный столбец подлежит изменению.

Нельзя изменять следующие столбцы.

Столбец с типом данных timestamp.

Свойство ROWGUIDCOL для таблицы.

Вычисляемый столбец или используемый в вычисляемом столбце.

Используемый в статистике, сформированной с помощью инструкции CREATE STATISTICS, если только столбец не принадлежит к типу данных varchar, nvarchar или varbinary, тип данных не изменялся и новый размер больше старого или равен ему, а значение столбца не заменялось значением NULL. Во-первых, удалите статистику, используя инструкцию DROP STATISTICS. Статистика, автоматически сформированная оптимизатором запросов, автоматически удаляется инструкцией ALTER COLUMN.

Используется в ограничении PRIMARY KEY или REFERENCES.

Используется в ограничениях CHECK или UNIQUE. Однако допустимо изменение длины столбца изменяемой длины, используемого в ограничении CHECK или UNIQUE.

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

Тип данных столбцов text, ntext и image может быть изменен только следующими способами:
- text на varchar(max), nvarchar(max) или xml;
  
  ntext на varchar(max), nvarchar(max) или xml;
  
  image в varbinary(max)
Некоторые изменения типов данных могут повлечь за собой изменения в данных. Например, изменение столбца типа nchar или типа nvarchar на char или varchar может вызвать преобразование символов национальных алфавитов. Дополнительные сведения см. в разделе Функции CAST и CONVERT (Transact-SQL) . Снижение точности или масштаба столбца может привести к усечению данных.

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

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

Столбцы, включенные в ограничение первичного ключа, нельзя изменить с NOT NULL на NULL.

column_name
Имя столбца, который требуется изменить, добавить или удалить. Длина значения column_name не может превышать 128 символов. Для новых столбцов аргумент column_name может быть опущен, если столбцы создаются с типом данных timestamp. Имя timestamp используется, если column_name не указано для столбца типа данных timestamp.

[ type_schema_name**.** ] type_name
Новый тип данных для изменяемого столбца либо тип данных для добавляемого столбца. Значение type_name нельзя задать для существующих столбцов секционированных таблиц. type_name может быть любым из следующих значений:

Системным типом данных SQL Server.

Псевдонимом типа данных, основанным на системном типе данных SQL Server. Прежде чем псевдонимы типов данных можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE.

Определяемый пользователем тип.NET Framework и схема, к которой он принадлежит. Чтобы определяемые пользователем типы.NET Framework можно было использовать в определении столбца, их сначала нужно создать с помощью инструкции CREATE TYPE.

Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.

type_name не может иметь тип timestamp.

По умолчанию для аргумента ANSI_NULL инструкции ALTER COLUMN всегда установлено значение ON; если не указано иное, столбец может содержать значения NULL.

Аргумент заполнения ANSI_PADDING для инструкции ALTER COLUMN всегда принимает значение ON.

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

Текущая установка для аргумента SET ARITHABORT пропускается. Инструкция ALTER TABLE функционирует аналогично случаю, когда для аргумента ARITHABORT установлено значение ON.

Примечание

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

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

scale
Масштаб указанного типа данных. Дополнительные сведения о допустимых значениях масштаба см. в разделе Точность, масштаб и длина (Transact-SQL) .

max
Применим только к типам данных varchar, nvarchar и varbinary для хранения 2^31-1 байт символов, двоичных данных и данных Юникода.

xml_schema_collection

Применяется только к данным типа xml для связывания схемы XML с этим типом. Перед вводом столбца xml в коллекцию схемы необходимо при помощи CREATE XML SCHEMA COLLECTION создать коллекцию схемы в базе данных.

COLLATE < collation_name >
Задает новые параметры сортировки для изменяемого столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Список и дополнительные сведения см. в разделах Имя параметров сортировки Windows (Transact-SQL) и Имя параметров сортировки SQL Server (Transact-SQL) .

Предложение COLLATE может быть использовано для изменения параметров сортировки только для столбцов типов данных char, varchar, nchar и nvarchar. Чтобы изменить параметры сортировки столбца пользовательского псевдонима типа данных, необходимо выполнить отдельные инструкции ALTER TABLE, чтобы изменить столбец на системный тип данных SQL Server, изменить параметры сортировки, а затем снова перевести столбец в псевдоним типа данных.

Инструкция ALTER COLUMN не может изменить параметры сортировки, если выполняется одно или несколько из следующих условий:

Если на изменяемый столбец ссылается ограничение CHECK, ограничение FOREIGN KEY или вычисляемые столбцы.

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

Если связанное со схемой представление или функция ссылаются на столбец.

Дополнительные сведения см. в разделе COLLATE (Transact-SQL) .

NULL | NOT NULL
Указывает, может ли столбец принимать значения NULL. Столбцы, не допускающие значения NULL, могут быть добавлены инструкцией ALTER TABLE только в том случае, если для них указаны значения по умолчанию или если таблица пуста. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае, если одновременно указан параметр PERSISTED. Если новый столбец допускает значения NULL, а значение по умолчанию не задано, то новый столбец содержит значение NULL для каждой строки в таблице. Если новый столбец допускает значение NULL, а определение по умолчанию добавляется с новым столбцом, то инструкция WITH VALUES может использоваться для хранения значений по умолчанию в новом столбце для каждой существующей строки в таблице.

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

Значение NULL может указываться в инструкции ALTER COLUMN, чтобы принудить столбец NOT NULL допускать значения NULL, за исключением столбцов в ограничениях PRIMARY KEY. Значение NOT NULL может быть указано в инструкции ALTER COLUMN, только если столбец не содержит значения NULL. Значения NULL следует обновить, присвоив некоторые значения, прежде чем разрешить инструкцию ALTER COLUMN NOT NULL, например:

UPDATE MyTable SET NullCol = N"some_value" WHERE NullCol IS NULL; ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;

При создании или изменении таблицы инструкцией CREATE TABLE или ALTER TABLE установки базы данных и сеанса изменяются и, возможно, переопределяют разрешение содержать значение NULL для типа данных, используемого в определении столбца. Рекомендуется всегда явно определять невычисляемые столбцы как NULL или NOT NULL.

Если добавляется столбец определяемого пользователем типа данных, то рекомендуется определить для этого столбца то же состояние допустимости значений NULL, что и в определяемом пользователем типе данных и задать для него значение по умолчанию. Дополнительные сведения см. в разделе CREATE TABLE (SQL Server) .

Примечание

Если в инструкции ALTER COLUMN указано значение NULL или NOT NULL, то необходимо также указать параметры new_data_type [(precision [, scale ])].Если тип данных, точность или масштаб не изменялись, укажите текущие значения столбца.

[ {ADD | DROP} ROWGUIDCOL ]

Указывает свойство ROWGUIDCOL, добавленное к указанному столбцу или удаленное из него. Свойство ROWGUIDCOL указывает, что данный столбец является столбцом идентификатора GUID строки. Только один столбец в таблице uniqueidentifier может быть выделен как столбец ROWGUIDCOL, и свойство ROWGUIDCOL может присваиваться только столбцу uniqueidentifier. Нельзя присваивать свойство ROWGUIDCOL столбцу определяемого пользователем типа данных.

Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце, и не формирует автоматически значения для новых строк, вставляемых в таблицу. Для формирования уникальных значений для каждого столбца можно использовать функцию NEWID в инструкциях INSERT или определить функцию NEWID как значение по умолчанию для столбца.

[ {ADD | DROP} PERSISTED ]
Указывает, что свойство PERSISTED добавлено к указанному столбцу или удалено из него. Столбец должен быть вычисляемым столбцом, который задается при помощи детерминированных выражений. Для столбцов, указанных как PERSISTED, компонент Компонент Database Engine физически хранит вычисляемые значения в таблице и обновляет значения при обновлении любого столбца, от которого зависит вычисляемый столбец. Если пометить вычисляемый столбец как PERSISTED, можно создавать индексы по вычисляемым столбцам, которые заданы, являются детерминированными, но не точными выражениями.

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

DROP NOT FOR REPLICATION

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

SPARSE
Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Для разреженных столбцов нельзя указать параметр NOT NULL. При преобразовании столбцов из разреженных в неразреженные или из неразреженных в разреженные таблица блокируется на протяжении выполнения команды. Возможно, потребуется использование предложения REBUILD для освобождения пространства. Дополнительные ограничения и сведения о разреженных столбцах см. в разделе .

WITH CHECK | WITH NOCHECK
Указывает, удовлетворяют ли данные в таблице недавно добавленному или повторно включенному ограничению FOREIGN KEY или CHECK. Если не указано иное, для новых ограничений предполагается использовать WITH CHECK, а для повторно включенных ограничений - WITH NOCHECK.

Если проверка новых ограничений CHECK или FOREIGN KEY относительно существующих данных не нужна, используйте WITH NOCHECK. За исключением редких случаев делать этого не рекомендуется. Новое ограничение будет проверяться во всех дальнейших обновлениях данных. Любые нарушения ограничений, подавляемые WITH NOCHECK во время добавления ограничения, могут вызвать сбой будущих обновлений, если они обновляют строки данными, не соответствующими ограничениям.

Оптимизатор запросов не рассматривает ограничения, которые определены WITH NOCHECK. Такие ограничения не учитываются до тех пор, пока они не будут повторно включены инструкцией ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

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

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
Указывает, что из таблицы удалено ограничение constraint_name или column_name. Может быть перечислено несколько столбцов и ограничений.

Пользовательское имя ограничения или имя ограничения, предоставленное системой, можно определить с помощью запросов к представлениям каталога sys.check_constraint , sys.default_constraints , sys.key_constraints и sys.foreign_keys .

Невозможно удалить ограничение PRIMARY KEY, если в таблице существует XML-индекс.

Невозможно удалить столбец, если он:

используется в индексе;

используется в ограничениях CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY;

связан со значением по умолчанию, определенным ключевым словом DEFAULT, или привязан к объекту «значение по умолчанию»;

привязан к правилу.

Примечание

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

WITH
Указывает, что установлен один или несколько параметров удаления кластеризованного ограничения.

MAXDOP = max_degree_of_parallelism

Переопределяет параметр конфигурации max degree of parallelism только на время выполнения операции. Дополнительные сведения см. в разделе .

Используйте параметр MAXDOP для ограничения числа процессоров, применяемых при выполнении параллельных планов. Максимальное число процессоров - 64.

Аргумент max_degree_of_parallelism может иметь одно из следующих значений.

1
Подавляет формирование параллельных планов.

>1
Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.

0 (по умолчанию)
В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

Дополнительные сведения см. в разделе .

Примечание

Параллельные операции с индексами доступны не во всех выпусках SQL Server.Дополнительные сведения см. в разделе .

ONLINE = {ON | OFF } <применяемое к drop_clustered_constraint_option>
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию - OFF. REBUILD может выполняться как операция в режиме ONLINE.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это обеспечивает продолжение выполнения запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то в завершение операции на короткое время запрашивается совмещаемая блокировка (S) для источника. Блокировка типа SCH-M (изменение схемы) запрашивается, когда кластеризованный индекс создается или удаляется в режиме в сети и когда происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON. Допустима только однопотоковая операция перестроения кучи.

Для выполнения инструкции DDL для SWITCH или перестроения индекса в режиме «в сети» все активные блокирующие транзакции, выполняемые для конкретной таблицы, должны быть завершены. В процессе выполнения инструкция SWITCH или операция перестроения индекса не позволяют запустить новую транзакцию и могут существенно повлиять на пропускную способность рабочей нагрузки и временно задержать доступ к базовой таблице.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. Многопотоковые операции перестроения кучи разрешены.

Дополнительные сведения см. в разделе .

Примечание

Операции с индексами в сети доступны не во всех выпусках SQL Server.Дополнительные сведения см. в разделе .

MOVE TO { partition_scheme_name**(column_name [ 1 ,** ... n] ) | filegroup | "default " }

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

Примечание

В этом контексте default не является ключевым словом.Это идентификатор файловой группы по умолчанию, и поэтому он должен быть заключен в разделители, например: MOVE TO « default» или MOVE TO [ default] .Если указано значение " default" , то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON.Это параметр по умолчанию.Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL) .

{ CHECK | NOCHECK } CONSTRAINT
Указывает, включено или отключено ограничение constraint_name. Данный параметр может использоваться только с ограничениями FOREIGN KEY и CHECK. Если указан параметр NOCHECK, то ограничение отключено и будущие вставки или обновления столбца не проверяются относительно условий ограничений. Невозможно отключить ограничения DEFAULT, PRIMARY KEY и UNIQUE.

ALL
Указывает, что все ограничения отключаются при помощи параметра NOCHECK или включаются при помощи параметра CHECK.

{ ENABLE | DISABLE } TRIGGER
Указывает, включено или отключено ограничение trigger_name. Отключенный триггер остается определенным для таблицы; однако при выполнении инструкций INSERT, UPDATE или DELETE относительно таблицы действия в триггере не выполняются до тех пор, пока он не будет включен повторно.

ALL
Указывает, что все триггеры в таблице включены или отключены.

trigger_name
Указывает имя триггера, подлежащего включению или отключению.

{ ENABLE | DISABLE } CHANGE_TRACKING

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

Этот параметр доступен только в том случае, если отслеживание изменений разрешено для базы данных. Дополнительные сведения см. в разделе Параметры ALTER DATABASE SET (Transact-SQL) .

Чтобы разрешить отслеживание изменений, в таблице должен содержаться первичный ключ.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

Указывает, производит ли компонент Компонент Database Engine отслеживание обновлений столбцов. Значение по умолчанию - OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO { schema_name ] target_table [ PARTITION target_ partition_number_expression ]

Переключает блок данных одним из следующих способов.

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

Переключает секции из одной секционированной таблицы в другую.

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

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

Если происходит переназначение данных одной секции для формирования одиночной таблицы, то уже должна быть создана пустая целевая таблица. И исходная таблица или секция, и целевая таблица или секция должны располагаться в одной файловой группе. Соответствующие индексы или секции индексов также должны располагаться в одной и той же файловой группе. К переключаемым секциям применяются многие дополнительные ограничения. Значения table и target_table не могут быть одинаковыми. Значение target_table может быть идентификатором, состоящим из нескольких частей.

Аргументы source_partition_number_expression и target_partition_number_expression являются константными выражениями, которые могут ссылаться на переменные и функции. В их число входят переменные определяемого пользователем типа и определяемые пользователем функции. Они не могут ссылаться на выражения языка Transact-SQL.

Сведения об ограничении SWITCH при использовании репликации см. в разделе .

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default " | "NULL " })

Указывает местоположения хранения данных FILESTREAM.

Инструкция ALTER TABLE с предложением SET FILESTREAM_ON будет выполнена успешно только в том случае, если в таблице отсутствуют столбцы FILESTREAM. Добавить столбцы FILESTREAM можно с помощью второй инструкции ALTER TABLE.

Если указан partition_scheme_name, то применяются правила для CREATE TABLE . Таблица должна быть уже секционирована для строк данных, а схема разделения должна использовать те же функции секционирования и столбцы, что используются в схеме секционирования FILESTREAM.

Аргумент filestream_filegroup_name указывает имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью инструкции CREATE DATABASE или ALTER DATABASE , иначе возникает ошибка.

"default " указывает файловую группу FILESTREAM с заданным свойством DEFAULT. При отсутствии файловой группы FILESTREAM возникает ошибка.

"NULL " указывает на удаление всех ссылок на файловые группы FILESTREAM для таблицы. Сначала должны быть удалены все столбцы FILESTREAM. Необходимо использовать инструкцию SET FILESTREAM_ON**="NULL "** для удаления всех данных FILESTREAM, связанных с таблицей.

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

Указывает разрешенные методы укрупнения блокировки для таблицы.

AUTO
Этот параметр позволяет Компонент SQL Server Database Engine выбрать гранулярность укрупнения блокировки, подходящую для данной схемы таблицы.
- В секционированных таблицах допускается укрупнение блокировки до секций. После укрупнения блокировки до уровня секции дальнейшее укрупнение до гранулярности TABLE выполняться не будет.
  
  Если таблица не секционирована, то блокировка будет укрупняться до гранулярности TABLE.

TABLE
Укрупнение блокировки будет выполняться на уровне гранулярности таблицы независимо от того, секционирована таблица или нет. Значение по умолчанию равно TABLE.

DISABLE
В большинстве случаев предотвращает укрупнение блокировки. Блокировки уровня таблицы запрещены не полностью. Например, при сканировании таблицы, которая не имеет кластеризованного индекса на уровне изоляции SERIALIZABLE, компонент Компонент Database Engine должен установить блокировку таблицы для защиты целостности данных.

REBUILD
Используйте синтаксис REBUILD WITH для перестроения всей таблицы, включая все секции в секционированную таблицу. Если в таблице содержится кластеризованный индекс, то параметр REBUILD перестраивает его. REBUILD может выполняться как операция в режиме ONLINE.

Используйте синтаксис REBUILD PARTITION для перестроения одной секции в секционированной таблице.

Перестраивает все секции при изменении настройки сжатия секций.

REBUILD WITH ()
Все параметры применяются к таблице с кластеризованным индексом. Если в таблице не содержится кластеризованный индекс, то на структуру кучи влияют только определенные параметры.

Если определенный параметр сжатия не был указан с помощью операции REBUILD, то для секции будет использован текущий параметр сжатия. Для возврата текущего параметра выполните запрос к столбцу data_compression из представления каталога sys.partitions .

Полное описание этих параметров перестроения см. в разделе index_option (Transact-SQL) .

DATA_COMPRESSION

Задает режим сжатия данных для указанной таблицы, номера секции или диапазона секций. Ниже приведены доступные параметры.

NONE
Таблица или указанные секции не сжимаются. Это не относится к таблицам columnstore.

ROW
Таблицы или указанные секции сжимаются, используя сжатие строк. Это не относится к таблицам columnstore.

PAGE
Таблицы или указанные секции сжимаются, используя сжатие страниц. Это не относится к таблицам columnstore.

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

COLUMNSTORE_ARCHIVE

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

Чтобы перестроить несколько секций одновременно, см. в разделе index_option (Transact-SQL) . Если в таблице отсутствует кластеризованный индекс, то при изменении сжатия данных выполняется перестроение кучи и некластеризованных индексов. Дополнительные сведения о сжатии см. в разделе .

ONLINE = { ON | OFF } <применяемое к single_partition_rebuild_option>
Определяет, будут ли отдельная секция базовой таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию - OFF. REBUILD может выполняться как операция в режиме ONLINE.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Необходимо наличие S-блокировки таблицы в начале перестройки индекса и блокировки Sch-M на таблице в конце перестроения индекса в режиме «в сети». Обе блокировки являются короткими блокировками метаданных, но при этом блокировка изменения схемы (Sch-M) должна ожидать завершения всех блокирующих транзакций. Во время ожидания Sch-M блокирует все другие транзакции, ожидающие за этой блокировкой доступа к одной таблице.

Примечание

Перестроение индекса в режиме «в сети» может задать параметры low_priority_lock_wait, описанные ниже в этом разделе.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Это предотвращает доступ к базовой таблице всех пользователей во время операции.

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Имя набора столбцов. Набор столбцов представляет собой нетипизированное XML-представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. Набор столбцов не может быть добавлен в таблицу, если в ней содержатся разреженные столбцы. Дополнительные сведения о наборах столбцов см. в разделе .

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

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

SET (FILETABLE_DIRECTORY = directory_name)

Указывает имя каталога таблицы FileTable, совместимое с Windows. Это имя должно быть уникальным среди всех имен каталогов FileTable в базе данных. Проверка уникальности не учитывает регистр символов независимо от параметров сортировки SQL. Может использоваться только для таблицы FileTable.

WAIT_AT_LOW_PRIORITY

Перестроение индекса в режиме «в сети» должно ожидать операции блокировки в этой таблице. Значение WAIT_AT_LOW_PRIORITY указывает, что операция перестроения индекса в режиме «в сети» будет ожидать блокировки с низким приоритетом, позволяя выполняться другим операциям, пока операция перестроения индекса в режиме «в сети» находится в состоянии ожидания. Пропуск параметра WAIT AT LOW PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES ]

Время ожидания (целочисленное значение, указанное в минутах) в течение которого SWITCH или блокировки для операции перестроения индекса в режиме «в сети» будут ожидать с низким приоритетом при выполнении команды DDL. Если операция будет заблокирована на время MAX_DURATION, будет выполнено одно из действий ABORT_AFTER_WAIT. Время MAX_DURATION всегда указывается в минутах, и слово MINUTES можно опустить.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE (Нет)
Продолжить ожидание блокировки с обычным приоритетом.

SELF
Прекратить операцию SWITCH или операцию DDL по перестроению индекса в сети, выполняемую в данный момент, без предпринятия какого-либо действия.

BLOCKERS
Остановить все пользовательские транзакции, в данный момент блокирующие операцию SWITCH или операцию DDL по перестроению индекса в сети, чтобы можно было продолжить данную операцию.

Требуется разрешение ALTER ANY CONNECTION

Замечания

Чтобы добавить новые строки данных, используйте INSERT . Чтобы удалить строки данных, используйте DELETE или TRUNCATE TABLE . Чтобы изменить значения в существующих строках, используйте UPDATE .

При наличии в кэше процедур каких-либо планов выполнения, ссылающихся на таблицу, инструкция ALTER TABLE помечает их как подлежащие перекомпиляции в их следующем выполнении.

Изменение размера столбца

Длину, точность и масштаб столбца можно изменить, указав новый размер для типа данных столбца в предложении ALTER COLUMN. Если в столбце имеются данные, новый размер не может быть меньше максимального размера данных. Кроме того, столбец нельзя определять в индексе, если тип данных столбца не относится к varchar, nvarchar или varbinary, а индекс не является результатом ограничения PRIMARY KEY. См. пример Р.

Блокировки и инструкция ALTER TABLE

Изменения, указанные в инструкции ALTER TABLE, воплощаются немедленно. Если для изменений требуется модификация строк таблицы, то инструкция ALTER TABLE обновляет эти строки. Инструкция ALTER TABLE получает блокировку модификации схемы (SCH-M) для таблицы, чтобы убедиться, что в процессе изменения другие соединения не ссылаются даже на метаданные таблицы, за исключением операций с индексами в сети, требующих очень короткой блокировки SCH-M в конце. В операции ALTER TABLE...SWITCH запрашивается блокировка и исходной, и целевой таблиц. Изменения, сделанные в таблице, регистрируются в журнале и полностью обратимы. Изменения, затрагивающие все строки в очень больших таблицах, например удаление столбца или (в некоторых выпусках SQL Server) добавление столбца NOT NULL со значением по умолчанию, могут занять длительное время и привести к созданию множества записей в журнале. Данные инструкции ALTER TABLE следует выполнять с той же осторожностью, что и любые инструкции INSERT, UPDATE или DELETE, влияющие на множество строк.

Добавление столбцов NOT NULL в качестве операции в сети

Начиная с SQL Server 2012 Enterprise Edition, добавление столбца NOT NULL со значением по умолчанию является операцией в сети, если значение по умолчанию является константой времени выполнения. Это значит, что операция выполняется почти мгновенно, независимо от количества строк в таблице. Так происходит потому, что существующие строки в таблице не обновляются в ходе операции. Вместо этого значение по умолчанию сохраняется только в метаданных таблицы, и это значение подставляется по мере необходимости в запросах, обращающихся к этим строкам. Такой режим работы действует автоматически. Для реализации операции в сети достаточно синтаксиса ADD COLUMN. Константа времени выполнения - это выражение, которое имеет одно и то же значение во время выполнения для каждой строки в таблице независимо от ее детерминизма. Например, выражение константы «Временные данные» и системная функция GETUTCDATETIME() являются константами времени выполнения. Функции NEWID() и NEWSEQUENTIALID(), напротив, не являются константами времени выполнения, поскольку для каждой строки в таблице создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, которое не является константой времени выполнения, всегда выполняется вне сети, и в течение этой операции накладывается монопольная блокировка (SCH-M).

Хотя существующие строки ссылаются на значение, хранящееся в метаданных, значение по умолчанию хранится в строке для всех новых строк, которые вставляются без указания другого значения для столбца. Значение по умолчанию, хранящееся в метаданных, перемещается в существующую строку, когда строка обновляется (даже если в инструкции UPDATE не указан фактический столбец), а также когда перестраивается таблица или кластеризованный индекс.

Столбцы типа varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography или определяемых пользователем типов CLR нельзя добавлять в операцию в сети. Столбец нельзя добавлять в сети, если в результате такой операции максимальный размер строки превысит ограничение в 8060 байт. В этом случае столбец добавляется в рамках операции вне сети.

Выполнение параллельного плана

В выпуске Microsoft SQL Server 2012 Enterprise и более поздних версиях число процессоров, применяемых для выполнения одной инструкции ALTER TABLE ADD (на базе индекса) CONSTRAINT или DROP (кластеризованный индекс) CONSTRAINT, определяется с помощью параметра конфигурации max degree of parallelism и текущей рабочей нагрузки. Если компонент Компонент Database Engine определяет, что система занята, то перед началом выполнения инструкции степень параллелизма операции автоматически понижается. Можно вручную настроить число процессоров, применяемых для запуска инструкции, указав параметр MAXDOP. Дополнительные сведения см. в разделе .

Секционированные таблицы

Помимо выполнения операций SWITCH, затрагивающих секционированные таблицы, инструкция ALTER TABLE может использоваться для изменения состояния столбцов, ограничений и триггеров секционированной таблицы точно так же, как она используется для несекционированных таблиц. Однако данная инструкция не может использоваться для изменения способа, которым секционируется сама таблица. Чтобы заново секционировать секционированную таблицу, используйте ALTER PARTITION SCHEME и ALTER PARTITION FUNCTION . Кроме того, невозможно изменить тип данных столбца секционированной таблицы.

Ограничения в таблицах с представлениями, привязанными к схемам

К инструкциям ALTER TABLE в таблицах с представлениями, привязанными к схемам, применяются те же ограничения, которые применяются в текущем времени для изменения таблиц с простым индексом. Добавление столбца разрешено. Однако удаление или изменение столбца, участвующего в любом из представлений, привязанных к схемам, не разрешается. Если инструкция ALTER TABLE требует изменения столбца, используемого в привязанном к схеме представлении, то происходит сбой инструкции ALTER TABLE и компонент Компонент Database Engine выдает сообщение об ошибке. Дополнительные сведения о привязке к схеме и индексированных представлениях см. в разделе CREATE VIEW (Transact-SQL) .

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

Индексы и инструкция ALTER TABLE

При удалении ограничений индексы, создаваемые как часть ограничения, удаляются. Индексы, создаваемые при помощи инструкции CREATE INDEX, должны удаляться при помощи инструкции DROP INDEX. Инструкция ALTER INDEX может использоваться для перестроения индексной части определения ограничения; не следует удалять и вновь добавлять ограничение при помощи инструкции ALTER TABLE.

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

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

Параметр MOVE TO недопустим для индексированных представлений и некластеризованных индексов.

Схема секционирования или файловая группа уже должна существовать.

Если не указан аргумент MOVE TO, то таблица будет размещена в той же схеме секционирования или файловой группе, которая была определена для кластеризованного индекса.

При удалении кластеризованного индекса можно указать параметр ONLINE = ON, так что транзакция DROP INDEX не будет блокировать запросы к лежащим в основе данным и изменениям в них, а также в связанных с ними некластеризованных индексах.

Параметр ONLINE = ON имеет следующие ограничения.

= ON для кластеризованных индексов, которые также отключены. Отключенные индексы должны удаляться при помощи параметра ONLINE = OFF.

Только один индекс может удаляться единовременно.

Недопустимо использование параметра ONLINE = ON для индексированного представления, некластеризованных индексов или индексов в локальных временных таблицах.

Значение ONLINE = ON недопустимо для индексов columnstore.

Для удаления кластеризованного индекса временно требуется место на диске, равное размеру существующего кластеризованного индекса. Это дополнительное пространство освобождается сразу после завершения операции.

Примечание

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

Репликация изменений схемы

По умолчанию при выполнении команды ALTER TABLE для опубликованной таблицы в издателе SQL Server Publisher это изменение распространяется для всех подписчиков SQL Server. Эта функция имеет некоторые ограничения и может быть отключена. Дополнительные сведения см. в разделе .

Сжатие данных

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

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings .

На секционированные таблицы налагаются следующие ограничения.

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

Синтаксис ALTER TABLE

Удаление столбцов NTEXT

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

Перестроение индексов в режиме «в сети».

Для выполнения инструкции DDL для перестроения индекса в режиме «в сети» все активные блокирующие транзакции, выполняемые для конкретной таблицы, должны быть завершены. Если выполняется перестроение индекса в режиме «в сети», то все новые транзакции, готовые к выполнению на данной таблице, блокируются. Хотя продолжительность блокировки для перестроения индекса в режиме «в сети» очень коротка, ожидание завершения всех открытых транзакций на данной таблице и блокировка новых запускаемых транзакций может значительно отразиться на пропускной способности и времени выполнения операции, а также значительно ограничить доступ к базовой таблице. Параметр WAIT_AT_LOW_PRIORITY позволяет администратору базы данных управлять S и Sch-M блокировками, необходимыми для перестроения индекса в режиме «в сети». Доступны 3 варианта. Во всех 3 случаях, если во время ожидания ((MAX_DURATION =n )) нет блокирующих действий, то перестроение индекса в режиме «в сети» выполняется немедленно и без ожидания завершения инструкции DDL.

Поддержка совместимости

В инструкции ALTER TABLE разрешается использовать только имена таблиц, составленные из двух частей (схема.объект). В SQL Server 2014 при задании имени таблицы в приведенных далее форматах во время компиляции возникает ошибка 117.

«сервер.база_данных.схема.таблица»

«.база_данных.схема.таблица»

«..схема.таблица»

В предыдущих версиях при задании формата «сервер.база_данных.схема.таблица» возникала ошибка 4902. Формат «.база_данных.схема.таблица» или «..схема.таблица» обрабатывался успешно.

Чтобы устранить эту проблему, используйте четырехкомпонентный префикс.

Разрешения

Требуется разрешение ALTER на таблицу.

Разрешения ALTER TABLE применяются к обеим таблицам, затронутым инструкцией ALTER TABLE SWITCH. Любые переключенные данные наследуют защиту целевой таблицы.

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

Для добавления столбца, который обновляет строки таблицы, требуется разрешение UPDATE для этой таблицы. Например, добавление столбца NOT NULL со значением по умолчанию или добавление столбца идентификаторов, если таблица не пуста.

Примеры

Добавление столбцов и ограничений

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

А.Добавление нового столбца

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

CREATE TABLE dbo.doc_exa (column_a INT) ; GO ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ; GO

Б.Добавление столбца с ограничением

В следующем примере показано добавление нового столбца с ограничением UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT) ; GO ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ; GO EXEC sp_help doc_exc ; GO DROP TABLE dbo.doc_exc ; GO

В.Добавление непроверяемого ограничения CHECK к существующему столбцу

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

CREATE TABLE dbo.doc_exd (column_a INT) ; GO INSERT INTO dbo.doc_exd VALUES (-1) ; GO ALTER TABLE dbo.doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1) ; GO EXEC sp_help doc_exd ; GO DROP TABLE dbo.doc_exd ; GO

Г.Добавление ограничения DEFAULT к существующему столбцу

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

CREATE TABLE dbo.doc_exz (column_a INT, column_b INT) ; GO INSERT INTO dbo.doc_exz (column_a)VALUES (7) ; GO ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ; GO INSERT INTO dbo.doc_exz (column_a) VALUES (10) ; GO SELECT * FROM dbo.doc_exz ; GO DROP TABLE dbo.doc_exz ; GO

Д.Добавление нескольких столбцов с ограничениями

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

CREATE TABLE dbo.doc_exe (column_a INT CONSTRAINT column_a_un UNIQUE) ; GO ALTER TABLE dbo.doc_exe ADD -- Add a PRIMARY KEY identity column. column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY, -- Add a column that references another column in the same table. column_c INT NULL CONSTRAINT column_c_fk REFERENCES doc_exe(column_a), -- Add a column with a constraint to enforce that -- nonnull data is in a valid telephone number format. column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK (column_d LIKE "-" OR column_d LIKE "() -"), -- Add a nonnull column with a default. column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .081 ; GO EXEC sp_help doc_exe ; GO DROP TABLE dbo.doc_exe ; GO

Е.Добавление столбца, допускающего значения NULL, со значениями по умолчанию

Следующий пример показывает добавление столбца, допускающего значения NULL, с определением DEFAULT и использование WITH VALUES для предоставления значений каждой строке таблицы. Если аргумент WITH VALUES не используется, то каждая строка в новом столбце имеет значение NULL.

CREATE TABLE dbo.doc_exf (column_a INT) ; GO INSERT INTO dbo.doc_exf VALUES (1) ; GO ALTER TABLE dbo.doc_exf ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES ; GO DROP TABLE dbo.doc_exf ; GO

Ж.Создание ограничения PRIMARY KEY с параметрами индекса

Следующий пример показывает создание ограничения PRIMARY KEY PK_TransactionHistoryArchive_TransactionID и установку параметров FILLFACTOR, ONLINE и PAD_INDEX. Результирующий кластеризованный индекс будет иметь такое же имя, что и ограничение.

З.Добавление разреженного столбца

В следующих примерах показывается добавление и изменение разреженных столбцов в таблице T1. Код для создания таблицы T1:

CREATE TABLE T1 (C1 int PRIMARY KEY, C2 varchar(50) SPARSE NULL, C3 int SPARSE NULL, C4 int) ; GO

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

ALTER TABLE T1 ADD C5 char(100) SPARSE NULL ; GO

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

ALTER TABLE T1 ALTER COLUMN C4 ADD SPARSE ; GO

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

ALTER TABLE T1 ALTER COLUMN C4 DROP SPARSE; GO

И.Добавление набора столбцов

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

CREATE TABLE T2 (C1 int PRIMARY KEY, C2 varchar(50) NULL, C3 int NULL, C4 int) ; GO

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

ALTER TABLE T2 ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ; GO ALTER TABLE T2 ALTER COLUMN C2 ADD SPARSE ; GO ALTER TABLE T2 ALTER COLUMN C3 ADD SPARSE ; GO

[В начало]

Удаление столбцов и ограничений

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

А.Удаление столбца или столбцов

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

CREATE TABLE dbo.doc_exb (column_a INT ,column_b VARCHAR(20) NULL ,column_c datetime ,column_d int) ; GO -- Remove a single column. ALTER TABLE dbo.doc_exb DROP COLUMN column_b ; GO -- Remove multiple columns. ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

Б.Удаление ограничений и столбцов

В первом примере из таблицы удаляется ограничение UNIQUE. Во втором примере удаляется 2 ограничения и один столбец.

CREATE TABLE dbo.doc_exc (column_a int NOT NULL CONSTRAINT my_constraint UNIQUE) ; GO -- Example 1. Remove a single constraint. ALTER TABLE dbo.doc_exc DROP my_constraint ; GO DROP TABLE dbo.doc_exc; GO CREATE TABLE dbo.doc_exc (column_a int NOT NULL CONSTRAINT my_constraint UNIQUE ,column_b int NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ; GO -- Example 2. Remove two constraints and one column -- The keyword CONSTRAINT is optional. The keyword COLUMN is required. ALTER TABLE dbo.doc_exc DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ; GO

В.Удаление ограничения PRIMARY KEY в режиме ONLINE

В следующем примере удаляется ограничение PRIMARY KEY с параметром ONLINE, имеющим значение ON.

ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID WITH (ONLINE = ON); GO

Г.Добавление и удаление ограничения FOREIGN KEY

Следующий пример показывает создание таблицы ContactBackup, а затем ее изменение сначала добавлением ограничения FOREIGN KEY, ссылающегося на таблицу Person.Person, затем удалением ограничения FOREIGN KEY.

CREATE TABLE Person.ContactBackup (ContactID int) ; GO ALTER TABLE Person.ContactBackup ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID) ; GO ALTER TABLE Person.ContactBackup DROP CONSTRAINT FK_ContactBacup_Contact ; GO DROP TABLE Person.ContactBackup ;

[В начало]

Изменение определения столбца

А.изменение типа данных столбца.

В следующем примере столбец таблицы изменяется с INT на DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT) ; GO INSERT INTO dbo.doc_exy (column_a) VALUES (10) ; GO ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ; GO DROP TABLE dbo.doc_exy ; GO

Б.Изменение размера столбца

В следующем примере выполняется увеличение размера столбца varchar, а также точности и масштаба столбца decimal. Поскольку столбцы содержат данные, их размер можно только увеличить. Также обратите внимание, что столбец col_a определяется в уникальном индексе. Размер столбца col_a можно увеличивать, поскольку он имеет тип данных varchar, а индекс не является результатом ограничения PRIMARY KEY.

IF OBJECT_ID ("dbo.doc_exy", "U") IS NOT NULL DROP TABLE dbo.doc_exy; GO -- Create a two-column table with a unique index on the varchar column. CREATE TABLE dbo.doc_exy (col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2)); GO INSERT INTO dbo.doc_exy VALUES ("Test", 99.99); GO -- Verify the current column size. SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N"dbo.doc_exy"); GO -- Increase the size of the varchar column. ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25); GO -- Increase the scale and precision of the decimal column. ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4); GO -- Insert a new row. INSERT INTO dbo.doc_exy VALUES ("MyNewColumnSize", 99999.9999) ; GO -- Verify the current column size. SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N"dbo.doc_exy");

В.Изменение параметров сортировки столбца

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

CREATE TABLE T3 (C1 int PRIMARY KEY, C2 varchar(50) NULL, C3 int NULL, C4 int) ; GO

Затем параметры сортировки столбца C2 изменяются на Latin1_General_BIN. Обратите внимание, что необходимо указать тип данных, хотя он не изменяется.

ALTER TABLE T3 ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN; GO

[В начало]

Изменение определения таблицы

В приведенных в этом разделе примерах показано, как изменить определение таблицы.

А.Изменение таблицы для изменения режима сжатия

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

ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE);

В следующем примере изменяется режим сжатия секционированной таблицы. Инструкция REBUILD PARTITION = 1 вызывает перестройку только секции с номером 1.

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

Дополнительные примеры сжатия данных см. в разделе .

Б.Модификация таблицы columnstore для изменения архивного сжатия

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

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

В.Переключение секций между таблицами

В следующем примере демонстрируется создание секционированной таблицы, исходя из предположения, что схема секционирования myRangePS1 уже создана в базе данных. Затем создается несекционированная таблица с такой же структурой, что и секционированная таблица, и в той же файловой группе, что и PARTITION 2 таблицы PartitionTable. В таком случае данные PARTITION 2 таблицы PartitionTable переключаются в таблицу NonPartitionTable.

CREATE TABLE PartitionTable (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO CREATE TABLE NonPartitionTable (col1 int, col2 char(10)) ON test2fg ; GO ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ; GO

Г.Разрешение укрупнения блокировки для секционированных таблиц

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

Д.Настройка отслеживания изменений для таблицы

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

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

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

[В начало]

Отключение и включение ограничений и триггеров

A.Отключение и повторное включение ограничения

В следующем примере отключается ограничение на зарплату. Параметр NOCHECK CONSTRAINT используется в инструкции ALTER TABLE для отключения ограничения и обеспечения возможности вставки, противоречащей указанному ограничению. CHECK CONSTRAINT повторно включает ограничение.

CREATE TABLE dbo.cnst_example (id INT NOT NULL, name VARCHAR(10) NOT NULL, salary MONEY NOT NULL CONSTRAINT salary_cap CHECK (salary < 100000)); -- Valid inserts INSERT INTO dbo.cnst_example VALUES (1,"Joe Brown",65000); INSERT INTO dbo.cnst_example VALUES (2,"Mary Smith",75000); -- This insert violates the constraint. INSERT INTO dbo.cnst_example VALUES (3,"Pat Jones",105000); -- Disable the constraint and try again. ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap; INSERT INTO dbo.cnst_example VALUES (3,"Pat Jones",105000); -- Re-enable the constraint and try another insert; this will fail. ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap; INSERT INTO dbo.cnst_example VALUES (4,"Eric James",110000) ;

Б.Отключение и повторное включение триггера

В следующем примере показывается использование параметра DISABLE TRIGGER инструкции ALTER TABLE для отключения триггера и обеспечения возможности вставки, которая в обычных условиях нарушает триггер. Затем инструкция ENABLE TRIGGER используется для повторного включения триггера.

CREATE TABLE dbo.trig_example (id INT, name VARCHAR(12), salary MONEY) ; GO -- Create the trigger. CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT AS IF (SELECT COUNT(*) FROM INSERTED WHERE salary > 100000) > 0 BEGIN print "TRIG1 Error: you attempted to insert a salary > $100,000" ROLLBACK TRANSACTION END ; GO -- Try an insert that violates the trigger. INSERT INTO dbo.trig_example VALUES (1,"Pat Smith",100001) ; GO -- Disable the trigger. ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ; GO -- Try an insert that would typically violate the trigger. INSERT INTO dbo.trig_example VALUES (2,"Chuck Jones",100001) ; GO -- Re-enable the trigger. ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ; GO -- Try an insert that violates the trigger. INSERT INTO dbo.trig_example VALUES (3,"Mary Booth",100001) ; GO

Tutorial

О чем данный учебник

языка SQL

DML – Data Manipulation Language (язык манипулирования данными), который содержит следующие конструкции:
- SELECT – выборка данных
- INSERT – вставка новых данных
- UPDATE – обновление данных
- DELETE – удаление данных
- MERGE – слияние данных

Данный учебник создан по принципу Step by Step, т.е. необходимо читать его последовательно и желательно сразу же выполняя примеры. Но если по ходу у вас возникает потребность узнать о какой-то команде более детально, то используйте конкретный поиск в интернет, например, в библиотеке MSDN.

При написании данного учебника использовалась база данных MS SQL Server версии 2014, для выполнения скриптов я использовал MS SQL Server Management Studio (SSMS).

Кратко о MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) - утилита для Microsoft SQL Server для конфигурирования, управления и администрирования компонентов базы данных. Данная утилита содержит редактор скриптов (который в основном и будет нами использоваться) и графическую программу, которая работает с объектами и настройками сервера. Главным инструментом SQL Server Management Studio является Object Explorer, который позволяет пользователю просматривать, извлекать объекты сервера, а также управлять ими. Данный текст частично позаимствован с википедии.

Для создания нового редактора скрипта используйте кнопку «New Query/Новый запрос»:

Для смены текущей базы данных можно использовать выпадающий список:

Для выполнения определенной команды (или группы команд) выделите ее и нажмите кнопку «Execute/Выполнить» или же клавишу «F5». Если в редакторе в текущий момент находится только одна команда, или же вам необходимо выполнить все команды, то ничего выделять не нужно.

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

Собственно, это все, что нам необходимо будет знать для выполнения приведенных здесь примеров. Остальное по утилите SSMS несложно изучить самостоятельно.

Немного теории

СУБД – Система Управления этими Базами Данных, т.е. это комплекс инструментов для работы с конкретным типом БД (MS SQL, Oracle, MySQL, Firebird, …).

Примечание
Т.к. в жизни, в разговорной речи, мы по большей части говорим: «БД Oracle», или даже просто «Oracle», на самом деле подразумевая «СУБД Oracle», то в контексте данного учебника иногда будет употребляться термин БД. Из контекста, я думаю, будет понятно, о чем именно идет речь.

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

Таблица – это главный объект РБД, все данные РБД хранятся построчно в столбцах таблицы. Строки, записи – тоже синонимы.

Для каждой таблицы, как и ее столбцов задаются наименования, по которым впоследствии к ним идет обращение.
Наименование объекта (имя таблицы, имя столбца, имя индекса и т.п.) в MS SQL может иметь максимальную длину 128 символов.

Для справки – в БД ORACLE наименования объектов могут иметь максимальную длину 30 символов. Поэтому для конкретной БД нужно вырабатывать свои правила для наименования объектов, чтобы уложиться в лимит по количеству символов.

SQL - язык позволяющий осуществлять запросы в БД посредством СУБД. В конкретной СУБД, язык SQL может иметь специфичную реализацию (свой диалект).

DDL и DML - подмножество языка SQL:

Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.
Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные.

В языке SQL можно использовать 2 вида комментариев (однострочный и многострочный):

Однострочный комментарий
и

/* многострочный комментарий */

Собственно, все для теории этого будет достаточно.

DDL – Data Definition Language (язык описания данных)

данном случае

Каждый из этих столбцов можно охарактеризовать по типу содержащемся в нем данных:

Табельный номер – целое число
ФИО – строка
Дата рождения – дата
E-mail – строка
Должность – строка
Отдел – строка

Для начала будет достаточно запомнить только следующие основные типы данных используемые в MS SQL:

Значение	Обозначение в MS SQL	Описание
Строка переменной длины	varchar(N) и nvarchar(N)	При помощи числа N, мы можем указать максимально возможную длину строки для соответствующего столбца. Например, если мы хотим сказать, что значение столбца «ФИО» может содержать максимум 30 символов, то необходимо задать ей тип nvarchar(30). Отличие varchar от nvarchar заключается в том, что varchar позволяет хранить строки в формате ASCII, где один символ занимает 1 байт, а nvarchar хранит строки в формате Unicode, где каждый символ занимает 2 байта. Тип varchar стоит использовать только в том случае, если вы на 100% уверены, что в данном поле не потребуется хранить Unicode символы. Например, varchar можно использовать для хранения адресов электронной почты, т.к. они обычно содержат только ASCII символы.
Строка фиксированной длины	char(N) и nchar(N)	От строки переменной длины данный тип отличается тем, что если длина строка меньше N символов, то она всегда дополняется справа до длины N пробелами и сохраняется в БД в таком виде, т.е. в базе данных она занимает ровно N символов (где один символ занимает 1 байт для char и 2 байта для типа nchar). На моей практике данный тип очень редко находит применение, а если и используется, то он используется в основном в формате char(1), т.е. когда поле определяется одним символом.
Целое число	int	Данный тип позволяет нам использовать в столбце только целые числа, как положительные, так и отрицательные. Для справки (сейчас это не так актуально для нас) – диапазон чисел который позволяет тип int от -2 147 483 648 до 2 147 483 647. Обычно это основной тип, который используется для задания идентификаторов.
Вещественное или действительное число	float	Если говорить простым языком, то это числа, в которых может присутствовать десятичная точка (запятая).
Дата	date	Если в столбце необходимо хранить только Дату, которая состоит из трех составляющих: Числа, Месяца и Года. Например, 15.02.2014 (15 февраля 2014 года). Данный тип можно использовать для столбца «Дата приема», «Дата рождения» и т.п., т.е. в тех случаях, когда нам важно зафиксировать только дату, или, когда составляющая времени нам не важна и ее можно отбросить или если она не известна.
Время	time	Данный тип можно использовать, если в столбце необходимо хранить только данные о времени, т.е. Часы, Минуты, Секунды и Миллисекунды. Например, 17:38:31.3231603 Например, ежедневное «Время отправления рейса».
Дата и время	datetime	Данный тип позволяет одновременно сохранить и Дату, и Время. Например, 15.02.2014 17:38:31.323 Для примера это может быть дата и время какого-нибудь события.
Флаг	bit	Данный тип удобно применять для хранения значений вида «Да»/«Нет», где «Да» будет сохраняться как 1, а «Нет» будет сохраняться как 0.

Для выполнения примеров создадим тестовую базу под названием Test.

Простую базу данных (без указания дополнительных параметров) можно создать, выполнив следующую команду:

CREATE DATABASE Test
Удалить базу данных можно командой (стоит быть очень осторожным с данной командой):

DROP DATABASE Test
Для того, чтобы переключиться на нашу базу данных, можно выполнить команду:

USE Test
Или же выберите базу данных Test в выпадающем списке в области меню SSMS. При работе мною чаще используется именно этот способ переключения между базами.

Теперь в нашей БД мы можем создать таблицу используя описания в том виде как они есть, используя пробелы и символы кириллицы:

CREATE TABLE [Сотрудники]([Табельный номер] int, [ФИО] nvarchar(30), [Дата рождения] date, nvarchar(30), [Должность] nvarchar(30), [Отдел] nvarchar(30))
В данном случае нам придется заключать имена в квадратные скобки […].

Но в базе данных для большего удобства все наименования объектов лучше задавать на латинице и не использовать в именах пробелы. В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1.

На заметку
В некоторых СУБД более предпочтительным может быть следующий формат наименований «PHONE_NUMBER», например, такой формат часто используется в БД ORACLE. Естественно при задании имя поля желательно чтобы оно не совпадало с ключевыми словами используемые в СУБД.

По этой причине можете забыть о синтаксисе с квадратными скобками и удалить таблицу [Сотрудники]:

DROP TABLE [Сотрудники]
Например, таблицу с сотрудниками можно назвать «Employees», а ее полям можно задать следующие наименования:

ID – Табельный номер (Идентификатор сотрудника)
Name – ФИО
Birthday – Дата рождения
Email – E-mail
Position – Должность
Department – Отдел

Теперь создадим нашу таблицу:

CREATE TABLE Employees(ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.

Для уже существующей таблицы поля можно переопределить при помощи следующих команд:

Обновление поля ID ALTER TABLE Employees ALTER COLUMN ID int NOT NULL -- обновление поля Name ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL

На заметку
Общая концепция языка SQL для большинства СУБД остается одинаковой (по крайней мере, об этом я могу судить по тем СУБД, с которыми мне довелось поработать). Отличие DDL в разных СУБД в основном заключаются в типах данных (здесь могут отличаться не только их наименования, но и детали их реализации), так же может немного отличаться и сама специфика реализации языка SQL (т.е. суть команд одна и та же, но могут быть небольшие различия в диалекте, увы, но одного стандарта нет). Владея основами SQL вы легко сможете перейти с одной СУБД на другую, т.к. вам в данном случае нужно будет только разобраться в деталях реализации команд в новой СУБД, т.е. в большинстве случаев достаточно будет просто провести аналогию.
Создание таблицы CREATE TABLE Employees(ID int, -- в ORACLE тип int - это эквивалент(обертка) для number(38) Name nvarchar2(30), -- nvarchar2 в ORACLE эквивалентен nvarchar в MS SQL Birthday date, Email nvarchar2(30), Position nvarchar2(30), Department nvarchar2(30)); -- обновление полей ID и Name (здесь вместо ALTER COLUMN используется MODIFY(…)) ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- добавление PK (в данном случае конструкция выглядит как и в MS SQL, она будет показана ниже) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
Для ORACLE есть отличия в плане реализации типа varchar2, его кодировка зависит настроек БД и текст может сохраняться, например, в кодировке UTF-8. Помимо этого длину поля в ORACLE можно задать как в байтах, так и в символах, для этого используются дополнительные опции BYTE и CHAR, которые указываются после длины поля, например:
NAME varchar2(30 BYTE) -- вместимость поля будет равна 30 байтам NAME varchar2(30 CHAR) -- вместимость поля будет равна 30 символов
Какая опция будет использоваться по умолчанию BYTE или CHAR, в случае простого указания в ORACLE типа varchar2(30), зависит от настроек БД, так же она иногда может задаваться в настройках IDE. В общем порой можно легко запутаться, поэтому в случае ORACLE, если используется тип varchar2 (а это здесь порой оправдано, например, при использовании кодировки UTF-8) я предпочитаю явно прописывать CHAR (т.к. обычно длину строки удобнее считать именно в символах).

Но в данном случае если в таблице уже есть какие-нибудь данные, то для успешного выполнения команд необходимо, чтобы во всех строках таблицы поля ID и Name были обязательно заполнены. Продемонстрируем это на примере, вставим в таблицу данные в поля ID, Position и Department, это можно сделать следующим скриптом:

INSERT Employees(ID,Position,Department) VALUES (1000,N"Директор",N"Администрация"), (1001,N"Программист",N"ИТ"), (1002,N"Бухгалтер",N"Бухгалтерия"), (1003,N"Старший программист",N"ИТ")
В данном случае, команда INSERT также выдаст ошибку, т.к. при вставке мы не указали значения обязательного поля Name.
В случае, если бы у нас в первоначальной таблице уже имелись эти данные, то команда «ALTER TABLE Employees ALTER COLUMN ID int NOT NULL» выполнилась бы успешно, а команда «ALTER TABLE Employees ALTER COLUMN Name int NOT NULL» выдала сообщение об ошибке, что в поле Name имеются NULL (не указанные) значения.

Добавим значения для полю Name и снова зальем данные:

Так же опцию NOT NULL можно использовать непосредственно при создании новой таблицы, т.е. в контексте команды CREATE TABLE.

Сначала удалим таблицу при помощи команды:

DROP TABLE Employees
Теперь создадим таблицу с обязательными для заполнения столбцами ID и Name:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Можно также после имени столбца написать NULL, что будет означать, что в нем будут допустимы NULL-значения (не указанные), но этого делать не обязательно, так как данная характеристика подразумевается по умолчанию.

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

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Или просто:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
Так же данной командой мы можем изменить тип поля на другой совместимый тип, или же изменить его длину. Для примера давайте расширим поле Name до 50 символов:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

Первичный ключ

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

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
Где «PK_Employees» это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы.

Если первичный ключ состоит из нескольких полей, то эти поля необходимо перечислить в скобках через запятую:

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY(поле1,поле2,…)
Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

Так же первичный ключ можно определить непосредственно при создании таблицы, т.е. в контексте команды CREATE TABLE. Удалим таблицу:

DROP TABLE Employees
А затем создадим ее, используя следующий синтаксис:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- описываем PK после всех полей, как ограничение)
После создания зальем в таблицу данные:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Директор",N"Администрация",N"Иванов И.И."), (1001,N"Программист",N"ИТ",N"Петров П.П."), (1002,N"Бухгалтер",N"Бухгалтерия",N"Сидоров С.С."), (1003,N"Старший программист",N"ИТ",N"Андреев А.А.")
Если первичный ключ в таблице состоит только из значений одного столбца, то можно использовать следующий синтаксис:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- указываем как характеристику поля Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), PRIMARY KEY(ID))
Или:

CREATE TABLE Employees(ID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Но я бы рекомендовал для постоянных таблиц всегда явно задавать имя ограничения, т.к. по явно заданному и понятному имени с ним впоследствии будет легче проводить манипуляции, например, можно произвести его удаление:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
Но такой краткий синтаксис, без указания имен ограничений, удобно применять при создании временных таблиц БД (имя временной таблицы начинается с # или ##), которые после использования будут удалены.

Подытожим

CREATE TABLE имя_таблицы (перечисление полей и их типов, ограничений) – служит для создания новой таблицы в текущей БД;
DROP TABLE имя_таблицы – служит для удаления таблицы из текущей БД;
ALTER TABLE имя_таблицы ALTER COLUMN имя_столбца … – служит для обновления типа столбца или для изменения его настроек (например для задания характеристики NULL или NOT NULL);
ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY (поле1, поле2,…) – добавление первичного ключа к уже существующей таблице;
ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения – удаление ограничения из таблицы.

Немного про временные таблицы

Вырезка из MSDN. В MS SQL Server существует два вида временных таблиц: локальные (#) и глобальные (##). Локальные временные таблицы видны только их создателям до завершения сеанса соединения с экземпляром SQL Server, как только они впервые созданы. Локальные временные таблицы автоматически удаляются после отключения пользователя от экземпляра SQL Server. Глобальные временные таблицы видны всем пользователям в течение любых сеансов соединения после создания этих таблиц и удаляются, когда все пользователи, ссылающиеся на эти таблицы, отключаются от экземпляра SQL Server.

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

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Так как временная таблица в MS SQL аналогична обычной таблице, ее соответственно так же можно удалить самому командой DROP TABLE:

DROP TABLE #Temp

Так же временную таблицу (как собственно и обычную таблицу) можно создать и сразу заполнить данными возвращаемые запросом используя синтаксис SELECT … INTO:

SELECT ID,Name INTO #Temp FROM Employees

На заметку
В разных СУБД реализация временных таблиц может отличаться. Например, в СУБД ORACLE и Firebird структура временных таблиц должна быть определена заранее командой CREATE GLOBAL TEMPORARY TABLE с указанием специфики хранения в ней данных, дальше уже пользователь видит ее среди основных таблиц и работает с ней как с обычной таблицей.

Нормализация БД – дробление на подтаблицы (справочники) и определение связей

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

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

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

Давайте создадим 2 таблицы справочники «Должности» и «Отделы», первую назовем Positions, а вторую соответственно Departments:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY, Name nvarchar(30) NOT NULL)
Заметим, что здесь мы использовали новую опцию IDENTITY, которая говорит о том, что данные в столбце ID будут нумероваться автоматически, начиная с 1, с шагом 1, т.е. при добавлении новых записей им последовательно будут присваиваться значения 1, 2, 3, и т.д. Такие поля обычно называют автоинкрементными. В таблице может быть определено только одно поле со свойством IDENTITY и обычно, но необязательно, такое поле является первичным ключом для данной таблицы.

На заметку
В разных СУБД реализация полей со счетчиком может делаться по своему. В MySQL, например, такое поле определяется при помощи опции AUTO_INCREMENT. В ORACLE и Firebird раньше данную функциональность можно было съэмулировать при помощи использования последовательностей (SEQUENCE). Но насколько я знаю в ORACLE сейчас добавили опцию GENERATED AS IDENTITY.

Давайте заполним эти таблицы автоматически, на основании текущих данных записанных в полях Position и Department таблицы Employees:

Заполняем поле Name таблицы Positions, уникальными значениями из поля Position таблицы Employees INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- отбрасываем записи у которых позиция не указана
То же самое проделаем для таблицы Departments:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
Если теперь мы откроем таблицы Positions и Departments, то увидим пронумерованный набор значений по полю ID:

SELECT * FROM Positions

SELECT * FROM Departments

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

Добавляем поле для ID должности ALTER TABLE Employees ADD PositionID int -- добавляем поле для ID отдела ALTER TABLE Employees ADD DepartmentID int
Тип ссылочных полей должен быть каким же, как и в справочниках, в данном случае это int.

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

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Теперь пропишем ссылки (ссылочные ограничения - FOREIGN KEY) для этих полей, для того чтобы пользователь не имел возможности записать в данные поля, значения, отсутствующие среди значений ID находящихся в справочниках.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
И то же самое сделаем для второго поля:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Теперь пользователь в данные поля сможет занести только значения ID из соответствующего справочника. Соответственно, чтобы использовать новый отдел или должность, он первым делом должен будет добавить новую запись в соответствующий справочник. Т.к. должности и отделы теперь хранятся в справочниках в одном единственном экземпляре, то чтобы изменить название, достаточно изменить его только в справочнике.

Имя ссылочного ограничения, обычно является составным, оно состоит из префикса «FK_», затем идет имя таблицы и после знака подчеркивания идет имя поля, которое ссылается на идентификатор таблицы-справочника.

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

ALTER TABLE таблица ADD CONSTRAINT имя_ограничения FOREIGN KEY(поле1,поле2,…) REFERENCES таблица_справочник(поле1,поле2,…)
В данном случае в таблице «таблица_справочник» первичный ключ представлен комбинацией из нескольких полей (поле1, поле2,…).

Собственно, теперь обновим поля PositionID и DepartmentID значениями ID из справочников. Воспользуемся для этой цели DML командой UPDATE:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
Посмотрим, что получилось, выполнив запрос:

SELECT * FROM Employees

Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице Employees теперь нет, можно удалить эти поля:

ALTER TABLE Employees DROP COLUMN Position,Department
Теперь таблица у нас приобрела следующий вид:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID
1000	Иванов И.И.	NULL	NULL	2	1
1001	Петров П.П.	NULL	NULL	3	3
1002	Сидоров С.С.	NULL	NULL	1	2
1003	Андреев А.А.	NULL	NULL	4	3

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Positions p ON p.ID=e.PositionID

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

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

ALTER TABLE Employees ADD ManagerID int
В данном поле допустимо значение NULL, поле будет пустым, если, например, над сотрудником нет вышестоящих.

Теперь создадим FOREIGN KEY на таблицу Employees:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Давайте, теперь создадим диаграмму и посмотрим, как выглядят на ней связи между нашими таблицами:

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

Напоследок стоит сказать, что ссылочные ключи могут включать дополнительные опции ON DELETE CASCADE и ON UPDATE CASCADE, которые говорят о том, как вести себя при удалении или обновлении записи, на которую есть ссылки в таблице-справочнике. Если эти опции не указаны, то мы не можем изменить ID в таблице справочнике у той записи, на которую есть ссылки из другой таблицы, так же мы не сможем удалить такую запись из справочника, пока не удалим все строки, ссылающиеся на эту запись или, же обновим в этих строках ссылки на другое значение.

Для примера пересоздадим таблицу с указанием опции ON DELETE CASCADE для FK_Employees_DepartmentID:

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)) INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219",2,1,NULL), (1001,N"Петров П.П.","19831203",3,3,1003), (1002,N"Сидоров С.С.","19760607",1,2,1000), (1003,N"Андреев А.А.","19820417",4,3,1000)
Удалим отдел с идентификатором 3 из таблицы Departments:

DELETE Departments WHERE ID=3
Посмотрим на данные таблицы Employees:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID
1000	Иванов И.И.	1955-02-19	NULL	2	1	NULL
1002	Сидоров С.С.	1976-06-07	NULL	1	2	1000

Опция ON UPDATE CASCADE ведет себя аналогично, но действует она при обновлении значения ID в справочнике. Например, если мы поменяем ID должности в справочнике должностей, то в этом случае будет производиться обновление DepartmentID в таблице Employees на новое значение ID которое мы задали в справочнике. Но в данном случае это продемонстрировать просто не получится, т.к. у колонки ID в таблице Departments стоит опция IDENTITY, которая не позволит нам выполнить следующий запрос (сменить идентификатор отдела 3 на 30):

UPDATE Departments SET ID=30 WHERE ID=3
Главное понять суть этих 2-х опций ON DELETE CASCADE и ON UPDATE CASCADE. Я применяю эти опции очень в редких случаях и рекомендую хорошо подумать, прежде чем указывать их в ссылочном ограничении, т.к. при нечаянном удалении записи из таблицы справочника это может привести к большим проблемам и создать цепную реакцию.

Восстановим отдел 3:

Даем разрешение на добавление/изменение IDENTITY значения SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"ИТ") -- запрещаем добавление/изменение IDENTITY значения SET IDENTITY_INSERT Departments OFF
Полностью очистим таблицу Employees при помощи команды TRUNCATE TABLE:

TRUNCATE TABLE Employees
И снова перезальем в нее данные используя предыдущую команду INSERT:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219",2,1,NULL), (1001,N"Петров П.П.","19831203",3,3,1003), (1002,N"Сидоров С.С.","19760607",1,2,1000), (1003,N"Андреев А.А.","19820417",4,3,1000)

Подытожим

Добавление свойства IDENTITY к полю – позволяет сделать это поле автоматически заполняемым (полем-счетчиком) для таблицы;
ALTER TABLE имя_таблицы ADD перечень_полей_с_характеристиками – позволяет добавить новые поля в таблицу;
ALTER TABLE имя_таблицы DROP COLUMN перечень_полей – позволяет удалить поля из таблицы;
ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения FOREIGN KEY (поля) REFERENCES таблица_справочник(поля) – позволяет определить связь между таблицей и таблицей справочником.

Прочие ограничения – UNIQUE, DEFAULT, CHECK

UPDATE Employees SET Email="[email protected]" WHERE ID=1000 UPDATE Employees SET Email="[email protected]" WHERE ID=1001 UPDATE Employees SET Email="[email protected]" WHERE ID=1002 UPDATE Employees SET Email="[email protected]" WHERE ID=1003
А теперь можно наложить на это поле ограничение-уникальности:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

Ограничение уникальности обычно именуется следующим образом – сначала идет префикс «UQ_», далее название таблицы и после знака подчеркивания идет имя поля, на которое накладывается данное ограничение.

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

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения UNIQUE(поле1,поле2,…)
При помощи добавления к полю ограничения DEFAULT мы можем задать значение по умолчанию, которое будет подставляться в случае, если при вставке новой записи данное поле не будет перечислено в списке полей команды INSERT. Данное ограничение можно задать непосредственно при создании таблицы.

Давайте добавим в таблицу Employees новое поле «Дата приема» и назовем его HireDate и скажем что значение по умолчанию у данного поля будет текущая дата:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
Или если столбец HireDate уже существует, то можно использовать следующий синтаксис:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Здесь я не указал имя ограничения, т.к. в случае DEFAULT у меня сложилось мнение, что это не столь критично. Но если делать по-хорошему, то, думаю, не нужно лениться и стоит задать нормальное имя. Делается это следующим образом:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Та как данного столбца раньше не было, то при его добавлении в каждую запись в поле HireDate будет вставлено текущее значение даты.

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

INSERT Employees(ID,Name,Email)VALUES(1004,N"Сергеев С.С.","[email protected]")
Посмотрим, что получилось:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Иванов И.И.	1955-02-19	[email protected]	2	1	NULL	2015-04-08
1001	Петров П.П.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Сидоров С.С.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Андреев А.А.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Сергеев С.С.	NULL	[email protected]	NULL	NULL	NULL	2015-04-08

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

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

INSERT Employees(ID,Email) VALUES(2000,"[email protected]")
А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

INSERT Employees(ID,Email) VALUES(1500,"[email protected]")
Можно так же создать ограничения UNIQUE и CHECK без указания имени:

ALTER TABLE Employees ADD UNIQUE(Email) ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)
Но это не очень хорошая практика и лучше задавать имя ограничения в явном виде, т.к. чтобы разобраться потом, что будет сложнее, нужно будет открывать объект и смотреть, за что он отвечает.

При хорошем наименовании много информации об ограничении можно узнать непосредственно по его имени.

И, соответственно, все эти ограничения можно создать сразу же при создании таблицы, если ее еще нет. Удалим таблицу:

DROP TABLE Employees
И пересоздадим ее со всеми созданными ограничениями одной командой CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- для DEFAULT я сделаю исключение CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999))

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N"Иванов И.И.","19550219","[email protected]",2,1), (1001,N"Петров П.П.","19831203","[email protected]",3,3), (1002,N"Сидоров С.С.","19760607","[email protected]",1,2), (1003,N"Андреев А.А.","19820417","[email protected]",4,3)

Немного про индексы, создаваемые при создании ограничений PRIMARY KEY и UNIQUE

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY NONCLUSTERED(поле1,поле2,…)
Для примера сделаем индекс ограничения PK_Employees некластерным, а индекс ограничения UQ_Employees_Email кластерным. Первым делом удалим данные ограничения:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
А теперь создадим их с опциями CLUSTERED и NONCLUSTERED:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (ID) ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (Email)
Теперь, выполнив выборку из таблицы Employees, мы увидим, что записи отсортировались по кластерному индексу UQ_Employees_Email:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	HireDate
1003	Андреев А.А.	1982-04-17	[email protected]	4	3	2015-04-08
1000	Иванов И.И.	1955-02-19	[email protected]	2	1	2015-04-08
1001	Петров П.П.	1983-12-03	[email protected]	3	3	2015-04-08
1002	Сидоров С.С.	1976-06-07	[email protected]	1	2	2015-04-08

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

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

Кластерный индекс выгодно применять к полям, по которым выборка идет наиболее часто.

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

Подытожим

данном этапе

простом виде

PRIMARY KEY – первичный ключ;
FOREIGN KEY – настройка связей и контроль ссылочной целостности данных;
UNIQUE – позволяет создать уникальность;
CHECK – позволяет осуществлять корректность введенных данных;
DEFAULT – позволяет задать значение по умолчанию;
Так же стоит отметить, что все ограничения можно удалить, используя команду «ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения».

CLUSTERED

NONCLUSTERED

Создание самостоятельных индексов

Индексы по полю или полям можно создавать следующей командой:

CREATE INDEX IDX_Employees_Name ON Employees(Name)
Так же здесь можно указать опции CLUSTERED, NONCLUSTERED, UNIQUE, а так же можно указать направление сортировки каждого отдельного поля ASC (по умолчанию) или DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
При создании некластерного индекса опцию NONCLUSTERED можно отпустить, т.к. она подразумевается по умолчанию, здесь она показана просто, чтобы указать позицию опции CLUSTERED или NONCLUSTERED в команде.

Удалить индекс можно следующей командой:

DROP INDEX IDX_Employees_Name ON Employees
Простые индексы так же, как и ограничения, можно создать в контексте команды CREATE TABLE.

Для примера снова удалим таблицу:

DROP TABLE Employees
И пересоздадим ее со всеми созданными ограничениями и индексами одной командой CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID), CONSTRAINT UQ_Employees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Напоследок вставим в таблицу наших сотрудников:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219","[email protected]",2,1,NULL), (1001,N"Петров П.П.","19831203","[email protected]",3,3,1003), (1002,N"Сидоров С.С.","19760607","[email protected]",1,2,1000), (1003,N"Андреев А.А.","19820417","[email protected]",4,3,1000)
Дополнительно стоит отметить, что в некластерный индекс можно включать значения при помощи указания их в INCLUDE. Т.е. в данном случае INCLUDE-индекс чем-то будет напоминать кластерный индекс, только теперь не индекс прикручен к таблице, а необходимые значения прикручены к индексу. Соответственно, такие индексы могут очень повысить производительность запросов на выборку (SELECT), если все перечисленные поля имеются в индексе, то возможно обращений к таблице вообще не понадобится. Но это естественно повышает размер индекса, т.к. значения перечисленных полей дублируются в индексе.

Вырезка из MSDN. Общий синтаксис команды для создания индексов
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON

Справочное руководство по MySQL. Удаление столбцов NTEXT. Прочие ограничения – UNIQUE, DEFAULT, CHECK

Синтаксис

Аргументы

Замечания

Изменение размера столбца

Блокировки и инструкция ALTER TABLE

Добавление столбцов NOT NULL в качестве операции в сети

Выполнение параллельного плана

Секционированные таблицы

Ограничения в таблицах с представлениями, привязанными к схемам

Индексы и инструкция ALTER TABLE

Репликация изменений схемы

Сжатие данных

Удаление столбцов NTEXT

Перестроение индексов в режиме «в сети».

Поддержка совместимости

Разрешения

Примеры

Добавление столбцов и ограничений

А.Добавление нового столбца

Б.Добавление столбца с ограничением

В.Добавление непроверяемого ограничения CHECK к существующему столбцу

Г.Добавление ограничения DEFAULT к существующему столбцу

Д.Добавление нескольких столбцов с ограничениями

Е.Добавление столбца, допускающего значения NULL, со значениями по умолчанию

Ж.Создание ограничения PRIMARY KEY с параметрами индекса

З.Добавление разреженного столбца

И.Добавление набора столбцов

Удаление столбцов и ограничений

А.Удаление столбца или столбцов

Б.Удаление ограничений и столбцов

В.Удаление ограничения PRIMARY KEY в режиме ONLINE

Г.Добавление и удаление ограничения FOREIGN KEY

Изменение определения столбца

А.изменение типа данных столбца.

Б.Изменение размера столбца

В.Изменение параметров сортировки столбца

Изменение определения таблицы

А.Изменение таблицы для изменения режима сжатия

Б.Модификация таблицы columnstore для изменения архивного сжатия

В.Переключение секций между таблицами

Г.Разрешение укрупнения блокировки для секционированных таблиц

Д.Настройка отслеживания изменений для таблицы

Отключение и включение ограничений и триггеров

A.Отключение и повторное включение ограничения

Б.Отключение и повторное включение триггера

О чем данный учебник

Кратко о MS SQL Server Management Studio (SSMS)

Немного теории

DDL – Data Definition Language (язык описания данных)

Первичный ключ

Подытожим

Немного про временные таблицы

Нормализация БД – дробление на подтаблицы (справочники) и определение связей

Подытожим

Прочие ограничения – UNIQUE, DEFAULT, CHECK

Немного про индексы, создаваемые при создании ограничений PRIMARY KEY и UNIQUE

Подытожим

Создание самостоятельных индексов

Подытожим

Заключение по DDL

Синтаксис SQL 2003

Ключевые слова