ALTER TABLE (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)Хранилище в Microsoft FabricБаза данных SQL в предварительной версии Microsoft Fabric

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

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

Таблицы на основе дисков:

• Syntax
• Examples

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

• Syntax
• Изменение таблиц Memory-Optimized

Дополнительные сведения о соглашениях о синтаксисе см. в статье Соглашения о синтаксисе в Transact-SQL.

Синтаксис для таблиц на диске

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_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 | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...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 ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS } } ]
                        )]
                    } )
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]

-- 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 ) ]
    }

<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }

<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ],
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

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

• ALTER TABLE ограничение_столбца
• ALTER TABLE определение_столбца
• ALTER TABLE определение_вычисляемого_столбца
• ALTER TABLE вариант_индекса
• ALTER TABLE table_constraint

Синтаксис для таблиц, оптимизированных для памяти

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ]
    }

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <table_index>
      | <column_index>
    } [ ,...n ]

    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }

    | SWITCH [ [ PARTITION ] source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) }

<table_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = { 0 | delay [MINUTES] } ) ]
      [ ON filegroup_name | default ]
}

Синтаксис для Azure Synapse Analytics и Parallel Data Warehouse

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF ) ] ]
}
[ ; ]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

Синтаксис для хранилища в Fabric

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

Arguments

database_name

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

schema_name

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

table_name

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

ALTER COLUMN

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

Не поддерживается изменение столбцов следующих типов:

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

• Таблица ROWGUIDCOL .

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

• Используется в статистике, созданной инструкцией CREATE STATISTICS . Пользователям необходимо выполнить удаление DROP STATISTICS статистики до ALTER COLUMN успешного выполнения. Выполните этот запрос, чтобы получить все созданные пользователем статистические данные и статистические столбцы для таблицы.

  SELECT s.name AS statistics_name,
         c.name AS column_name,
         sc.stats_column_id
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc
           ON s.object_id = sc.object_id
          AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c
           ON sc.object_id = c.object_id
          AND c.column_id = sc.column_id
  WHERE s.object_id = OBJECT_ID('<table_name>');
  

  Note

  Статистика, которая автоматически создается оптимизатором запросов, автоматически удаляется ALTER COLUMN.

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

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

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

Тип данных в столбцах text, ntext и image может быть изменен только следующими способами:

• text на varchar(max), nvarchar(max) или xml;
• ntext наvarchar(max), nvarchar(max) или xml;
• image на varbinary(max).

Некоторые изменения типа данных могут привести к изменению данных. Например, преобразование столбца типа nchar или nvarchar в столбец типа char или varchar может привести к преобразованию расширенных символов. См. описание CAST и CONVERT. Снижение точности или масштаба столбца может привести к усечению данных.

При использовании Always Encrypted (без безопасных анклавах), если измененный столбец шифруется с ENCRYPTED WITHпомощью, можно изменить тип данных на совместимый тип данных (например INT , на BIGINT), но изменить параметры шифрования нельзя.

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

При изменении столбца ядро СУБД отслеживает каждое изменение путем добавления строки в системную таблицу и маркировки предыдущего изменения столбца как удаленного столбца. В редких случаях, когда вы изменяете столбец слишком много раз, ядро СУБД может достичь ограничения размера записи. В этом случае возникает ошибка 511 или 1708. Чтобы избежать этих ошибок, периодически перестройте кластеризованный индекс в таблице или уменьшите количество изменений столбцов.

column_name

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

[ type_schema_name. ] type_name

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

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

Далее приведены критерии для аргумента type_name изменяемого столбца.

• Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.
• Аргумент type_name не может иметь значение timestamp.
• ANSI_NULL значения по умолчанию всегда ALTER COLUMNвключены; если не указано, столбец может иметь значение NULL.
• ANSI_PADDING заполнение всегда ON предназначено для ALTER COLUMN.
• Если изменяемый столбец является столбцом идентификаторов, то аргумент new_data_type должен иметь тип данных, который поддерживает свойство идентификатора.
• Текущий параметр для SET ARITHABORT не учитывается. ALTER TABLE работает так, как если ARITHABORT бы задано значение ON.

precision

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

scale

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

max

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

xml_schema_collection

Область применения: SQL Server и База данных SQL Azure.

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

Сортировка <collation_name>

Задает новые параметры сортировки для изменяемого столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Дополнительные сведения см. в разделе "Имя сортировки Windows " и "Имя сортировки SQL Server".

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

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

