CREATE TABLE |
Crée une table |
|---|---|
| SQL Server | Microsoft SQL Server |
Syntaxe
| CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name ( { column_definition } [ ,...n ] ) [ ; ] |
|
CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ] ( { column_definition | computed_column_definition | column_set_definition | [ table_constraint ] | [ table_index ] } [ ,...n ] [ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name , system_end_time_column_name ) ] ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ] [ WITH ( table_option [ ,...n ] ) ] [ ; ] column_definition ::= column_name data_type [ FILESTREAM ] [ COLLATE collation_name ] [ SPARSE ] [ MASKED WITH ( FUNCTION = ' mask_function ') ] [ CONSTRAINT constraint_name [ DEFAULT constant_expression ] ] [ IDENTITY [ ( seed,increment ) ] [ NOT FOR REPLICATION ] [ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ] [ NULL | NOT NULL ] [ ROWGUIDCOL ] [ ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = key_name , ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED } , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' ) ] [ column_constraint [ ...n ] ] [ column_index ] data type ::= [ type_schema_name . ] type_name [ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] column_constraint ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( index_option [ , ...n ] ) ] [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } column_index ::= INDEX index_name [ CLUSTERED | NONCLUSTERED ] [ WITH ( index_option [ ,... n ] ) ] [ ON { partition_scheme_name (column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] computed_column_definition ::= column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( index_option [ , ...n ] ) ] [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) ] column_set_definition ::= column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS table_constraint ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] ) [ WITH FILLFACTOR = fillfactor |WITH ( index_option [ , ...n ] ) ] [ ON { partition_scheme_name (partition_column_name) | filegroup | "default" } ] | FOREIGN KEY ( column [ ,...n ] ) REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) table_index ::= { { INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column_name [ ASC | DESC ] [ ,... n ] ) | INDEX index_name CLUSTERED COLUMNSTORE | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] ) } [ WITH ( index_option [ ,... n ] ) ] [ ON { partition_scheme_name (column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] } table_option ::= { [DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]] [ FILETABLE_DIRECTORY = directory_name ] [ FILETABLE_COLLATE_FILENAME = { collation_name | database_default } ] [ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name ] [ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name ] [ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name ] [ SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] ] [ REMOTE_DATA_ARCHIVE = { ON [ ( table_stretch_options [,...n] ) ] | OFF ( MIGRATION_STATE = PAUSED ) } ] } table_stretch_options ::= { [ FILTER_PREDICATE = { null | table_predicate_function } , ] MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED } } index_option ::= { PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF} | ALLOW_PAGE_LOCKS ={ ON | OFF} | COMPRESSION_DELAY= {0 | delay [Minutes]} | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ] } range ::= partition_number_expression TO partition_number_expression |
|
CREATE TABLE [database_name . [schema_name ] . | schema_name . ] table_name ( { column_definition | [ table_constraint ] [ ,... n ] | [ table_index ] [ ,... n ] } [ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name , system_end_time_column_name ) ] ) [ WITH ( table_option [ ,... n ] ) ] [ ; ] column_definition ::= column_name data_type [ COLLATE collation_name ] [ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ] [ NULL | NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT memory_optimized_constant_expression ] | [ IDENTITY [ ( 1, 1 ) ] ] [ column_constraint ] [ column_index ] data type ::= [type_schema_name . ] type_name [ (precision [ , scale ]) ] column_constraint ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } { NONCLUSTERED | NONCLUSTERED HASH WITH (BUCKET_COUNT = bucket_count) } | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] | CHECK ( logical_expression ) } 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 ] } table_option ::= { MEMORY_OPTIMIZED = ON | DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA} | SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } |
Paramètres
| Nom | Description |
|---|---|
| database_name | Ce paramètre permet d'indiquer le nom de la base de données dans laquelle la table est créé. |
| schema_name | Ce paramètre permet d'indiquer le nom du schéma dans laquelle la nouvelle table doit être. |
| table_name | Ce paramètre permet d'indiquer le nom de la nouvelle table. |
| column_name | Ce paramètre permet d'indiquer le nom d'une colonne (champ) d'une table. |
| ... | ... |
Description
Cette instruction permet de créer une nouvelle table dans SQL Server.
Dernière mise à jour : Samedi, le 4 novembre 2017