ALTER TABLE (Transact-SQL)

  • Статья

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

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

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 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...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 ]

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

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>

    | <filetable_option>

}
[ ; ]

-- ALTER TABLE options


<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism 
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }
<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

Аргументы

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

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

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

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

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

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

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

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

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

    • Используется в ограничении PRIMARY KEY или [FOREIGN 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 изменяемого столбца.

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

    • 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
    Точность указанного типа данных. Дополнительные сведения о допустимых значениях точности см. в разделе Точность, масштаб и длина (Transact-SQL).

  • 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 (Transact-SQL).

    ПримечаниеПримечание

    Если в инструкции 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 <drop_clustered_constraint_option>
    Указывает, что установлен один или несколько параметров удаления кластеризованного ограничения.

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

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

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

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

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

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

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

    ПримечаниеПримечание

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

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

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

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

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

    ПримечаниеПримечание

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

  • 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
      Укрупнение блокировки будет выполняться на уровне гранулярности таблицы независимо от того, секционирована таблица или нет. Это происходит так же, как в выпуске SQL Server 2005. Значение по умолчанию равно TABLE.

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

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

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

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

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

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

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

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

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

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

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

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

  • 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.

Замечания

Чтобы добавить новые строки данных, используйте 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. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.

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

Помимо выполнения операций 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 имеет следующие ограничения.

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

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

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

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

ПримечаниеПримечание

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

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

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

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

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

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

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

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

  • Синтаксис ALTER TABLE <table> REBUILD PARTITION ... перестраивает указанную секцию.

  • Синтаксис ALTER TABLE <table> REBUILD WITH... перестраивает все секции.

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

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

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

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

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

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

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

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

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

Разрешения

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

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

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

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

Примеры

Категория

Используемые элементы синтаксиса

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

ADD • PRIMARY KEY с параметрами индекса • разреженные столбцы и наборы столбцов •

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

DROP

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

изменение типа данных • изменение размера столбца • параметры сортировки

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

DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • отслеживание изменений

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

CHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

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

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

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

Следующий пример показывает добавление столбца, который допускает значения 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 '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- 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.

USE AdventureWorks2012 ; 
GO
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. Результирующий кластеризованный индекс будет иметь такое же имя, что и ограничение.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

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

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

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

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

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

USE AdventureWorks2012 ;
GO
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.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

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

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

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

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

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

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

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

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

USE AdventureWorks2012;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

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

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

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

USE AdventureWorks2012;
Go
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

Значок стрелки, используемый со ссылкой «В начало»[В начало]

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

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

В следующем примере отключается ограничение на зарплату. Параметр 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

Значок стрелки, используемый со ссылкой «В начало»[В начало]

См. также

Справочник

sys.tables (Transact-SQL)

sp_rename (Transact-SQL)

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

DROP TABLE (Transact-SQL)

sp_help (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

ALTER PARTITION FUNCTION (Transact-SQL)

EVENTDATA (Transact-SQL)