ALTER TABLE (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure хранилище платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric
Изменяет определение таблицы путем изменения, добавления или удаления столбцов и ограничений. Также ALTER TABLE переназначает и перестраивает секции или отключает и включает ограничения и триггеры.
Примечание.
ALTER TABLE В настоящее время в хранилище Fabric поддерживается только ограничения и добавление столбцов, допускающих значение NULL. См . синтаксис для хранилища в Fabric.
Внимание
Инструкция ALTER TABLE имеет разный синтаксис для таблиц на диске и таблиц, оптимизированных для памяти. Воспользуйтесь ссылками ниже, которые ведут непосредственно к описаниям соответствующих синтаксических блоков для ваших типов таблиц и соответствующим примерам:
Дополнительные сведения о соглашениях о синтаксисе см. в статье Соглашения о синтаксисе в 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 { 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
-- Syntax for Azure Synapse Analytics and 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 }
}
Примечание.
Бессерверный пул SQL в Azure Synapse Analytics поддерживает только внешние и временные таблицы.
Синтаксис для хранилища в Fabric
-- Syntax for Warehouse om Microsoft 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 { [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
}
Аргументы
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>');Примечание.
Статистика, автоматически сформированная оптимизатором запросов, автоматически удаляется инструкцией ALTER COLUMN.
Используется в ограничении PRIMARY KEY или [FOREIGN KEY] REFERENCES.
Используется в ограничениях CHECK или UNIQUE. При этом допускается изменение длины столбца изменяемой длины, используемого в ограничении CHECK или UNIQUE.
Связано с определением по умолчанию. Если же тип данных не изменяется, можно изменить длину, точность или масштаб столбца.
Тип данных в столбцах text, ntext и image может быть изменен только следующими способами:
- text на varchar(max), nvarchar(max) или xml;
- ntext наvarchar(max), nvarchar(max) или xml;
- image на varbinary(max).
Некоторые изменения типов данных могут повлечь за собой изменения в данных. Например, преобразование столбца типа nchar или nvarchar в столбец типа char или varchar может привести к преобразованию расширенных символов. См. описание CAST и CONVERT. Снижение точности или масштаба столбца может привести к усечению данных.
Примечание.
Нельзя изменить тип данных для столбца секционированной таблицы.
Нельзя изменить тип данных для столбцов, включенных в индекс, кроме столбцов с типами данных varchar, nvarchar или varbinary, если новый размер больше старого или равен ему.
Для столбцов, включенных в ограничение первичного ключа, нельзя изменить условие NOT NULL на NULL.
Если при использовании функции 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 всегда установлено значение ON; если не указано иное, столбец может содержать значения NULL.
- Аргумент заполнения ANSI_PADDING для инструкции ALTER COLUMN всегда принимает значение ON.
- Если изменяемый столбец является столбцом идентификаторов, то аргумент new_data_type должен иметь тип данных, который поддерживает свойство идентификатора.
- Текущая установка для аргумента SET ARITHABORT пропускается. Инструкция ALTER TABLE функционирует аналогично случаю, когда для аргумента ARITHABORT установлено значение ON.
Примечание.
Если предложение COLLATE не указывается, изменение типа данных для столбца приведет к изменению параметров сортировки на те, которые установлены для базы данных по умолчанию.
precision
Точность указанного типа данных. Дополнительные сведения о допустимых значениях точности см. в разделе Точность, масштаб и длина.
scale
Масштаб указанного типа данных. Дополнительные сведения о допустимых значениях масштаба см. в разделе Точность, масштаб и длина.
макс.
Применяется только к типам данных varchar, nvarchar и varbinary для хранения 2^31-1 байт символьных, двоичных данных и данных в Юникоде.
xml_schema_collection
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Применяется только к данным типа xml для связывания схемы XML с этим типом. Прежде чем включать столбец xml в коллекцию схемы, необходимо создать коллекцию схемы в базе данных с помощью инструкции CREATE XML SCHEMA COLLECTION.
COLLATE <collation_name>
Задает новые параметры сортировки для изменяемого столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Список и дополнительные сведения см. в статьях Имя параметра сортировки Windows (Transact-SQL) и Имя параметра сортировки SQL Server (Transact-SQL).
Предложение COLLATE изменяет параметры сортировки только для столбцов с типами данных char, varchar, nchar и nvarchar. Чтобы изменить параметры сортировки столбца типа данных определяемого пользователем псевдонима, используйте отдельные инструкции ALTER TABLE, чтобы изменить столбец на системный тип данных SQL Server. Затем измените параметры сортировки и снова преобразуйте столбец в прежний тип данных.
Инструкция ALTER COLUMN не может изменить параметры сортировки, если выполняется одно или несколько из следующих условий:
- Если на изменяемый столбец ссылается ограничение CHECK, ограничение FOREIGN KEY или вычисляемые столбцы.
- Если на базе столбца создан какой-нибудь индекс, статистика или полнотекстовый индекс. Статистика, автоматически созданная на базе изменяемого столбца, удаляется, если изменяются параметры сортировки столбца.
- Если связанное со схемой представление или функция ссылаются на столбец.
Дополнительные сведения см. в описании COLLATE.
NULL | NOT NULL
Указывает, может ли столбец принимать значения NULL. Столбцы, не допускающие значения NULL, могут быть добавлены инструкцией ALTER TABLE, только если для них указаны значения по умолчанию или если таблица пуста. Вы можете указать NOT NULL для вычисляемых столбцов только в том случае, если вы также указали PERSISTED. Если новый столбец допускает значения NULL, а значение по умолчанию не задано, новый столбец получает значение NULL для каждой строки в таблице. Если новый столбец допускает значение NULL и с новым столбцом добавляется определение по умолчанию, вы можете поместить в новый столбец значения по умолчанию для каждой строки в таблице с помощью аргумента WITH VALUES.
Если новый столбец не допускает значения NULL и таблица не пуста, вместе с новым столбцом необходимо добавить определение DEFAULT. В этом случае новый столбец автоматически получит значение по умолчанию для каждой существующей строки.
Вы можете указать значение NULL в инструкции ALTER COLUMN, чтобы столбец NOT NULL мог принимать значения NULL, если он не включен в ограничения PRIMARY KEY. Условие NOT NULL можно указывать в инструкции ALTER COLUMN только для тех столбцов, которые не содержат значения NULL. Значения NULL следует обновить, присвоив некоторые значения, прежде чем разрешить инструкцию ALTER COLUMN NOT NULL, например:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;
Когда вы создаете или изменяете таблицу с помощью инструкций CREATE TABLE или ALTER TABLE, допустимость значений NULL для типа данных, указанного в определении столбца, зависит от параметров базы данных и сеанса или даже переопределяется ими. Для всех невычисляемых столбцов явно указывайте атрибут NULL или NOT NULL.
Добавляя столбец с пользовательским типом данных, обязательно определите для этого столбца допустимость значений NULL, как указано в соответствующем пользовательском типе данных. Кроме того, укажите значение по умолчанию для этого столбца. Дополнительные сведения см. в разделе CREATE TABLE.
Примечание.
Если в инструкции ALTER COLUMN указано значение NULL или NOT NULL, то необходимо также указать параметры new_data_type [(precision [, scale ])]. Если тип данных, точность или масштаб не изменялись, укажите текущие значения столбца.
[ {ADD | DROP} ROWGUIDCOL ]
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает, что свойство ROWGUIDCOL добавляется к указанному столбцу или удаляется из него. Свойство ROWGUIDCOL указывает, что данный столбец является столбцом идентификатора GUID строки. В каждой таблице только один столбец типа uniqueidentifier может иметь свойство ROWGUIDCOL. Кроме того, свойство ROWGUIDCOL можно присвоить только столбцу типа uniqueidentifier. Вы не можете присвоить свойство ROWGUIDCOL столбцу с пользовательским типом данных.
Свойство ROWGUIDCOL не обеспечивает уникальность значений, хранимых в столбце, и не формирует автоматически значения для новых строк, вставляемых в таблицу. Чтобы создавать уникальные значения для каждого столбца, примените функцию NEWID или NEWSEQUENTIALID в инструкции INSERT. Также вы можете указать функцию NEWID или NEWSEQUENTIALID как значение по умолчанию для столбца.
[ {ADD | DROP} PERSISTED ]
Указывает, что свойство PERSISTED добавлено к указанному столбцу или удалено из него. Этот столбец должен быть вычисляемым столбцом, который заполняется детерминированным выражением. Для столбцов, указанных как PERSISTED, ядро СУБД физически сохраняет вычисляемые значения в таблице и обновляет значения при обновлении любых других столбцов, от которых зависит вычисляемый столбец. Если пометить вычисляемый столбец как PERSISTED, можно создавать индексы по вычисляемым столбцам, которые заданы, являются детерминированными, но не точными выражениями. Дополнительные сведения см. в разделе Индексы вычисляемых столбцов.
SET QUOTED_IDENTIFIER при создании или изменении индексов в вычисляемых столбцах или индексированных представлениях должно быть включено. Дополнительные сведения см. в статье SET QUOTED_IDENTIFIER (Transact-SQL).
Любой вычисляемый столбец, который используется как столбец секционирования для секционированной таблицы, должен быть явно помечен с помощью атрибута PERSISTED.
DROP NOT FOR REPLICATION
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает, что значения в столбцах идентификаторов увеличиваются при выполнении агентами репликации операций по вставке строк. Вы можете указать это предложение, только если column_name является столбцом идентификаторов.
SPARSE
Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Разреженные столбцы не могут иметь свойство NOT NULL. При преобразовании столбца из разреженного в nonsparse или из непарса в разреженный параметр блокирует таблицу в течение длительности выполнения команды. Возможно, потребуется использование предложения REBUILD для освобождения пространства. Дополнительные ограничения и сведения о разреженных столбцах см. в разделе Разреженные столбцы.
ADD MASKED WITH ( FUNCTION = ' mask_function ')
Область применения: SQL Server (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 (SQL Server 2016 (13.x) и более поздних версий) и База данных SQL Azure.
Позволяет выполнять разные действия по изменению столбцов с сохранением доступности таблицы. По умолчанию — OFF. Изменение столбцов можно выполнять в оперативном режиме, если эти изменения связаны с типом данных, длиной или точностью столбцов, допустимостью значений NULL, разреженностью и параметрами сортировки.
Изменение столбцов в оперативном режиме позволяет использовать изменяемые столбцы в пользовательской и автоматической статистике на всем протяжении операции ALTER COLUMN, то есть не влияет на обычный режим выполнения запросов. В конце операции автоматическая статистика, которая ссылается на эти столбцы, удаляется, а созданная пользователем статистика становится недействительной. Пользователь должен вручную обновить созданную пользователем статистику после завершения операции. Если столбцы являются частью выражения фильтра для статистических данных или индексов, изменить такие столбцы нельзя.
- Во время изменения столбцов в оперативном режиме все операции, имеющие зависимость от этих столбцов (индексирование, представления и т. д.), будут блокироваться или завершаться с соответствующей ошибкой. Такое поведение гарантирует, что изменение столбцов в оперативном режиме не завершится сбоем из-за появления новых зависимостей во время выполнения этой операции.
- Изменение для столбцов свойства NOT NULL на NULL в оперативном режиме не поддерживается, если изменяемые столбцы используются в некластеризованных индексах.
- Изменение ALTER в оперативном режиме не поддерживается для столбцов, которые указаны в ограничении CHECK, если такая операция ALTER снизит точность этих столбцов (содержащих число или дату и время).
- При изменении столбцов в оперативном режиме нельзя использовать параметр
WAIT_AT_LOW_PRIORITY. - При изменении столбцов в оперативном режиме
ALTER COLUMN ... ADD/DROP PERSISTEDне поддерживается. - Изменение столбцов в оперативном режиме не влияет на
ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION. - Изменение столбцов в оперативном режиме не позволяет изменить таблицу, для которой включено отслеживание изменений или которая является издателем репликации слиянием.
- Изменение столбцов в оперативном режиме не поддерживает изменение типа данных CLR на другой тип данных, или наоборот.
- Изменение столбцов в оперативном режиме не поддерживает изменение на тип данных XML, у которого коллекция схем отличается от текущей коллекции схем.
- Изменение столбцов в оперативном режиме не снижает другие ограничения, существующие для этой операции. Ссылки из индексов, статистических данных и т. п. могут привести к сбою изменения.
- Изменение в оперативном режиме для нескольких столбцов одновременно не поддерживается.
- Изменение столбцов в оперативном режиме никак не влияет на темпоральные таблицы с системным управлением версиями. Операция ALTER COLUMN никогда не выполняется в оперативном режиме, независимо от значения параметра 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 поддерживается только для таблиц, оптимизированных для памяти.
Внимание
Инструкции CREATE INDEX, DROP INDEX, ALTER INDEX и PAD_INDEX не будут работать с индексами в таблицах, оптимизированных для памяти, если не используется инструкция ALTER TABLE.
ADD
Указывает, что добавляется одно или несколько определений столбца, определений вычисляемого столбца или ограничений таблиц. Или же добавляются столбцы, которые система использует для системного управления версиями. Для таблиц, оптимизированных для памяти, можно добавить индекс.
Примечание.
Новые столбцы добавляются после изменения всех существующих столбцов в таблице.
Внимание
Инструкции CREATE INDEX, DROP INDEX, ALTER INDEX и PAD_INDEX не будут работать с индексами в таблицах, оптимизированных для памяти, если не применить инструкцию ALTER TABLE.
PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
Область применения: SQL Server (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 Azure.
В версии SQL Server 2017 (14.x) пользователи могут пометить один или оба столбца периода флагом HIDDEN, и тогда эти столбцы будут неявно скрыты, то есть инструкция SELECT * FROM <имя_таблицы> не будет возвращать значения этих столбцов. По умолчанию столбцы периода не скрываются. Чтобы использовать скрытые столбцы, их необходимо явно указывать во всех запросах, обращающихся к темпоральной таблице.
DROP
Указывает, что удаляется одно или несколько определений столбца, определений вычисляемого столбца или ограничений таблиц либо удаляется спецификация столбцов, которые будут использоваться для системного управления версиями.
Примечание.
Столбцы, удаленные в таблицах реестра, удаляются только обратимо. Удаленный столбец остается в таблице реестра, но он помечается как удаленный столбец, задав dropped_ledger_table столбец в sys.tables 1значение . Представление реестра для таблицы удаленного реестра также помечается как удаленное путем установки столбца dropped_ledger_view в sys.tables 1значение . Удаленная таблица реестра, таблица журнала и его представление реестра переименованы путем добавления префикса (MSSQL_DroppedLedgerTable, MSSQL_DroppedLedgerHistory, MSSQL_DroppedLedgerView) и добавления GUID к исходному имени.
CONSTRAINT constraint_name
Указывает, что из таблицы удалено ограничение constraint_name. Может быть перечислено несколько ограничений.
Вы можете определить определяемое пользователем или системное имя ограничения, запрашивая sys.check_constraintпредставления , sys.default_constraintssys.key_constraintsи sys.foreign_keys представления каталога.
Невозможно удалить ограничение PRIMARY KEY, если для таблицы существует XML-индекс.
INDEX index_name
Указывает, что index_name удалено из таблицы.
Синтаксис ALTER TABLE ... ADD/DROP/ALTER INDEX поддерживается только для таблиц, оптимизированных для памяти.
Внимание
Инструкции CREATE INDEX, DROP INDEX, ALTER INDEX и PAD_INDEX не будут работать с индексами в таблицах, оптимизированных для памяти, если не используется инструкция ALTER TABLE.
COLUMN column_name
Указывает, что constraint_name или column_name удаляется из таблицы. Можно перечислить несколько столбцов.
Столбец невозможно удалить, если для него справедливо любое из следующих условий:
- Используется в индексе — как ключевой столбец или как INCLUDE.
- используется в ограничениях CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY;
- связан со значением по умолчанию, которое определено с ключевым словом DEFAULT или привязано к объекту по умолчанию;
- привязан к правилу.
Примечание.
При удалении столбца занимаемое им место на диске не освобождается. В том случае, если размер строк таблицы приближается к пределу или превышает его, возможен возврат места, занятого на диске удаленным столбцом. Возврат пространства осуществляется путем создания кластеризованного индекса в таблице или перестроения существующего кластеризованного индекса при помощи инструкции ALTER INDEX. Дополнительные сведения о последствиях удаления типов данных больших двоичных объектов см. в этой записи в блоге CSS.
PERIOD FOR SYSTEM_TIME
Область применения: SQL Server (SQL Server 2016 (13.x) и более поздних версий) и База данных SQL Azure.
Удаляет спецификацию для столбцов, которые будут использоваться для системного управления версиями.
WITH <drop_clustered_constraint_option>
Указывает, что установлен один или несколько параметров удаления кластеризованного ограничения.
MAXDOP = max_degree_of_parallelism
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Переопределяет параметр конфигурации max degree of parallelism только на время выполнения операции. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.
Используйте параметр MAXDOP для ограничения числа процессоров, применяемых при выполнении параллельных планов. Максимальное число процессоров — 64.
max_degree_of_parallelism может принимать одно из следующих значений:
1Подавляет формирование параллельных планов.
>1Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.
0(по умолчанию) Использует фактическое количество процессоров или меньше на основе текущей системной рабочей нагрузки.
Дополнительные сведения см. в статье Настройка параллельных операций с индексами.
Примечание.
Параллельные операции индексов недоступны в каждом выпуске SQL Server. Дополнительные сведения см. в выпусках и поддерживаемых функциях SQL Server 2022.
ONLINE = { ON | OFF } <как применимо к drop_clustered_constraint_option>
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF. Операцию REBUILD можно выполнять в оперативном режиме (ONLINE).
DNS
Долгосрочные блокировки таблицы не сохраняются на время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Такое поведение позволит продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции на короткое время выполняется совмещаемая блокировка (S) исходного объекта. Если создается некластеризованный индекс, в конце операции на короткое время выполняется совмещаемая блокировка (S) для исходного объекта. Если в оперативном режиме создается или удаляется кластеризованный индекс либо перестраивается кластеризованный или некластеризованный индекс, запрашивается блокировка SCH-M (изменение схемы). При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON. Допустима только однопотоковая операция перестроения кучи.
Чтобы выполнить инструкцию DDL для операции SWITCH или перестроения индекса в оперативном режиме, должны быть завершены все активные блокирующие транзакции для соответствующей таблицы. Пока выполняется операция SWITCH или перестроение индекса, блокируется запуск новых транзакций, что может существенно повлиять на пропускную способность рабочей нагрузки и временно замедлить доступ к базовой таблице.
ВЫКЛ.
Блокировки таблиц применяются на время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Эта блокировка предотвращает любой доступ пользователей к базовой таблице на время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Эта блокировка предотвращает обновления базовой таблицы, но разрешает операции чтения, например инструкции SELECT. Многопотоковые операции перестроения кучи разрешены.
Дополнительные сведения см. в разделе Об операциях с индексами в режиме "в сети".
Примечание.
Операции с индексами в сети недоступны в каждом выпуске SQL Server. Дополнительные сведения см. в выпусках и поддерживаемых функциях SQL Server 2022.
MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | файловая группа | "default" }
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает местоположение для перемещения строк данных, находящихся в настоящее время на конечном уровне кластеризованного индекса. Таблица перемещается на новое место. Этот параметр применяется только ограничениям, образующим кластеризованный индекс.
Примечание.
В этом контексте default не является ключевым словом. Это идентификатор файловой группы по умолчанию, и поэтому он должен быть заключен в разделители, например: MOVE TO "default" или MOVE TO [default]. Если указано значение "default", то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON. Этот параметр принимается по умолчанию. Дополнительные сведения см. в описании SET QUOTED_IDENTIFIER.
{ CHECK | NOCHECK } CONSTRAINT
Указывает, включено или отключено ограничение constraint_name. Данный параметр может использоваться только с ограничениями FOREIGN KEY и CHECK. При указании NOCHECK ограничение отключено, а будущие вставки или обновления столбца не проверяются в соответствии с условиями ограничения. Отключить ограничения DEFAULT, PRIMARY KEY и UNIQUE нельзя.
ВСЕ
Указывает, что все ограничения отключаются при помощи параметра NOCHECK или включаются при помощи параметра CHECK.
{ ENABLE | DISABLE } TRIGGER
Указывает, включено или отключено ограничение trigger_name. Отключенный триггер остается определенным для таблицы. Но действия триггера не будут выполняться при выполнении инструкций INSERT, UPDATE или DELETE для этой таблицы, пока триггер не будет снова включен.
ВСЕ
Указывает, что все триггеры в таблице включены или отключены.
trigger_name
Указывает имя триггера, подлежащего включению или отключению.
{ ENABLE | DISABLE } CHANGE_TRACKING
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает, разрешено или запрещено отслеживание изменений для этой таблицы. По умолчанию отслеживание изменений запрещено.
Этот параметр доступен только в том случае, если отслеживание изменений разрешено для базы данных. Дополнительные сведения см. в описании параметров ALTER DATABASE SET.
Чтобы разрешить отслеживание изменений, в таблице должен содержаться первичный ключ.
WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает, были ли обновлены ядро СУБД треки, которые изменили отслеживаемые столбцы. Значение по умолчанию — OFF.
SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных 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 в текущий формат (с поддержкой обновления).
Ограничения
Если обе таблицы секционируются одинаково, включая некластеризованные индексы, а целевая таблица не имеет некластеризованных индексов, может появиться ошибка 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 Server 2008 (10.0.x) и более поздних версий. База данных 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 (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, чтобы сделать существующую таблицу темпоральной. Дополнительные сведения см. в разделе Temporal Tables.
HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} }
Применимо к: SQL Server 2017 (14.x) и База данных SQL Azure.
Определяет ограничение срока хранения данных журнала в темпоральной таблице или отсутствие такого ограничения. Если не указано, подразумевается неограниченный срок хранения.
DATA_DELETION
Применяется только к: Только для пограничных вычислений SQL Azure
Включает очистку старых или устаревших данных из таблиц в базе данных на основе политики хранения. Дополнительные сведения см. в статье Включение и отключение хранения данных. Для включения хранения данных необходимо указать следующие параметры.
FILTER_COLUMN = { column_name }
Указывает столбец, который должен использоваться для определения того, являются ли строки в таблице устаревшими. Для столбца фильтра разрешены следующие типы данных.
- Дата
- Дата/время
- datetime2
- SmallDateTime
- DateTimeOffset
RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS }}
Указывает политику периода хранения для таблицы. Период хранения указывается как сочетание положительного целого значения и единицы измерения для дат.
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Указывает разрешенные методы укрупнения блокировки для таблицы.
АВТОМАТИЧЕСКИ
Этот параметр позволяет SQL Server ядро СУБД выбрать степень детализации эскалации блокировки, соответствующую схеме таблицы.
- Если таблица является секционированной, то укрупнению блокировки будет разрешен доступ к куче или гранулярности сбалансированного дерева. Другими словами, укрупнение блокировки будет разрешено вплоть до уровня секции. После эскалации блокировки на уровень HoBT блокировка не будет перерастать позже в степень детализации ТАБЛИЦЫ.
- Если таблица не секционирована, блокировка укрупняется до гранулярности TABLE.
TABLE
Блокировка укрупняется до уровня гранулярности таблицы независимо от того, секционирована таблица или нет. Значение по умолчанию равно TABLE.
DISABLE
В большинстве случаев предотвращает укрупнение блокировки. Блокировки уровня таблицы нельзя запретить полностью. Например, при сканировании таблицы без кластеризованного индекса на уровне сериализуемой изоляции ядро СУБД необходимо взять блокировку таблицы для защиты целостности данных.
REBUILD
Используйте синтаксис REBUILD WITH для перестроения всей таблицы, включая все секции в секционированную таблицу. Если в таблице содержится кластеризованный индекс, то параметр REBUILD перестраивает его. Операция REBUILD может выполняться в рамках операции ONLINE.
Используйте синтаксис REBUILD PARTITION для перестроения одной секции в секционированной таблице.
PARTITION = ALL
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Перестраивает все секции при изменении настройки сжатия секций.
REBUILD WITH ( <rebuild_option> )
Все параметры применяются к таблице с кластеризованным индексом. Если в таблице нет кластеризованного индекса, на структуру кучи влияют не все параметры.
Если для операции REBUILD не указан конкретный параметр сжатия, для секции используется текущий режим сжатия. Чтобы вернуть текущий параметр, запросите data_compression столбец в представлении sys.partitions каталога.
Полное описание параметров перестроения см. в статье ALTER TABLE index_option.
DATA_COMPRESSION
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Задает режим сжатия данных для указанной таблицы, номера секции или диапазона секций. Существуют следующие варианты выбора.
NONE — таблица или указанные секции не сжимаются. Этот вариант не применим к таблицам columnstore.
ROW — таблицы или указанные секции сжимаются, используется сжатие строк. Этот вариант не применим к таблицам columnstore.
PAGE — таблицы или указанные секции сжимаются, используется сжатие страниц. Этот вариант не применим к таблицам columnstore.
COLUMNSTORE
Область применения: SQL Server (SQL Server 2014 (12.x) и более поздних версий) и База данных SQL Azure.
Применяется только к таблицам columnstore. COLUMNSTORE указывает, что должна быть распакована секция, которая была упакована с помощью параметра COLUMNSTORE_ARCHIVE. При восстановлении данных сохраняется режим сжатия columnstore, который используется для всех таблиц columnstore.
COLUMNSTORE_ARCHIVE
Область применения: SQL Server (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 в таблице. Существуют следующие варианты выбора.
DNS
Столбцы, использующие тип данных xml, сжимаются.
ВЫКЛ.
Столбцы, использующие тип данных xml, не сжимаются.
ONLINE = { ON | OFF } <как применимо к single_partition_rebuild_option>
Определяет, доступна ли отдельная секция базовой таблицы и связанные индексы для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF. Операцию REBUILD можно выполнять в оперативном режиме (ONLINE).
DNS
Долгосрочные блокировки таблицы не сохраняются на время операций с индексами. В начале перестройки индекса требуется S-блокировка таблицы, а в конце перестроения индекса в оперативном режиме — блокировка Sch-M. Обе они являются короткими блокировками метаданных, но блокировка изменения схемы (Sch-M) дополнительно ожидает завершения всех блокирующих транзакций. В этот период ожидания блокировка Sch-M блокирует все другие транзакции, которые получат доступ к этой таблице только после завершения блокировки.
Примечание.
При перестроении индекса в режиме "в сети" могут быть заданы параметры low_priority_lock_wait, описанные ниже в этом разделе.
ВЫКЛ.
Блокировки таблиц применяются во время выполнения операций с индексами. Это предотвращает доступ к базовой таблице всех пользователей во время операции.
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.
Имя набора столбцов. Набор столбцов представляет собой нетипизированное XML-представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. Набор столбцов не может быть добавлен в таблицу, если в ней содержатся разреженные столбцы. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.
{ ENABLE | DISABLE } FILETABLE_NAMESPACE
Область применения: SQL Server (SQL Server 2012 (11.x) и более поздних версий.
Включает или выключает ограничения для таблицы FileTable, заданные системой. Может использоваться только для таблицы FileTable.
SET ( FILETABLE_DIRECTORY = directory_name )
Область применения: SQL Server (SQL Server 2012 (11.x) и более поздних версий. База данных SQL Azure не поддерживаетFILETABLE.
Указывает имя каталога таблицы FileTable, совместимое с Windows. Это имя должно быть уникальным среди всех имен каталогов FileTable в базе данных. Проверка уникальности не учитывает регистр символов независимо от параметров сортировки SQL. Может использоваться только для таблицы FileTable.
REMOTE_DATA_ARCHIVE
Область применения: SQL Server (SQL Server 2017 (14.x) и более поздних версий.
Включает или отключает Stretch Database для таблицы. Дополнительные сведения см. в разделе Stretch Database.
Внимание
Stretch Database устарел в SQL Server 2022 (16.x) и База данных SQL Azure. Эта функция будет удалена в будущей версии ядро СУБД. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
Включение Stretch Database для таблицы
Если вы включаете Stretch для таблицы, указывая ON, также нужно указать MIGRATION_STATE = OUTBOUND, чтобы сразу же приступить к миграции данных, или MIGRATION_STATE = PAUSED, чтобы отложить миграцию. Значение по умолчанию — MIGRATION_STATE = OUTBOUND. Более подробную информацию о включении Stretch для таблицы см. в статье Включение Stretch Database для таблицы.
Предварительные требования. Прежде чем включить Stretch для таблицы, необходимо включить Stretch на сервере и в базе данных. Дополнительные сведения см. в статье Включение Stretch Database для базы данных.
Разрешения. Чтобы включить 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 | predicate } ]
Область применения: SQL Server (SQL Server 2017 (14.x) и более поздних версий.
Дополнительно указывает предикат фильтра для выбора строк для миграции из таблицы, которая содержит данные журнала и текущие данные. Этот предикат должен вызывать детерминированную встроенную функцию с табличным значением. Дополнительные сведения см. в статьях Включение Stretch Database для таблицы и Выбор строк для миграции с использованием функции фильтров (Stretch Database).
Внимание
Если указать плохо оптимизированный предикат фильтра, перенос данных будет выполняться медленно. Stretch Database применяет предикат фильтра к таблице при помощи оператора CROSS APPLY.
Если предикат фильтра не указан, переносится вся таблица.
Если вы указываете предикат фильтра, необходимо также указать MIGRATION_STATE.
MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
Область применения: SQL Server (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 (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 = time [MINUTES]
Область применения: SQL Server (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 (SQL Server 2014 (12.x) и более поздних версий) и База данных SQL Azure.
NONE
Продолжить ожидание блокировки с обычным приоритетом.
SELF
Выход из операции SWITCH или операции DDL по перестроению индекса в оперативном режиме, которая выполняется в данный момент, без дополнительных действий.
BLOCKERS
Остановка всех пользовательских транзакций, которые в данный момент блокируют операцию SWITCH или операцию DDL по перестроению индекса в оперативном режиме, чтобы эта операция могла продолжить работу.
Необходимо разрешение ALTER ANY CONNECTION.
IF EXISTS
Область применения: SQL Server (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) указывает время (целочисленное значение в минутах) для выполнения возобновляемой операции добавления ограничения в online-режиме, пока она не будет приостановлена. Если этот параметр не указан, операция продолжается вплоть до завершения.
Дополнительные сведения об активации и использовании возобновляемых операций ALTER TABLE ADD CONSTRAINT: Возобновляемое добавление табличных ограничений.
Замечания
Чтобы добавить новые строки данных, используйте INSERT. Чтобы удалить строки данных, используйте DELETE или TRUNCATE TABLE. Чтобы изменить значения в существующих строках, используйте UPDATE.
При наличии в кэше процедур каких-либо планов выполнения, ссылающихся на таблицу, инструкция ALTER TABLE помечает их как подлежащие перекомпиляции в их следующем выполнении.
В базе данных SQL в Microsoft Fabric можно создать некоторые функции таблицы, но не будут зеркально отображаться в 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, UPDATE или DELETE, влияющие на большое количество строк.
Применяется к хранилищу в 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 в виде оперативной операции
Начиная с выпуск Enterprise SQL Server 2012 (11.x), добавление столбца NOT NULL со значением по умолчанию — это операция в сети, когда значение по умолчанию является константой среды выполнения. Это означает, что операция выполняется почти мгновенно, независимо от количества строк в таблице, так как существующие строки не обновляются в таблице во время этой операции. Вместо этого значение по умолчанию сохраняется в метаданных таблицы и применяется по мере необходимости в запросах, обращающихся к этим строкам. Такое поведение реализуется автоматически. Вам не нужно использовать дополнительные предложения, кроме базового синтаксиса операции ADD COLUMN. Константой времени выполнения считается любое выражение, которое сохраняет во время выполнения одинаковое значение для каждой строки в таблице, независимо от ее детерминизма. Например, выражение константы «Временные данные» и системная функция GETUTCDATETIME() являются константами времени выполнения. Функции NEWID() и NEWSEQUENTIALID(), напротив, не являются константами времени выполнения, так как для каждой строки в таблице создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, которое не является константой времени выполнения, всегда выполняется с прекращением работы и монопольной блокировкой (SCH-M) на весь период ее выполнения.
Для существующих строк используется ссылка на значение, хранящееся в метаданных, но это же значение сохраняется напрямую в каждой новой строке, которая вставляется без указания значения для этого столбца. Значение по умолчанию, хранящееся в метаданных, будет перемещено в существующую строку при ее обновлении (даже если в инструкции UPDATE не указан этот столбец), а также при перестроении таблицы или кластеризованного индекса.
Столбцы типов varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography или с пользовательским типом среды CLR нельзя добавлять в оперативном режиме. Столбец нельзя добавить в оперативном режиме, если после этой операции максимально возможный размер строки превысит ограничение в 8060 байт. В этом случае столбец добавляется в рамках операции вне сети.
Выполнение параллельного плана
В выпуске SQL Server 2012 (11.x) Enterprise и более поздних версиях число процессоров, используемых для выполнения одного оператора ALTER TABLE ADD (индекс) CONSTRAINT или DROP (кластеризованный индекс) определяется максимальным значением параметра конфигурации параллелизма и текущей рабочей нагрузкой. Если ядро СУБД обнаруживает, что система занята, степень параллелизма операции автоматически уменьшается до запуска инструкции. Можно вручную настроить число процессоров, применяемых для запуска инструкции, указав параметр MAXDOP. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.
Секционированные таблицы
Кроме выполнения операций SWITCH, затрагивающих секционированные таблицы, инструкция ALTER TABLE позволяет изменять состояния столбцов, ограничений и триггеров секционированной таблицы точно так же, как и для несекционированных таблиц. Но эта инструкция не может изменить способ секционирования таблицы. Чтобы заново секционировать секционированную таблицу, используйте ALTER PARTITION SCHEME и ALTER PARTITION FUNCTION. Кроме того, невозможно изменить тип данных для столбца секционированной таблицы.
Ограничения в таблицах с представлениями, привязанными к схемам
К инструкциям ALTER TABLE в таблицах с представлениями, привязанными к схемам, применяются те же ограничения, которые применяются в текущем времени для изменения таблиц с простым индексом. Добавление столбца разрешено. Но не разрешается удаление или изменение столбца, участвующего в любом из представлений, привязанных к схемам. Если оператор ALTER TABLE требует изменения столбца, используемого в представлении с привязкой к схеме, alter TABLE завершается ошибкой, и ядро СУБД вызывает сообщение об ошибке. Дополнительные сведения о привязке к схеме и индексированных представлениях см. в описании CREATE VIEW.
Создание или удаление триггеров для базовых таблиц не зависит от создания привязанного к схеме представления, в котором указаны эти таблицы.
Индексы и инструкция ALTER TABLE
При удалении ограничений индексы, создаваемые как часть ограничения, удаляются. Индексы, создаваемые при помощи инструкции CREATE INDEX, должны удаляться при помощи инструкции DROP INDEX. Используйте инструкцию ALTER INDEX, чтобы перестроить индексную часть определения ограничения. Не обязательно удалять и заново добавлять ограничение с помощью инструкции ALTER TABLE.
Перед удалением столбца необходимо удалить все индексы и ограничения, основанные на столбце.
После удаления ограничения, создавшего кластеризованный индекс, строки данных, хранившиеся на конечном уровне кластеризованного индекса, хранятся в некластеризованной таблице. Можно удалить кластеризованный индекс и переместить полученную в результате таблицу в другую файловую группу или схему секционирования в одной транзакции, указав параметр MOVE TO. Параметр MOVE TO обладает следующими ограничениями.
- Параметр MOVE TO недопустим для индексированных представлений и некластеризованных индексов.
- Схема секционирования или файловая группа уже должна существовать.
- Если не указан аргумент MOVE TO, таблица будет размещена в той же схеме секционирования или файловой группе, которая была определена для кластеризованного индекса.
При удалении кластеризованного индекса укажите параметр ONLINE **=** ON, чтобы транзакция DROP INDEX не блокировала запросы и изменения для базовых данных и связанных с ними некластеризованных индексов.
Параметр ONLINE = ON имеет следующие ограничения.
- Параметр ONLINE = ON неприменим для кластеризованных индексов, которые отключены. Отключенные индексы должны удаляться при помощи параметра ONLINE = OFF.
- Только один индекс может удаляться единовременно.
- Параметр ONLINE = ON неприменим для индексированных представлений, некластеризованных индексов или индексов в локальных временных таблицах.
- Параметр ONLINE = ON неприменим для индексов columnstore.
Для удаления кластеризованного индекса временно требуется место на диске, равное размеру существующего кластеризованного индекса. Это дополнительное пространство освобождается сразу после завершения операции.
Примечание.
Параметры, перечисленные в <drop_clustered_constraint_option>, применяются к кластеризованным индексам по таблицам, но не могут применяться к кластеризованным индексам по представлениям или к некластеризованным индексам.
Реплицировать изменения схемы
При запуске 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. Это действие можно выполнять в параллельном режиме, чтобы значительно ускорить его.
Операция REBUILD для индексов в режиме "в сети"
Чтобы выполнить инструкцию DDL для перестроения индекса в оперативном режиме, необходимо завершить все активные блокирующие транзакции, выполняемые для соответствующей таблицы. Запуск перестроения индекса в оперативном режиме блокирует все новые транзакции, которые готовы к выполнению для этой таблицы. Хотя продолжительность блокировки для перестроения индекса в оперативном режиме очень невелика, ожидание завершения всех открытых транзакций по таблице и блокировка новых транзакций может заметно отразиться на пропускной способности. Это может замедлить выполнение рабочей нагрузки или привести к превышению времени ожидания, существенно ограничивая доступ к базовой таблице. Параметр WAIT_AT_LOW_PRIORITY позволяет администратору базы данных управлять блокировками S и 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 завершилось успешно.
Чтобы устранить эту проблему, откажитесь от использования четырехкомпонентного префикса.
Разрешения
Требуется разрешение ALTER на таблицу.
Разрешения ALTER TABLE применяются к обеим таблицам, затронутым инструкцией ALTER TABLE SWITCH. Любые переключенные данные наследуют защиту от целевой таблицы.
Если вы определили какой-либо из столбцов в инструкции ALTER TABLE с пользовательским типом для среды CLR или с псевдонимом типа данных, вам потребуется разрешение REFERENCES для этого типа.
Для добавления или изменения столбца, который обновляет строки таблицы, требуется разрешение UPDATE для этой таблицы. Например, добавление столбца NOT NULL со значением по умолчанию или добавление столбца идентификаторов, если таблица не пуста.
Примеры
| Категория | Используемые элементы синтаксиса |
|---|---|
| Добавление столбцов и ограничений | ADD * PRIMARY KEY с параметрами индекса * разреженные столбцы и наборы столбцов * |
| Удаление столбцов и ограничений | DROP |
| Изменение определения столбца | изменение типа данных * изменение размера столбца * параметры сортировки |
| Изменение определения таблицы | DATA_COMPRESSION * SWITCH PARTITION * LOCK ESCALATION * отслеживание изменений |
| Отключение и включение ограничений и триггеров | CHECK * NO CHECK * ENABLE TRIGGER * DISABLE TRIGGER |
| Операции в сети | ONLINE |
| Системное управление версиями | SYSTEM_VERSIONING |
Добавление столбцов и ограничений
В примерах из этого раздела показано добавление в таблицу столбцов и ограничений.
А. Добавление нового столбца
Следующий пример показывает добавление столбца, который допускает значения 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
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO
В. Добавление непроверенного ограничения CHECK в существующий столбец
В следующем примере к существующему столбцу в таблице добавляется ограничение. Столбец имеет значение, нарушающее это ограничение. Поэтому во избежание проверки ограничения относительно существующих строк, а также для того, чтобы разрешить добавление ограничения, применяется WITH NOCHECK.
CREATE TABLE dbo.doc_exd (column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO
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
Е. Добавление нескольких столбцов с ограничениями
Следующий пример показывает добавление нескольких столбцов с ограничениями, которые определяются с помощью нового столбца. Первый новый столбец имеет свойство IDENTITY. Каждая строка таблицы имеет новые добавочные значения в столбце идентификаторов.
CREATE TABLE dbo.doc_exe (column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO
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 NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO
G. Создание ограничения PRIMARY KEY с параметрами сжатия индекса или данных
Следующий пример показывает создание ограничения PRIMARY KEY PK_TransactionHistoryArchive_TransactionID и установку параметров FILLFACTOR, ONLINE и PAD_INDEX. Результирующий кластеризованный индекс будет иметь такое же имя, что и ограничение.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
USE AdventureWorks;
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 AdventureWorks;
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);
Удаление столбцов и ограничений
Приведенные в этом разделе примеры демонстрируют удаление столбцов и ограничений.
А. Удаление столбца или столбцов
В первом примере для удаления столбца изменяется таблица. Во втором примере удаляется несколько столбцов.
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
В. Удаление ограничения PRIMARY KEY в режиме ONLINE
В следующем примере удаляется ограничение PRIMARY KEY с параметром ONLINE, имеющим значение ON.
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 ;
Изменение определения столбца
А. Изменение типа данных столбца
В следующем примере столбец таблицы изменяется с 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') ;
В. Изменение сортировки столбцов
В следующем примере демонстрируется изменение параметров сортировки столбца. Сначала создается таблица с параметрами сортировки пользователей по умолчанию.
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 методом случайного шифрования. Чтобы следующая инструкция была выполнена успешно, должны соблюдаться следующие условия:
- Ключ шифрования столбцов должен поддерживать анклав. Это означает, что он должен быть зашифрован главным ключом столбца, который допускает анклавные вычисления.
- Целевой экземпляр SQL Server должен поддерживать Always Encrypted с безопасными анклавами.
- Оператор следует передавать через подключение, настроенное для Always Encrypted с безопасными анклавами и применяемое поддерживаемый драйвер клиента.
- Вызывающему приложению нужен доступ к главному ключу столбца, который защищает ключ
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
Изменение определения таблицы
В приведенных в этом разделе примерах показано, как изменить определение таблицы.
А. Изменение таблицы для изменения сжатия
В следующем примере изменяется режим сжатия несекционированной таблицы. Куча или кластеризованный индекс будет перестроен. Если таблица является кучей, то все некластеризованные индексы будут перестроены.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE) ;
В следующем примере изменяется режим сжатия секционированной таблицы. Инструкция REBUILD PARTITION = 1 вызывает перестройку только секции с номером 1.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =NONE) ;
GO
Та же операция, использующая следующий альтернативный синтаксис, вызывает повторное построение всех секций в таблице.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
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
В. Переключение секций между таблицами
В следующем примере демонстрируется создание секционированной таблицы, исходя из предположения, что схема секционирования 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 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO) ;
GO
Е. Настройка отслеживания изменений в таблице
В следующем примере в таблице Person.Person включается отслеживание изменений.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;
В следующем примере разрешается отслеживание изменений и отслеживание столбцов, которые обновляются при внесении изменений.
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
В следующем примере в таблице Person.Person отключается отслеживание изменений.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING ;
Отключение и включение ограничений и триггеров
А. Отключение и повторное включение ограничения
В следующем примере отключается ограничение на зарплату. Параметр NOCHECK CONSTRAINT используется в инструкции ALTER TABLE для отключения ограничения и обеспечения возможности вставки, противоречащей указанному ограничению. CHECK CONSTRAINT повторно включает ограничение.
CREATE TABLE dbo.cnst_example (
id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)) ;
-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000) ;
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000) ;
-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000) ;
-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000) ;
-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;
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 TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO
Операции в сети
А. Перестроение индекса в оперативном режиме с низким приоритетом ожидания.
В следующем примере показано, как выполнить перестроение индекса в оперативном режиме с низким приоритетом ожидания.
Область применения: 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. Изменение столбца в режиме "в сети"
В следующем примере показано, как выполнить операцию изменения столбца с параметром 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
sp_help doc_exy;
DROP TABLE dbo.doc_exy ;
GO
Системное управление версиями
Следующие четыре примера помогут ознакомиться с синтаксисом использования системного управления версиями. Дополнительные сведения см. в разделе Приступая к работе c темпоральными таблицами с системным управлением версиями.
Применимо: SQL Server 2016 (13.x) и более поздних версий и База данных SQL Azure.
А. Добавление системного управления версиями в существующие таблицы
В следующем примере показано добавление системного управления версиями в существующую таблицу и создание будущей таблицы журнала. В этом примере допускается существование таблицы InsurancePolicy, для которой определен первичный ключ. Этот пример заполняет только что созданные столбцы периода для системного управления версиями значениями по умолчанию для времени начала и окончания, так как эти значения не могут быть NULL. В этом примере используется предложение HIDDEN, чтобы не затрагивать существующие приложения, взаимодействующие с текущей таблицей. Он также использует HISTORY_RETENTION_PERIOD, доступные только для База данных SQL.
--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
DEFAULT SYSUTCDATETIME(),
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ;
--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))
В. Отключение и повторное включение системного управления версиями для изменения схемы таблицы
В этом примере показано, как отключить системное управление версиями в таблице 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 NOT NULL DEFAULT 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
ADD Col5 int NOT NULL DEFAULT 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.
А. Определение секционирования таблицы
Следующий запрос возвращает одну или несколько строк, если таблица FactResellerSales секционирована. Если таблица не секционирована, строки не возвращаются.
SELECT * FROM sys.partitions AS p
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
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT 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 ;
В. Определение столбца секционирования для секционированных таблиц
Следующий запрос возвращает имя столбца секционирования таблицы . 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
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
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))) ;
Е. Разделение секции
В следующем примере показано разбиение секции в таблице.
Таблица 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 |
- Секция 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