• Ограничение CHECK , FOREIGN KEY ограничение или вычисляемые столбцы ссылаются на измененный столбец.
• Любой индекс, статистика или полнотекстовый индекс создаются в столбце. Статистика, автоматически созданная на базе изменяемого столбца, удаляется, если изменяются параметры сортировки столбца.
• Представление или функция, привязанные к схеме, ссылается на столбец.

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

NULL | НЕ NULL

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

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

Можно указать NULL для ALTER COLUMN принудительного разрешения значений NULL столбца NOT NULL , за исключением столбцов в PRIMARY KEY ограничениях. Можно указать NOT NULL только в ALTER COLUMN том случае, если столбец не содержит значений NULL. Значения NULL необходимо обновить до некоторого ALTER COLUMNNOT 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.

[ {ADD | DROP} ROWGUIDCOL ]

Область применения: SQL Server и База данных SQL Azure.

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

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

[ {ADD | DROP} СОХРАНЕНО ]

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

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

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

DROP NOT FOR REPLICATION

Область применения: SQL Server и База данных SQL Azure.

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

SPARSE

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

ADD MASKED WITH (FUNCTION = 'mask_function')

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

Указывает маску для динамического маскирования данных. mask_function — это имя функции маскирования с соответствующими параметрами. Доступны три функции:

• default()
• email()
• partial()
• random()

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

Чтобы удалить маску, используйте DROP MASKED. Сведения о параметрах функции см. в разделе "Динамическое маскирование данных".

Добавление и удаление маски требуют ALTER ANY MASK разрешения.

WITH ( ONLINE = ON | OFF) <применительно к изменению столбца>

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

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

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

• Пока выполняется операция изменения в сети, любая операция DDL, которая может зависеть от этого столбца (например, создание или изменение индексов, представлений и т. д.), блокируется или завершается ошибкой. Такое поведение гарантирует, что изменение столбцов в оперативном режиме не завершится сбоем из-за появления новых зависимостей во время выполнения этой операции.

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

• В сети ALTER не поддерживается, если столбец ссылается на ограничение проверки, и ALTER операция ограничивает точность столбца (числовой или даты и времени).

• При изменении столбцов в оперативном режиме нельзя использовать параметр WAIT_AT_LOW_PRIORITY.

• При изменении столбцов в оперативном режиме ALTER COLUMN ... ADD/DROP PERSISTED не поддерживается.

• Изменение столбцов в оперативном режиме не влияет на ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION.

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

• Изменение столбцов в оперативном режиме не поддерживает изменение типа данных CLR на другой тип данных, или наоборот.

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

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

• Изменение в оперативном режиме для нескольких столбцов одновременно не поддерживается.

• Изменение столбцов в оперативном режиме никак не влияет на темпоральные таблицы с системным управлением версиями. ALTER Столбец не выполняется как в режиме "в сети", независимо от того, какое значение было указано для ONLINE параметра.

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

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

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. Дополнительные сведения см. в разделе "Отключение ограничений внешнего ключа" с помощью инструкций INSERT и UPDATE.

ALTER INDEX index_name

Указывает, что число контейнеров для index_name должно быть изменено.

Синтаксис ALTER TABLE ... ADD/DROP/ALTER INDEX поддерживается только для таблиц, оптимизированных для памяти.

ADD

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

ПЕРИОД SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

Область применения: SQL Server 2017 (14.x) и более поздних версий, а также база данных SQL Azure.

Указывает имена столбцов, которые система использует для обозначения периода действия записи. Можно указать существующие столбцы или создать новые столбцы в составе аргумента ADD PERIOD FOR SYSTEM_TIME . Настройте столбцы с типом данных datetime2 и определите их как NOT NULL. Если вы определяете столбец периода как NULLрезультаты ошибки. Можно определить column_constraint и/или указать значения по умолчанию для столбцов system_start_time и system_end_time столбцов. См. пример A в разделе Системное управление версиями ниже, где показано использование значения по умолчанию для столбца system_end_time.

Используйте этот аргумент с аргументом SET SYSTEM_VERSIONING для создания существующей таблицы темпоральной таблицы. Дополнительные сведения см. в разделе "Темпоральные таблицы " и " Начало работы с темпоральными таблицами".

По состоянию на SQL Server 2017 (14.x) пользователи могут пометить один или оба столбца периода флагом HIDDEN , чтобы неявно скрыть эти столбцы, чтобы SELECT * FROM <table_name> не возвращать значение для столбцов. По умолчанию столбцы периода не скрываются. Чтобы использовать скрытые столбцы, их необходимо явно указывать во всех запросах, обращающихся к темпоральной таблице.

