ALTER TABLE |
Modifie la table |
|---|---|
| Microsoft SQL Server |
Syntaxe
|
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] , ] 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 } } ] ) ] } ) | REBUILD [ [PARTITION = ALL] [ WITH ( rebuild_option [ ,...n ] ) ] | [ PARTITION = partition_number [ WITH ( single_partition_rebuild_option [ ,...n ] ) ] ] ] | table_option | filetable_option | stretch_configuration } [ ; ] 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 } ) } |
Syntaxe des tables sur disque |
|
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 ) ] } [ ; ] 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 ] } |
Syntaxe des tables optimisées en mémoire |
Description
Cette instruction permet de modifier une définition de table, d'ajouter ou d'enlever des colonnes et des contraintes, de réassocier et de reconstruire des partitions ou d'activer ou désactiver des contraintes et des déclencheurs.
Dernière mise à jour : Vendredi, le 19 Juin 2020