Creates a new table in SQL Server and Azure SQL Database.
For Azure Synapse Analytics syntax, see CREATE TABLE (Azure Synapse Analytics).
Simple CREATE TABLE syntax (common if not using options):
CREATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >( < > [ . n ] ) [ ; ]
Disk-based CREATE TABLE syntax:
CREATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[ AS FileTable ] ( < | | | [ ] [ . n ] | [ ] > [ . 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 ( [ . n ] ) ] [ ; ] ::= column_name [ 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 | TRANSACTION_ID | SEQUENCE_NUMBER > < START | END >[ HIDDEN ] ] [ [ CONSTRAINT constraint_name ] ] [ ROWGUIDCOL ] [ ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = key_name , ENCRYPTION_TYPE = < DETERMINISTIC | RANDOMIZED >, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' ) ] [ [ . n ] ] [ ] ::= [ type_schema_name. ] type_name [ ( precision [ , scale ] | max | [ < CONTENT | DOCUMENT >] xml_schema_collection ) ] ::= [ CONSTRAINT constraint_name ] < < PRIMARY KEY | UNIQUE >[ CLUSTERED | NONCLUSTERED ] [ ( [ . n ] ) ] [ WITH FILLFACTOR = fillfactor | WITH ( [ . 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 ) > ::= INDEX index_name [ CLUSTERED | NONCLUSTERED ] [ WITH ( [ . n ] ) ] [ ON < partition_scheme_name ( column_name ) | filegroup_name | default >] [ FILESTREAM_ON < filestream_filegroup_name | partition_scheme_name | "NULL" >] ::= column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] < PRIMARY KEY | UNIQUE >[ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( [ . 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_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ::= [ CONSTRAINT constraint_name ] < < PRIMARY KEY | UNIQUE >[ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ . n ] ) [ WITH FILLFACTOR = fillfactor | WITH ( [ . n ] ) ] [ ON < partition_scheme_name (partition_column_name) | filegroup | "default" >] | FOREIGN KEY ( column_name [ . 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 ) > ::= < < INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ . n ] ) | INDEX index_name CLUSTERED COLUMNSTORE [ ORDER (column_name [ , . n ] ) ] | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ . n ] ) >[ INCLUDE ( column_name [ . n ] ) ] [ WHERE ] [ WITH ( [ . n ] ) ] [ ON < partition_scheme_name ( column_name ) | filegroup_name | default >] [ FILESTREAM_ON < filestream_filegroup_name | partition_scheme_name | "NULL" >] > ::= < [ DATA_COMPRESSION = < NONE | ROW | PAGE >[ ON PARTITIONS ( < | > [ . n ] ) ] ] [ XML_COMPRESSION = < ON | OFF >[ ON PARTITIONS ( < | > [ . n ] ) ] ] [ FILETABLE_DIRECTORY = ] [ FILETABLE_COLLATE_FILENAME = < | database_default > ] [ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = ] [ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = ] [ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = ] [ SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name.history_table_name [ , DATA_CONSISTENCY_CHECK = < ON | OFF >] ) ] ] [ REMOTE_DATA_ARCHIVE = < ON [ ( [ . n] ) ] | OFF ( MIGRATION_STATE = PAUSED ) > ] [ DATA_DELETION = ON < ( FILTER_COLUMN = column_name, RETENTION_PERIOD = < INFINITE | number < DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS >) > ] [ LEDGER = ON [ ( [ . n ] ) ] | OFF ] > ::= < [ LEDGER_VIEW = schema_name.ledger_view_name [ ( [ . n ] ) ] ] [ APPEND_ONLY = ON | OFF ] > ::= < [ TRANSACTION_ID_COLUMN_NAME = transaction_id_column_name ] [ SEQUENCE_NUMBER_COLUMN_NAME = sequence_number_column_name ] [ OPERATION_TYPE_COLUMN_NAME = operation_type_id column_name ] [ OPERATION_TYPE_DESC_COLUMN_NAME = operation_type_desc_column_name ] > ::= < [ FILTER_PREDICATE = < NULL | table_predicate_function >, ] MIGRATION_STATE = < OUTBOUND | INBOUND | PAUSED >> ::= < PAD_INDEX = < ON | OFF >| FILLFACTOR = fillfactor | IGNORE_DUP_KEY = < ON | OFF >| STATISTICS_NORECOMPUTE = < ON | OFF >| STATISTICS_INCREMENTAL = < ON | OFF >| ALLOW_ROW_LOCKS = < ON | OFF >| ALLOW_PAGE_LOCKS = < ON | OFF >| OPTIMIZE_FOR_SEQUENTIAL_KEY = < ON | OFF >| COMPRESSION_DELAY = < 0 | delay [ Minutes ] >| DATA_COMPRESSION = < NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE >[ ON PARTITIONS ( < partition_number_expression | > [ . n ] ) ] | XML_COMPRESSION = < ON | OFF >[ ON PARTITIONS ( < | > [ . n ] ) ] > ::= TO
Memory optimized CREATE TABLE syntax:
CREATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >( < | [ ] [ . n ] | [ ] [ . n ] > [ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name , system_end_time_column_name ) ] ) [ WITH ( [ . n ] ) ] [ ; ] ::= column_name [ COLLATE collation_name ] [ GENERATED ALWAYS AS ROW < START | END >[ HIDDEN ] ] [ NULL | NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT memory_optimized_constant_expression ] | [ IDENTITY [ ( 1, 1 ) ] ] [ ] [ ] ::= [type_schema_name. ] type_name [ (precision [ , scale ]) ] ::= [ CONSTRAINT constraint_name ] < < PRIMARY KEY | UNIQUE > < NONCLUSTERED | NONCLUSTERED HASH WITH ( BUCKET_COUNT = bucket_count ) >[ ( [ . n ] ) ] | [ FOREIGN KEY ] REFERENCES [ schema_name. ] referenced_table_name [ ( ref_column ) ] | CHECK ( logical_expression ) > ::= [ CONSTRAINT constraint_name ] < < PRIMARY KEY | UNIQUE > < NONCLUSTERED ( column_name [ ASC | DESC ] [ . n ]) | NONCLUSTERED HASH ( column_name [ . n ] ) WITH ( BUCKET_COUNT = bucket_count ) >| FOREIGN KEY ( column_name [ . n ] ) REFERENCES referenced_table_name [ ( ref_column [ . n ] ) ] | CHECK ( logical_expression ) > ::= INDEX index_name < [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH ( BUCKET_COUNT = bucket_count ) > ::= INDEX index_name < [ NONCLUSTERED ] HASH ( column_name [ . n ] ) WITH ( BUCKET_COUNT = bucket_count ) | [ NONCLUSTERED ] ( column_name [ ASC | DESC ] [ . n ] ) [ ON filegroup_name | default ] | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = < 0 | delay [ Minutes ] >) ] [ ON filegroup_name | default ] > ::= < MEMORY_OPTIMIZED = ON | DURABILITY = < SCHEMA_ONLY | SCHEMA_AND_DATA >| SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name.history_table_name [, DATA_CONSISTENCY_CHECK = < ON | OFF >] ) ] >
The name of the database in which the table is created. database_name must specify the name of an existing database. If not specified, database_name defaults to the current database. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have CREATE TABLE permissions.
The name of the schema to which the new table belongs.
The name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign ( # )) that can't exceed 116 characters.
Applies to: SQL Server 2012 (11.x) and later.
Creates the new table as a FileTable. You don't specify columns because a FileTable has a fixed schema. For more information, see FileTables.
An expression that defines the value of a computed column. A computed column is a virtual column that isn't physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty . The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression can't be a subquery or contain alias data types.
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
Each row in a table can have different values for columns that are involved in a computed column; therefore, the computed column may not have the same value for each row.
Based on the expressions that are used, the nullability of computed columns is determined automatically by the Database Engine. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows also produce NULL results. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL with the check_expression constant, where the constant is a nonnull value substituted for any NULL result. REFERENCES permission on the type is required for computed columns based on common language runtime (CLR) user-defined type expressions.
Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED lets you create an index on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns that are used as partitioning columns of a partitioned table must be explicitly marked PERSISTED . computed_column_expression must be deterministic when PERSISTED is specified.
Specifies the partition scheme or filegroup on which the table is stored. If partition_scheme is specified, the table is to be a partitioned table whose partitions are stored on a set of one or more filegroups specified in partition_scheme. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If "default" is specified, or if ON isn't specified at all, the table is stored on the default filegroup. The storage mechanism of a table as specified in CREATE TABLE can't be subsequently altered.
ON < partition_scheme | filegroup | "default" > can also be specified in a PRIMARY KEY or UNIQUE constraint. These constraints create indexes. If filegroup is specified, the index is stored in the named filegroup. If "default" is specified, or if ON isn't specified at all, the index is stored in the same filegroup as the table. If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index. If CLUSTERED is specified or the constraint otherwise creates a clustered index, and a partition_scheme is specified that differs from the partition_scheme or filegroup of the table definition, or vice-versa, only the constraint definition will be honored, and the other will be ignored.
In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default] . If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER.
After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO . This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table. For more information, see ALTER TABLE.
Indicates that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns (including geometry and geography) are stored on the specified filegroup.
TEXTIMAGE_ON isn't allowed if there are no large value columns in the table. TEXTIMAGE_ON can't be specified if partition_scheme is specified. If "default" is specified, or if TEXTIMAGE_ON isn't specified at all, the large value columns are stored in the default filegroup. The storage of any large value column data specified in CREATE TABLE can't be subsequently altered.
varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8,000 bytes, and as long as the value can fit the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value, which indicates that all values are stored directly in the data row.
TEXTIMAGE_ON only changes the location of the "LOB storage space", it does not affect when data is stored in-row. Use large value types out of row option of sp_tableoption to store the entire LOB value out of the row.
In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in TEXTIMAGE_ON "default" or TEXTIMAGE_ON [default] . If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER.
Applies to: SQL Server 2008 R2 (10.50.x) and later. Azure SQL Database and Azure SQL Managed Instance do not support FILESTREAM .
Specifies the filegroup for FILESTREAM data.
If the table contains FILESTREAM data and the table is partitioned, the FILESTREAM_ON clause must be included, and must specify a partition scheme of FILESTREAM filegroups. This partition scheme must use the same partition function and partition columns as the partition scheme for the table; otherwise, an error is raised.
If the table isn't partitioned, the FILESTREAM column can't be partitioned. FILESTREAM data for the table must be stored in a single filegroup. This filegroup is specified in the FILESTREAM_ON clause.
If the table isn't partitioned and the FILESTREAM_ON clause isn't specified, the FILESTREAM filegroup that has the DEFAULT property set is used. If there is no FILESTREAM filegroup, an error is raised.
As with ON and TEXTIMAGE_ON , the value set by using CREATE TABLE for FILESTREAM_ON can't be changed, except in the following cases:
The filegroup in the FILESTREAM_ON clause, or each FILESTREAM filegroup that is named in the partition scheme, must have one file defined for the filegroup. This file must be defined by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.
For related FILESTREAM articles, see Binary Large Object - Blob Data.
Specifies the data type of the column, and the schema to which it belongs. For disk-based tables, use one of the following data types:
If type_schema_name isn't specified, the SQL Server Database Engine references type_name in the following order:
For memory-optimized tables, see Supported Data Types for In-Memory OLTP for a list of supported system types.
Specifies that each instance of the xml data type in column_name can contain multiple top-level elements. CONTENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified. If not specified, CONTENT is the default behavior.
Specifies that each instance of the xml data type in column_name can contain only one top-level element. DOCUMENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified.
Applies only to the xml data type for associating an XML schema collection with the type. Before typing an xml column to a schema, the schema must first be created in the database by using CREATE XML SCHEMA COLLECTION.
Specifies the value provided for the column when a value isn't explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. If a default value is specified for a user-defined type column, the type should support an implicit conversion from constant_expression to the user-defined type. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.
Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p, 0), or numeric(p, 0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints can't be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).
In the CREATE TABLE statement, the NOT FOR REPLICATION clause can be specified for the IDENTITY property, FOREIGN KEY constraints, and CHECK constraints. If this clause is specified for the IDENTITY property, values aren't incremented in identity columns when replication agents perform inserts. If this clause is specified for a constraint, the constraint isn't enforced when replication agents perform insert, update, or delete operations.
Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Specifies a column used by the system to automatically record information about row versions in the table and its history table (if the table is system versioned and has a history table). Use this argument with the WITH SYSTEM_VERSIONING = ON parameter to create system-versioned tables: temporal or ledger tables. For more information, see updateable ledger tables and temporal tables.
Parameter | Required data type | Required nullability | Description |
---|---|---|---|
ROW | datetime2 | START: NOT NULL END: NOT NULL | Either the start time for which a row version is valid (START) or the end time for which a row version is valid (END). Use this argument with the PERIOD FOR SYSTEM_TIME argument to create a temporal table. |
TRANSACTION_ID | bigint | START: NOT NULL END: NULL | Applies to: SQL Server 2022 (16.x) and later, and Azure SQL Database. |
If you attempt to specify a column that doesn't meet the above data type or nullability requirements, the system will throw an error. If you don't explicitly specify nullability, the system will define the column as NULL or NOT NULL per the above requirements.
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Specifies to create an index on the table. This can be a clustered index, or a nonclustered index. The index will contain the columns listed, and will sort the data in either ascending or descending order.
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Specifies to store the entire table in columnar format with a clustered columnstore index. This always includes all columns in the table. The data isn't sorted in alphabetical or numeric order since the rows are organized to gain columnstore compression benefits.
In Azure Synapse Analytics, Analytics Platform System (PDW), and SQL Server 2022 (16.x) and later versions, you can determine the order of the columns for a clustered columnstore index. For more information, see Use an ordered clustered columnstore index for large data warehouse tables.
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Specifies to create a nonclustered columnstore index on the table. The underlying table can be a rowstore heap or clustered index, or it can be a clustered columnstore index. In all cases, creating a nonclustered columnstore index on a table stores a second copy of the data for the columns in the index.
The nonclustered columnstore index is stored and managed as a clustered columnstore index. It is called a nonclustered columnstore index to because the columns can be limited and it exists as a secondary index on a table.
Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name specifies the column against which a partitioned index will be partitioned. This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name isn't restricted to the columns in the index definition. Any column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. This restriction allows the Database Engine to verify uniqueness of key values within a single partition only.
When you partition a non-unique, clustered index, the Database Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. When partitioning a non-unique, nonclustered index, the Database Engine adds the partitioning column as a non-key (included) column of the index, if it is not already specified.
If partition_scheme_name or filegroup isn't specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.
You cannot specify a partitioning scheme on an XML index. If the base table is partitioned, the XML index uses the same partition scheme as the table.
For more information about partitioning indexes, Partitioned Tables and Indexes.
Creates the specified index on the specified filegroup. If no location is specified and the table or view isn't partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist.
Creates the specified index on the default filegroup.
In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default] . If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER.
Applies to: SQL Server 2008 R2 (10.50.x) and later.
Specifies the placement of FILESTREAM data for the table when a clustered index is created. The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.
filestream_filegroup_name is the name of a FILESTREAM filegroup. The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.
If the table is partitioned, the FILESTREAM_ON clause must be included, and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. Otherwise, an error is raised.
If the table isn't partitioned, the FILESTREAM column can't be partitioned. FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.
FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table doesn't contain a FILESTREAM column.
For more information, see FILESTREAM.
Indicates that the new column is a row GUID column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. Applying the ROWGUIDCOL property enables the column to be referenced using $ROWGUID . The ROWGUIDCOL property can be assigned only to a uniqueidentifier column. User-defined data type columns can't be designated with ROWGUIDCOL.
The ROWGUIDCOL property doesn't enforce uniqueness of the values stored in the column. ROWGUIDCOL also doesn't automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID or NEWSEQUENTIALID function on INSERT statements or use these functions as the default for the column.
Specifies encrypting columns by using the Always Encrypted feature.
Indicates that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns can't be designated as NOT NULL. For additional restrictions and more information about sparse columns, see Use Sparse Columns.
Applies to: SQL Server 2016 (13.x) and later.
Specifies a dynamic data mask. mask_function is the name of the masking function with the appropriate parameters. Four functions are available:
Requires ALTER ANY MASK permission.
For function parameters, see Dynamic Data Masking.
Applies to: SQL Server 2008 R2 (10.50.x) and later.
Valid only for varbinary(max) columns. Specifies FILESTREAM storage for the varbinary(max) BLOB data.
The table must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint. The GUID value for the column must be supplied either by an application when inserting data, or by a DEFAULT constraint that uses the NEWID () function.
The ROWGUIDCOL column can't be dropped and the related constraints can't be changed while there is a FILESTREAM column defined for the table. The ROWGUIDCOL column can be dropped only after the last FILESTREAM column is dropped.
When the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system.
Specifies the collation for the column. Collation name can be either a Windows collation name or an SQL collation name. collation_name is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned either the collation of the user-defined data type, if the column is of a user-defined data type, or the default collation of the database.
For more information about the Windows and SQL collation names, see Windows Collation Name and SQL Collation Name.
For more information, see COLLATE.
An optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.
Important We recommend that you specify NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets of ALTER TABLE. SWITCH operations. Doing this makes sure that any CHECK constraints on partitioning columns do not have to check for null values.
Important Documenting WITH FILLFACTOR = fillfactor as the only index option that applies to PRIMARY KEY or UNIQUE constraints is maintained for backward compatibility, but will not be documented in this manner in future releases.
The name of the column set. A column set is an untyped XML representation that combines all of the sparse columns of a table into a structured output. For more information about column sets, see Use Column Sets.
Applies to: SQL Server 2016 (13.x) and later, and Azure SQL Database.
Specifies the names of the columns that the system will use to record the period for which a record is valid. Use this argument with the GENERATED ALWAYS AS ROW < START | END >and WITH SYSTEM_VERSIONING = ON arguments to create a temporal table. For more information, see Temporal Tables.
Applies to: SQL Server 2016 (13.x) and later, and Azure SQL Database.
For a memory-optimized, delay specifies the minimum number of minutes a row must remain in the table, unchanged, before it is eligible for compression into the columnstore index. SQL Server selects specific rows to compress according to their last update time. For example, if rows are changing frequently during a two-hour period of time, you could set COMPRESSION_DELAY = 120 Minutes to ensure updates are completed before SQL Server compresses the row.
For a disk-based table, delay specifies the minimum number of minutes a delta rowgroup in the CLOSED state must remain in the delta rowgroup before SQL Server can compress it into the compressed rowgroup. Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.
The default is 0 minutes.
For recommendations on when to use COMPRESSION_DELAY , see Get started with Columnstore for real time operational analytics
Specifies one or more table options.
Specifies the data compression option for the specified table, partition number, or range of partitions. The options are as follows:
For more information, see Data Compression.
Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.
Specifies the XML compression option for any xml data type columns in the table. The options are as follows:
Specifies the partitions to which the DATA_COMPRESSION or XML_COMPRESSION settings apply. If the table isn't partitioned, the ON PARTITIONS argument will generate an error. If the ON PARTITIONS clause isn't provided, the DATA_COMPRESSION option will apply to all partitions of a partitioned table.
partition_number_expression can be specified in the following ways:
can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8) .
To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:
WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) );
You can also specify the XML_COMPRESSION option more than once, for example:
WITH ( XML_COMPRESSION = OFF ON PARTITIONS (1), XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8), XML_COMPRESSION = OFF ON PARTITIONS (3, 5) );
Specifies one or more index options. For a complete description of these options, see CREATE INDEX.
PAD_INDEX =When ON, the percentage of free space specified by FILLFACTOR is applied to the intermediate level pages of the index. When OFF or a FILLFACTOR value it not specified, the intermediate level pages are filled to near capacity leaving enough space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages. The default is OFF.
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0. Fill factor values 0 and 100 are the same in all respects.
IGNORE_DUP_KEY =Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.
IGNORE_DUP_KEY can't be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
To view IGNORE_DUP_KEY , use sys.indexes.
In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON .
STATISTICS_NORECOMPUTE =When ON, out-of-date index statistics aren't automatically recomputed. When OFF, automatic statistics updating are enabled. The default is OFF.
ALLOW_ROW_LOCKS =When ON, row locks are allowed when you access the index. The Database Engine determines when row locks are used. When OFF, row locks aren't used. The default is ON.
ALLOW_PAGE_LOCKS =When ON, page locks are allowed when you access the index. The Database Engine determines when page locks are used. When OFF, page locks aren't used. The default is ON.
OPTIMIZE_FOR_SEQUENTIAL_KEY =Applies to: SQL Server 2019 (15.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Specifies whether or not to optimize for last-page insert contention. The default is OFF. See the Sequential Keys section of the CREATE INDEX page for more information.
Applies to: SQL Server 2012 (11.x) and later.
Specifies the windows-compatible FileTable directory name. This name should be unique among all the FileTable directory names in the database. Uniqueness comparison is case-insensitive, regardless of collation settings. If this value isn't specified, the name of the FileTable is used.
Applies to: SQL Server 2012 (11.x) and later. Azure SQL Database and Azure SQL Managed Instance do not support FILETABLE .
Specifies the name of the collation to be applied to the Name column in the FileTable. The collation must be case-insensitive to comply with Windows operating system file naming semantics. If this value isn't specified, the database default collation is used. If the database default collation is case-sensitive, an error is raised, and the CREATE TABLE operation fails.
Applies to: SQL Server 2012 (11.x) and later. Azure SQL Database and Azure SQL Managed Instance do not support FILETABLE .
Specifies the name to be used for the primary key constraint that is automatically created on the FileTable. If this value isn't specified, the system generates a name for the constraint.
Applies to: SQL Server 2012 (11.x) and later. Azure SQL Database and Azure SQL Managed Instance do not support FILETABLE .
Specifies the name to be used for the unique constraint that is automatically created on the stream_id column in the FileTable. If this value isn't specified, the system generates a name for the constraint.
Applies to: SQL Server 2012 (11.x) and later. Azure SQL Database and Azure SQL Managed Instance do not support FILETABLE .
Specifies the name to be used for the unique constraint that is automatically created on the parent_path_locator and name columns in the FileTable. If this value isn't specified, the system generates a name for the constraint.
Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Enables system versioning of the table if the datatype, nullability constraint, and primary key constraint requirements are met. The system will record the history of each record in the system-versioned table in a separate history table. If the HISTORY_TABLE argument isn't used, the name of this history table will be MSSQL_TemporalHistoryFor . If the name of a history table is specified during history table creation, you must specify the schema and table name.
If the history table doesn't exist, the system generates a new history table matching the schema of the current table in the same filegroup as the current table, creating a link between the two tables and enables the system to record the history of each record in the current table in the history table. By default, the history table is PAGE compressed.
If the HISTORY_TABLE argument is used to create a link to and use an existing history table, the link is created between the current table and the specified table. If current table is partitioned, the history table is created on default file group because partitioning configuration isn't replicated automatically from the current table to the history table. When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records don't overlap. Performing the data consistency check is the default.
Use this argument with the PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW < START | END >arguments to enable system versioning on a table. For more information, see Temporal Tables. Use this argument with the WITH LEDGER = ON argument to create an updatable ledger table. Using existing history tables with ledger tables isn't allowed.
Applies to: SQL Server 2016 (13.x) and later.
Creates the new table with Stretch Database enabled or disabled. For more info, see Stretch Database.
Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Enabling Stretch Database for a table
When you enable Stretch for a table by specifying ON , you can optionally specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration. The default value is MIGRATION_STATE = OUTBOUND . For more info about enabling Stretch for a table, see Enable Stretch Database for a table.
Prerequisites. Before you enable Stretch for a table, you have to enable Stretch on the server and on the database. For more info, see Enable Stretch Database for a database.
Permissions. Enabling Stretch for a database or a table requires db_owner permissions. Enabling Stretch for a table also requires ALTER permissions on the table.
Applies to: SQL Server 2016 (13.x) and later.
Optionally specifies a filter predicate to select rows to migrate from a table that contains both historical and current data. The predicate must call a deterministic inline table-valued function. For more info, see Enable Stretch Database for a table and Select rows to migrate by using a filter function.
If you provide a filter predicate that performs poorly, data migration also performs poorly. Stretch Database applies the filter predicate to the table by using the CROSS APPLY operator.
If you don't specify a filter predicate, the entire table is migrated.
When you specify a filter predicate, you also have to specify MIGRATION_STATE.
MIGRATION_STATE =Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Applies to: Azure SQL Edge only
Enables retention policy based cleanup of old or aged data from tables within a database. For more information, see Enable and Disable Data Retention. The following parameters must be specified for data retention to be enabled.
Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, and Azure SQL Managed Instance. Azure SQL Managed Instance does not support memory optimized tables in General Purpose tier.
The value ON indicates that the table is memory optimized. Memory-optimized tables are part of the In-Memory OLTP feature, which is used to optimize the performance of transaction processing. To get started with In-Memory OLTP see Quickstart 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance. For more in-depth information about memory-optimized tables, see Memory-Optimized Tables.
The default value OFF indicates that the table is disk-based.
Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
The value of SCHEMA_AND_DATA indicates that the table is durable, meaning that changes are persisted on disk and survive restart or failover. SCHEMA_AND_DATA is the default value.
The value of SCHEMA_ONLY indicates that the table is non-durable. The table schema is persisted but any data updates aren't persisted upon a restart or failover of the database. DURABILITY = SCHEMA_ONLY is only allowed with MEMORY_OPTIMIZED = ON .
When a table is created with DURABILITY = SCHEMA_ONLY , and READ_COMMITTED_SNAPSHOT is subsequently changed using ALTER DATABASE , data in the table will be lost.
Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Indicates the number of buckets that should be created in the hash index. The maximum value for BUCKET_COUNT in hash indexes is 1,073,741,824. For more information about bucket counts, see Indexes for Memory-Optimized Tables.
Bucket_count is a required argument.
Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, and Azure SQL Managed Instance.
Column and table indexes can be specified as part of the CREATE TABLE statement. For details about adding and removing indexes on memory-optimized tables, see Altering Memory-Optimized Tables
Applies to: SQL Server 2022 (16.x), Azure SQL Database, and Azure SQL Managed Instance.
If the statement creates a ledger table, the ENABLE LEDGER permission is required.
Indicates whether the table being created is a ledger table (ON) or not (OFF). The default is OFF. If the APPEND_ONLY = ON option is specified, the system creates an append-only ledger table allowing only inserting new rows. Otherwise, the system creates an updatable ledger table. An updatable ledger table also requires the SYSTEM_VERSIONING = ON argument. An updatable ledger table must also be a system-versioned table. However, an updatable ledger table doesn't have to be a temporal table (it doesn't require the PERIOD FOR SYSTEM_TIME parameter). If the history table is specified with LEDGER = ON and SYSTEM_VERSIONING = ON , it must not reference an existing table.
A ledger database (a database created with the LEDGER = ON option) only allows the creation of ledger tables. Attempts to create a table with LEDGER = OFF will raise an error. Each new table by default is created as an updatable ledger table, even if you don't specify LEDGER = ON , and will be created with default values for all other parameters.
An updatable ledger table must contain four GENERATED ALWAYS columns, exactly one column defined with each of the following arguments:
An append-only ledger table must contain exactly one column defined with each of the following arguments:
If any of the required generated always columns isn't defined in the CREATE TABLE statement and the statement includes LEDGER = ON , the system will automatically attempt to add the column using an applicable column definition from the below list. If there is a name conflict with an already defined column, the system will raise an error.
[ledger_start_transaction_id] BIGINT GENERATED ALWAYS AS TRANSACTION_ID START HIDDEN NOT NULL [ledger_end_transaction_id] BIGINT GENERATED ALWAYS AS TRANSACTION_ID END HIDDEN NULL [ledger_start_sequence_number] BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER START HIDDEN NOT NULL [ledger_end_sequence_number] BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER END HIDDEN NULL
The specifies the schema and the name of the ledger view the system automatically creates and links to the table. If the option isn't specified, the system generates the ledger view name by appending _Ledger to the name of the table being created ( database_name.schema_name.table_name ). If a view with the specified or generated name exists, the system will raise an error. If the table is an updatable ledger table, the ledger view is created as a union on the table and its history table.
Each row in the ledger view represents either the creation or deletion of a row version in the ledger table. The ledger view contains all columns of the ledger table, except the generated always columns listed above. The ledger view also contains the following additional columns:
Column name | Data type | Description |
---|---|---|
Specified using the TRANSACTION_ID_COLUMN_NAME option. ledger_transaction_id if not specified. | bigint | The ID of the transaction that created or deleted a row version. |
Specified using the SEQUENCE_NUMBER_COLUMN_NAME option. ledger_sequence_number if not specified. | bigint | The sequence number of a row-level operation within the transaction on the table. |
Specified using the OPERATION_TYPE_COLUMN_NAME option. ledger_operation_type if not specified. | tinyint | Contains 1 ( INSERT ) or 2 ( DELETE ). Inserting a row into the ledger table produces a new row in the ledger view containing 1 in this column. Deleting a row from the ledger table produces a new row in the ledger view containing 2 in this column. Updating a row in the ledger table produces two new rows in the ledger view. One row contains 2 ( DELETE ) and the other row contains 1 ( INSERT ) in this column. |
Specified using the OPERATION_TYPE_DESC_COLUMN_NAME option. ledger_operation_type_desc if not specified. | nvarchar(128) | Contains INSERT or DELETE . See above for details. |
Transactions that include creating ledger table are captured in sys.database_ledger_transactions.
Specifies a ledger option.
Specifies the name of the ledger view and the names of additional columns the system adds to the ledger view.
Specifies whether the ledger table being created is append-only or updatable. The default is OFF .
Specifies one or more ledger view options. Each of the ledger view option specifies a name of a column, the system will add to the view, in addition to the columns defined in the ledger table.
Specifies the name of the column storing the ID of the transaction that created or deleted a row version. The default column name is ledger_transaction_id .
Specifies the name of the columns storing the sequence number of a row-level operation within the transaction on the table. The default column name is ledger_sequence_number .
Specifies the name of the columns storing the operation type ID. The default column name is ledger_operation_type.
Specifies the name of the columns storing the operation type description. The default column name is ledger_operation_type_desc .
For information about the number of allowed tables, columns, constraints and indexes, see Maximum Capacity Specifications for SQL Server.
Space is generally allocated to tables and indexes in increments of one extent at a time. When the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE is set to TRUE, or always prior to SQL Server 2016 (13.x), when a table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated every time the currently allocated extents become full. For a report about the amount of space allocated and used by a table, execute sp_spaceused .
The Database Engine doesn't enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata for the table, even if the option is set to OFF when the table is created.
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables can't be partitioned.
Prefix local temporary table names with single number sign ( #table_name ), and prefix global temporary table names with a double number sign ( ##table_name ).
Transact-SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:
CREATE TABLE #MyTempTable ( col1 INT PRIMARY KEY ); INSERT INTO #MyTempTable VALUES (1);
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
If you include a schema_name when you create or access a temporary table, it is ignored. All temporary tables are created in the dbo schema.
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several sessions, the Database Engine must be able to distinguish the tables created by the different sessions. The Database Engine does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sys.sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name can't exceed 116 characters.
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:
A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it isn't defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. This is shown in the following example.
CREATE PROCEDURE dbo.Test2 AS CREATE TABLE #t (x INT PRIMARY KEY); INSERT INTO #t VALUES (2); SELECT Test2Col = x FROM #t; GO CREATE PROCEDURE dbo.Test1 AS CREATE TABLE #t (x INT PRIMARY KEY); INSERT INTO #t VALUES (1); SELECT Test1Col = x FROM #t; EXEC Test2; GO CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (99); GO EXEC Test1; GO
Here is the result set.
(1 row(s) affected) Test1Col ----------- 1 (1 row(s) affected) Test2Col ----------- 2
When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions except for FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message that states the constraint was skipped. The table is still created without the FOREIGN KEY constraints. Temporary tables can't be referenced in FOREIGN KEY constraints.
If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure can't be executed simultaneously by multiple users.
Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users' sessions across the whole SQL Server instance. For information on SQL table types, see the above section on Create Tables.
Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users' sessions within the same Azure SQL Database. User sessions from other databases can't access global temporary tables.
Global temporary tables for Azure SQL Database follow the same syntax and semantics that SQL Server uses for temporary tables. Similarly, global temporary stored procedures are also scoped to the database level in Azure SQL Database. Local temporary tables (initiated with # table name) are also supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses. See the above section on Temporary Tables.
This feature is available for Azure SQL Database.
For troubleshooting tempdb , see How to Monitor tempdb use.