DROP

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

ОГРАНИЧЕНИЕ constraint_name

Указывает, что из таблицы удалено ограничение constraint_name. Может быть перечислено несколько ограничений.

Вы можете определить определяемое пользователем или системное имя ограничения, запрашивая sys.check_constraintпредставления , sys.default_constraintssys.key_constraintsи sys.foreign_keys представления каталога.

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

ИНДЕКС index_name

Указывает, что index_name удалено из таблицы.

Синтаксис ALTER TABLE ...ALTER INDEXADD/DROP/поддерживается только для оптимизированных для памяти таблиц.

СТОЛБЕЦ column_name

Указывает, что constraint_name или column_name удаляется из таблицы. Можно перечислить несколько столбцов.

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

• Используется в индексе, будь то ключевой столбец или в качестве INCLUDE
• Используется в объекте , FOREIGN KEYUNIQUEили PRIMARY KEY ограниченииCHECK.
• Связан с значением по умолчанию, определенным с DEFAULT ключевым словом, или привязанным к объекту по умолчанию.
• привязан к правилу.

ПЕРИОД SYSTEM_TIME

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

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

WITH <drop_clustered_constraint_option>

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

MAXDOP = max_degree_of_parallelism

Область применения: SQL Server и База данных SQL Azure.

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

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

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

• 1

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

• >1

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

• 0 (по умолчанию)

  Использует фактическое количество процессоров или меньше на основе текущей системной рабочей нагрузки.

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

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 операторы. Многопотоковые операции перестроения кучи разрешены.

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

  Note

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

MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | файловая группа | "default" }

Область применения: SQL Server и База данных SQL Azure.

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

{ CHECK | NOCHECK } CONSTRAINT

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

• ALL

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

{ ENABLE | DISABLE } TRIGGER

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

• ALL

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

• trigger_name

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

{ ENABLE | DISABLE } CHANGE_TRACKING

Область применения: SQL Server и База данных SQL Azure.

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

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

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

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

Область применения: SQL Server и База данных SQL Azure.

Указывает, были ли обновлены ядро СУБД треки, которые изменили отслеживаемые столбцы. Значение по умолчанию — OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

Область применения: SQL Server и База данных SQL Azure.

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

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

Если таблица 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.

Секционированная таблица с кластеризованным индексом columstore ведет себя как секционированная куча.

• Первичный ключ должен содержать ключ секции.
• Уникальный индекс должен содержать ключ секции. Но при этом включение ключа секции в существующий уникальный индекс может повлиять на его уникальность.
• Для переключения секций все некластеризованные индексы должны содержать ключ секции.

Ограничения SWITCH при использовании репликации см. в разделе "Репликация секционированных таблиц и индексов".

Некластеризованные индексы columnstore были созданы в формате только для чтения до SQL Server 2016 (13.x) и для База данных SQL до версии 12. Перед выполнением любых PARTITION операций необходимо перестроить некластеризованные индексы columnstore в текущий формат (который обновляется).

Limitations

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

Пример выходных данных:

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

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

Область применения: SQL Server. База данных SQL Azure не поддерживаетFILESTREAM.

Указывает местоположения хранения данных 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 ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ] } )

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

Отключает или включает для таблицы системное управление версиями. Чтобы включить системное управление версиями таблицы, система проверяет соответствие типа данных, ограничению null и требованиям к ограничению первичного ключа для системного управления версиями. Система записывает журнал каждой записи в системной таблице с версиями в отдельной таблице журнала. HISTORY_TABLE Если аргумент не используется, имя этой таблицы журнала — MSSQL_TemporalHistoryFor<primary_table_object_id>. Если таблица журнала не существует, система создает новую таблицу журнала, соответствующую схеме текущей таблицы, создает связь между двумя таблицами и позволяет системе записывать журнал каждой записи в текущей таблице в таблице журнала. Если вы укажете аргумент HISTORY_TABLE, чтобы создать связь с уже существующей таблицей журнала, система создает связь между текущей таблицей и указанной в этом аргументе таблицей. При создании связи с существующей таблицей журнала вы можете настроить проверку согласованности данных. Проверка согласованности данных гарантирует, что существующие записи не перекрываются. Выполнение проверки согласованности данных считается вариантом по умолчанию. Используйте аргумент SYSTEM_VERSIONING = ON для таблицы, которая определена с помощью предложения PERIOD FOR SYSTEM_TIME, чтобы сделать существующую таблицу темпоральной. Дополнительные сведения см. в темпоральных таблицах.

HISTORY_RETENTION_PERIOD = { INFINITE | число { DAY | DAYS | НЕДЕЛЯ | НЕДЕЛИ | MONTH | МЕСЯЦЫ | YEAR | YEARS } }

Применимо к: SQL Server 2017 (14.x) и База данных SQL Azure.

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

DATA_DELETION

Применяется только к: Только для пограничных вычислений SQL Azure

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

• FILTER_COLUMN = { column_name }

  Указывает столбец, который следует использовать для определения того, устарели ли строки в таблице. Для столбца фильтра разрешены следующие типы данных.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | число { DAY | DAYS | НЕДЕЛЯ | НЕДЕЛИ | MONTH | МЕСЯЦЫ | YEAR | YEARS } }

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

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

Область применения: SQL Server и База данных SQL Azure.

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

• AUTO

  Этот параметр позволяет SQL Server ядро СУБД выбрать степень детализации эскалации блокировки, соответствующую схеме таблицы.

  • Если таблица секционирована, степень детализации блокировки допускается кучи или дерево B-дерева (HoBT). Другими словами, эскалация допускается на уровне секции. После эскалации блокировки на уровень HoBT блокировка не будет перерастаться позже на TABLE степень детализации.

  • Если таблица не секционирована, эскалация блокировки выполняется до TABLE детализации.

• TABLE

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

• DISABLE

  В большинстве случаев предотвращает укрупнение блокировки. Блокировки уровня таблицы нельзя запретить полностью. Например, при сканировании таблицы без кластеризованного индекса на уровне сериализуемой изоляции ядро СУБД необходимо взять блокировку таблицы для защиты целостности данных.

REBUILD

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

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

PARTITION = ALL

Область применения: SQL Server и База данных SQL Azure.

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

ПЕРЕСТРОЕНИЕ WITH ( <rebuild_option> )

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

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

Полное описание параметров перестроения см. в статье ALTER TABLE index_option.

DATA_COMPRESSION

Область применения: SQL Server и База данных SQL Azure.

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

• NONE

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

• РЯД

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

• СТРАНИЦА

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

• COLUMNSTORE

  Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

• COLUMNSTORE_ARCHIVE

  Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

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

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW или PAGE не разрешено в базе данных SQL в Предварительной версии Microsoft Fabric.

XML_COMPRESSION

Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Задает параметр сжатия XML для всех столбцов с типом данных xml в таблице. Существуют следующие варианты выбора.

• ON

  Столбцы, использующие тип данных xml, сжимаются.

• OFF

  Столбцы, использующие тип данных xml, не сжимаются.

ONLINE = { ON | OFF } <как применимо к single_partition_rebuild_option>

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

• ON

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

  Note

  При перестроении индекса в режиме "в сети" могут быть заданы параметры low_priority_lock_wait, описанные ниже в этом разделе.

• OFF

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

column_set_name XML-COLUMN_SET FOR ALL_SPARSE_COLUMNS

Область применения: SQL Server и База данных SQL Azure.

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

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

Область применения: SQL Server.

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

SET ( FILETABLE_DIRECTORY = directory_name )

Область применения: SQL Server. База данных SQL Azure не поддерживает FileTable.

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

REMOTE_DATA_ARCHIVE

Область применения: SQL Server 2017 (14.x) и более поздних версий.

Включает или отключает Stretch Database для таблицы. Дополнительные сведения см. в разделе Stretch Database.

Включение Stretch Database для таблицы

Если вы включаете Stretch для таблицы, указывая ON, также нужно указать MIGRATION_STATE = OUTBOUND, чтобы сразу же приступить к миграции данных, или MIGRATION_STATE = PAUSED, чтобы отложить миграцию. Значение по умолчанию — MIGRATION_STATE = OUTBOUND. Более подробную информацию о включении Stretch для таблицы см. в статье Включение Stretch Database для таблицы.

Prerequisites. Прежде чем включить Stretch для таблицы, необходимо включить Stretch на сервере и в базе данных. Дополнительные сведения см. в статье Включение Stretch Database для базы данных.

Permissions. Чтобы включить Stretch для таблицы или базы данных, требуются права db_owner. Для включения Stretch для таблицы также требуются ALTER разрешения для таблицы.

Отключение Stretch Database для таблицы

При отключении Stretch для таблицы у вас есть два варианта управления удаленными данными, уже перенесенными в Azure. Дополнительные сведения см. в статье Отключение Stretch Database и возврат удаленных данных.

• Чтобы отключить Stretch для таблицы и скопировать удаленные данные для таблицы из Azure обратно в SQL Server, выполните следующую команду. Эту команду нельзя отменить.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
  

Эта операция предусматривает расходы на передачу данных и не может быть отменена. Дополнительные сведения см. на странице сведений о ценах на передачу данных.

После копирования всех удаленных данных из Azure в SQL Server база данных Stretch для таблицы будет отключена.

• Чтобы отключить растяжение для таблицы и оставить удаленные данные в Azure, выполните следующую команду.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
  

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

Отключение Stretch Database не приводит к стиранию удаленной таблицы. Если вам нужно стереть удаленную таблицу, воспользуйтесь порталом Azure.

[ FILTER_PREDICATE = { null | предикат } ]

Область применения: SQL Server 2017 (14.x) и более поздних версий.

Дополнительно указывает предикат фильтра для выбора строк для миграции из таблицы, которая содержит данные журнала и текущие данные. Этот предикат должен вызывать детерминированную встроенную функцию с табличным значением. Дополнительные сведения см. в статьях Включение Stretch Database для таблицы и Выбор строк для миграции с использованием функции фильтров (Stretch Database).

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

При указании предиката фильтра также необходимо указать MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | ВХОДЯЩИЙ ТРАФИК | PAUSED }

Область применения: SQL Server 2017 (14.x) и более поздних версий.

• Укажите OUTBOUND для миграции данных с SQL Server на Azure.

• Укажите INBOUND для копирования удаленных данных для таблицы из Azure обратно в SQL Server с отключением Stretch для таблицы. Дополнительные сведения см. в статье Отключение Stretch Database и возврат удаленных данных.

  Эта операция предусматривает расходы на передачу данных и не может быть отменена.

• Укажите PAUSED для приостановки миграции данных. Дополнительные сведения см. в статье Приостановка и возобновление переноса данных (Stretch Database).

WAIT_AT_LOW_PRIORITY

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

MAX_DURATION = время [ МИНУТы ]

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

• NONE

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

• SELF

  Выйдите из операции перестроения индекса DDL, выполняемой SWITCH в данный момент, не выполняя никаких действий.

• BLOCKERS

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

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

ЕСЛИ СУЩЕСТВУЕТ

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

Условное удаление столбца или ограничения в том случае, если они существуют.

RESUMABLE = { ON | OFF}

Область применения: SQL Server 2022 (16.x) и более поздних версий.

Указывает, является ли операция ALTER TABLE ADD CONSTRAINT возобновляемой. Операция добавления табличного ограничения будет возобновляемой при значении ON. При добавлении OFFоперации ограничения таблицы невозможно возобновить. По умолчанию — OFF. Параметр RESUMABLE можно использовать как часть ALTER TABLE index_option в ALTER TABLE table_constraint.

MAX_DURATION При использовании с RESUMABLE = ON (требуется ONLINE = ON) указывает время (целочисленное значение, указанное в минутах), которое выполняется повторной операцией ограничения в сети перед приостановкой. Если этот параметр не указан, операция продолжается вплоть до завершения.

Дополнительные сведения о включении и использовании повторно выполняемых ALTER TABLE ADD CONSTRAINT операций см. в разделе "Повторное добавление ограничений таблицы".

Remarks

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

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

В базе данных SQL в Microsoft Fabric Preview можно создать некоторые функции таблицы, но не зеркально отображаться в Fabric OneLake. Дополнительные сведения см. в разделе "Ограничения для зеркального отображения базы данных SQL Fabric" (предварительная версия).

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

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

Применяется к хранилищу в Microsoft Fabric.

ALTER TABLE не может быть частью явной транзакции.

Расширенные события (XEvents) для коммутатора секционирования

Следующие XEvents связаны с ALTER TABLE ... SWITCH PARTITION перестроениями индексов в сети.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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

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

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

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

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

В выпуске SQL Server 2012 (11.x) Enterprise и более поздних версиях число процессоров, используемых для выполнения одной ALTER TABLE ADD (индексной) CONSTRAINT или DROP (кластеризованной) инструкции, CONSTRAINT определяется параметром конфигурации параллелизма и текущей рабочей нагрузкой. Если ядро СУБД обнаруживает, что система занята, степень параллелизма операции автоматически уменьшается до запуска инструкции. Можно вручную настроить количество процессоров, используемых для запуска инструкции, указав MAXDOP этот параметр. Дополнительные сведения см. в разделе "Конфигурация сервера": максимальная степень параллелизма.

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

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

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

Ограничения, применяемые к ALTER TABLE операторам в таблицах с представлениями с привязкой к схеме, совпадают с ограничениями, применяемыми в настоящее время при изменении таблиц с простым индексом. Добавление столбца разрешено. Но не разрешается удаление или изменение столбца, участвующего в любом из представлений, привязанных к схемам. ALTER TABLE Если оператор требует изменения столбца, используемого в представлении с привязкой к схеме, происходит сбой, ALTER TABLE и ядро СУБД вызывает сообщение об ошибке. Дополнительные сведения о привязке к схеме и индексированных представлениях см. в описании CREATE VIEW.

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

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

При удалении ограничений индексы, создаваемые как часть ограничения, удаляются. С помощью индексов, созданных с CREATE INDEXDROP 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 не является допустимым для индексированных представлений, некластеризованных индексов или индексов в локальных временных таблицах.
• ONLINE = ON недопустимо для индексов columnstore.

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

Реплицировать изменения схемы

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

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

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

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

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

• Если в таблице есть невыровненные индексы, настройку сжатия для отдельной секции изменить нельзя.
• Синтаксис ALTER TABLE <table> REBUILD PARTITION ... перестраивает указанную секцию.
• Синтаксис ALTER TABLE <table> REBUILD WITH ... перестраивает все секции.

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

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

Перестроение индекса в сети

Чтобы выполнить инструкцию DDL для перестроения индекса в оперативном режиме, необходимо завершить все активные блокирующие транзакции, выполняемые для соответствующей таблицы. Запуск перестроения индекса в оперативном режиме блокирует все новые транзакции, которые готовы к выполнению для этой таблицы. Хотя продолжительность блокировки для перестроения индекса в оперативном режиме очень невелика, ожидание завершения всех открытых транзакций по таблице и блокировка новых транзакций может заметно отразиться на пропускной способности. Это может замедлить выполнение рабочей нагрузки или привести к превышению времени ожидания, существенно ограничивая доступ к базовой таблице. Этот WAIT_AT_LOW_PRIORITY параметр позволяет базам данных управлять блокировками S-lock и Sch-M, необходимыми для перестроения индексов в сети. Во всех трех случаях: NONE, SELFи BLOCKERS, если во время ожидания ((MAX_DURATION = n [minutes])) нет блокирующих действий, перестроение индекса в сети выполняется немедленно, не ожидая, и инструкция DDL завершена.

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

Инструкция ALTER TABLE поддерживает только две части (schema.object) имена таблиц. В SQL Server указание имени таблицы с использованием следующих форматов завершается сбоем во время компиляции с ошибкой 117.

• server.database.schema.table
• .database.schema.table
• ..schema.table

В предыдущих версиях при задании формата server.database.schema.table возникала ошибка 4902. Указание формата .database.schema.table или ..schema.table завершилось успешно.

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

Permissions

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

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

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

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

Examples

Примеры кода в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать на домашней странице microsoft SQL Server Samples and Community Projects .

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

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

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

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

CREATE TABLE dbo.doc_exa (column_a INT);
GO

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

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

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

EXECUTE sp_help doc_exc;
GO

DROP TABLE dbo.doc_exc;
GO

C. Добавление непроверенного ограничения 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

EXECUTE sp_help doc_exd;
GO

DROP TABLE dbo.doc_exd;
GO

D. Добавление ограничения 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

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

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

CREATE TABLE dbo.doc_exe
(
    column_a INT
        CONSTRAINT column_a_un UNIQUE
);
GO

ALTER TABLE dbo.doc_exe

    -- Add a PRIMARY KEY identity column.
    ADD 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 FOREIGN KEY 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

EXECUTE sp_help doc_exe;
GO

DROP TABLE dbo.doc_exe;
GO

F. Добавление столбца, допускаемого значением 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
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

G. Создание ограничения PRIMARY KEY с параметрами сжатия индекса или данных

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

Область применения: SQL Server и База данных SQL Azure.

USE AdventureWorks2022;
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

В этом аналогичном примере применяется сжатие страниц и кластеризованный первичный ключ.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (DATA_COMPRESSION = PAGE);
GO

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

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

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

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

J. Добавление зашифрованного столбца

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

ALTER TABLE Customers
    ADD PromotionCode NVARCHAR (100)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        );

K. Добавление первичного ключа с повторной операцией

Возобновляемая операция ALTER TABLE для добавления первичного ключа, кластеризованного по столбцу (a) со значением параметра MAX_DURATION, равным 240 минутам.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

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

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

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

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

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;

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

В первом примере из таблицы удаляется ограничение 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 my_constraint, my_pk_constraint, COLUMN column_b;
GO

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

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

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

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

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

CREATE TABLE Person.ContactBackup (ContactID INT);
GO

ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT FK_ContactBackup_Contact
        FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID);
GO

ALTER TABLE Person.ContactBackup
    DROP CONSTRAINT FK_ContactBackup_Contact;
GO

DROP TABLE Person.ContactBackup;

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

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

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

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

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

-- 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');

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

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

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

D. Шифрование столбца

В следующем примере показано, как зашифровать столбец с помощью Always Encrypted с безопасными анклавами.

Первым делом создается таблица без зашифрованных столбцов.

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

После этого столбец C2 шифруется с помощью ключа шифрования с именем CEK1 методом случайного шифрования. Чтобы следующая инструкция была выполнена успешно, должны соблюдаться следующие условия:

• Ключ шифрования столбцов должен поддерживать анклав. Это означает, что он должен быть зашифрован с помощью главного ключа столбца (CMK), который позволяет вычисления анклава.
• Целевой экземпляр SQL Server должен поддерживать Always Encrypted с безопасными анклавами.
• Оператор следует передавать через подключение, настроенное для Always Encrypted с безопасными анклавами и применяемое поддерживаемый драйвер клиента.
• Вызывающее приложение должно иметь доступ к CMK, обеспечивая CEK1защиту.

ALTER TABLE T3 ALTER COLUMN C2 VARCHAR (50)  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK1],
     ENCRYPTION_TYPE = RANDOMIZED,
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL;
GO

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

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

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

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

ALTER TABLE T1 REBUILD
    WITH (DATA_COMPRESSION = PAGE);

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

Область применения: SQL Server.

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

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

Область применения: SQL Server.

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

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

B. Изменение таблицы columnstore для изменения архивного сжатия

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

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

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

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

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

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

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

D. Разрешить эскалацию блокировки в секционированных таблицах

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

Область применения: SQL Server и База данных SQL Azure.

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

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

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

Область применения: SQL Server и База данных SQL Azure.

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

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

Область применения: SQL Server.

USE AdventureWorks2022;
GO

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

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

Область применения: SQL Server и База данных SQL Azure.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

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

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 fails.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4, 'Eric James', 110000);

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

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

Операции в сети

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

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

Область применения: SQL Server 2014 (12.x) и более поздних версий, а также база данных SQL Azure.

ALTER TABLE T1 REBUILD WITH (
    PAD_INDEX = ON,
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    )
);

B. Изменение столбца в режиме "в сети"

В следующем примере показано, как выполнить операцию alter column с параметром ONLINE .

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

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) WITH (ONLINE = ON);
GO

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

Управление версиями системы

В следующих четырех примерах вы ознакомитесь с синтаксисом для использования системного управления версиями. Дополнительные сведения см. в статье "Начало работы с системными темпоральными таблицами".

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.

A. Добавление системного управления версиями в существующие таблицы

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

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.99999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_RETENTION_PERIOD=1 YEAR
    )
);

B. Изменение существующего решения для использования системного управления версиями

В следующем примере показан переход на системное управление версиями из решения, использующего триггеры для имитации временной поддержкой. В примере предполагается, что существует существующее решение, использующее ProjectTask таблицу и ProjectTaskHistory таблицу для существующего решения, которое использует Changed Date и Revised Date столбцы для его периодов, что эти столбцы периодов не используют тип данных datetime2 и ProjectTask что таблица имеет первичный ключ.

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
    ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date]);

ALTER TABLE ProjectTask SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE=dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK=ON
    )
);

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

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

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int DEFAULT NOT NULL 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int DEFAULT NOT NULL 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. Удаление системного управления версиями

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

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

В следующих примерах A через C используется FactResellerSales таблица в базе данных AdventureWorksPDW2022.

A. Определение секционирования таблицы

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

SELECT *
FROM sys.partitions AS p
     INNER JOIN sys.tables AS t
         ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
      AND t.name = 'FactResellerSales';

B. Определение значений границ для секционированных таблиц

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

SELECT t.name AS TableName,
       i.name AS IndexName,
       p.partition_number,
       p.partition_id,
       i.data_space_id,
       f.function_id,
       f.type_desc,
       r.boundary_id,
       r.value AS BoundaryValue
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER JOIN sys.partition_range_values AS r
         ON f.function_id = r.function_id
        AND r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
ORDER BY p.partition_number;

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

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

SELECT t.object_id AS Object_ID,
       t.name AS TableName,
       ic.column_id AS PartitioningColumnID,
       c.name AS PartitioningColumnName
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
      AND c.column_id = ic.column_id;

D. Объединение двух разделов

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

Таблица Customer имеет следующее определение:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Следующая команда объединяет границы секций 10 и 25.

ALTER TABLE Customer MERGE RANGE (10);

Новый DDL для таблицы:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 25, 50, 100)
    )
);

E. Разделение секции

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

Таблица Customer имеет следующий DDL:

DROP TABLE Customer;

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Следующая команда создает новую секцию, ограниченную значением 75, от 50 до 100.

ALTER TABLE Customer SPLIT RANGE (75);

Новый DDL для таблицы:

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. Использование SWITCH для перемещения секции в таблицу журнала

В следующем примере выполняется перемещение данных в секции таблицы Orders в секцию в таблице OrdersHistory.

Таблица Orders имеет следующий DDL:

CREATE TABLE Orders
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01')
    )
);

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

• Раздел 1 (имеет данные): OrderDate < '2004-01-01'
• Раздел 2 (имеет данные): '2004-01-01' <= OrderDate < '2005-01-01'
• Раздел 3 (имеет данные): '2005-01-01' <= OrderDate< '2006-01-01'
• Раздел 4 (имеет данные): '2006-01-01'<= OrderDate < '2007-01-01'
• Раздел 5 (имеет данные): '2007-01-01' <= OrderDate

Таблица OrdersHistory имеет следующий DDL со столбцами и именами столбцов, идентичными столбцам и именам столбцов в таблице Orders. Они имеют распределенный хэш в столбце id.

CREATE TABLE OrdersHistory
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01')
    )
);

Хотя форматы и имена столбцов должны быть одинаковыми, границы секций могут не совпадать. В этом примере таблица OrdersHistory содержит следующие две секции, которые пусты:

• Раздел 1 (без данных): OrderDate < '2004-01-01'
• Секция 2 (пустая): '2004-01-01' <= OrderDate

Для предыдущих двух таблиц следующая команда перемещает все строки с OrderDate < '2004-01-01' из таблицы Orders в таблицу OrdersHistory.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

В результате первая секция в Orders пуста, а первая секция в OrdersHistory содержит данные. Теперь таблицы отображаются следующим образом:

Таблица Orders

• Секция 1 (пустая): OrderDate < '2004-01-01'
• Раздел 2 (имеет данные): '2004-01-01' <= OrderDate < '2005-01-01'
• Раздел 3 (имеет данные): '2005-01-01' <= OrderDate< '2006-01-01'
• Раздел 4 (имеет данные): '2006-01-01'<= OrderDate < '2007-01-01'
• Раздел 5 (имеет данные): '2007-01-01' <= OrderDate

Таблица OrdersHistory

• Раздел 1 (имеет данные): OrderDate < '2004-01-01'
• Секция 2 (пустая): '2004-01-01' <= OrderDate

Чтобы очистить Orders таблицу, можно удалить пустую секцию, объединив секции 1 и 2 выполнив следующие действия.

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

После объединения таблица Orders содержит следующие разделы.

Таблица Orders

• Раздел 1 (имеет данные): OrderDate < '2005-01-01'
• Раздел 2 (имеет данные): '2005-01-01' <= OrderDate< '2006-01-01'
• Раздел 3 (имеет данные): '2006-01-01'<= OrderDate < '2007-01-01'
• Раздел 4 (имеет данные): '2007-01-01' <= OrderDate

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

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

После разделения таблица OrdersHistory содержит следующие секции.

Таблица OrdersHistory

• Раздел 1 (имеет данные): OrderDate < '2004-01-01'
• Секция 2 (пустая): '2004-01-01' < '2005-01-01'
• Раздел 3 (пустой): '2005-01-01' <= OrderDate

Связанный контент

• sys.tables
• sp_rename
• sp_help
• EVENTDATA (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• ALTER TABLE column_constraint (Transact-SQL)
• ALTER TABLE column_definition (Transact-SQL)
• ALTER TABLE computed_column_definition (Transact-SQL)
• ALTER TABLE index_option (Transact-SQL)
• ALTER TABLE table_constraint (Transact-SQL)