CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 中创建新表。Creates a new table in SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database.

备注

关于 SQL 数据仓库SQL Data Warehouse 语法,请请参阅 CREATE TABLE(Azure SQL 数据仓库)For SQL 数据仓库SQL Data Warehouse syntax, see CREATE TABLE (Azure SQL Data Warehouse).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

简单语法Simple Syntax

--Simple CREATE TABLE Syntax (common if not using options)
CREATE TABLE
    { database_name.schema_name.table_name. | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,...n ] )
[ ; ]

完整语法Full Syntax

--Disk-Based CREATE TABLE Syntax
CREATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ AS FileTable ]
    ( {   <column_definition>
        | <computed_column_definition>
        | <column_set_definition>
        | [ <table_constraint> ] [ ,... n ]
        | [ <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 }
  | 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> | <range> }
       [ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
--Memory optimized CREATE TABLE Syntax
CREATE TABLE
    { database_name.schema_name.table_name | schema_name.table_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 } ] ) ]
  
}

参数Arguments

database_name database_name
要在其中创建表的数据库的名称。Is the name of the database in which the table is created. database_name 须指定现有数据库的名称 。database_name must specify the name of an existing database. 如果未指定,则 database_name 默认为当前数据库 。If not specified, database_name defaults to the current database. 当前连接的登录名必须与 database_name 所指定数据库中的一个现有用户 ID 关联,并且该用户 ID 必须具有 CREATE TABLE 权限 。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.

schema_name schema_name
新表所属架构的名称。Is the name of the schema to which the new table belongs.

table_name table_name
新表的名称。Is the name of the new table. 表名必须遵循有关标识符的规则。Table names must follow the rules for identifiers. 除了本地临时表名(以单个数字符号 (#) 为前缀的名称)不能超过 116 个字符外,table_name 最多可包含 128 个字符 。table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

AS FileTableAS FileTable
适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

将新表创建为 FileTable。Creates the new table as a FileTable. 您无需指定列,因为 FileTable 具有固定架构。You do not specify columns because a FileTable has a fixed schema. 有关详细信息,请参阅 FileTablesFor more information, see FileTables.

column_name column_name
computed_column_expression computed_column_expression
定义计算列的值的表达式。Is an expression that defines the value of a computed column. 计算列是虚拟列,并非实际存储在表中,除非此列标记为 PERSISTED。A computed column is a virtual column that is not 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. 例如,计算列可以定义为 cost AS price * qty 。表达式可以是非计算列的名称、常量、函数、变量以及通过一个或多个运算符连接的上述元素的任意组合。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 cannot be a subquery or contain alias data types.

计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何可使用正则表达式的其他位置,但下列情况除外: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:

  • 计算列必须标记为 PERSISTED,才能参与 FOREIGN KEY 或 CHECK 约束。Computed columns must be marked PERSISTED to participate in a FOREIGN KEY or CHECK constraint.

  • 如果计算列的值由具有确定性的表达式定义,并且索引列中可使用计算结果的数据类型,则可将该列用作索引中的键列,或者用作 PRIMARY KEY 或 UNIQUE 约束的一部分。A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

    例如,如果表中含有整数列 a 和 b,则可以对计算列 a+b 创建索引,但不能对计算列 a+DATEPART(dd, GETDATE()) 创建索引,因为在以后的调用中,其值可能发生改变 。For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

  • 计算列不能作为 INSERT 或 UPDATE 语句的目标。A computed column cannot be the target of an INSERT or UPDATE statement.

备注

表中计算列所使用的列值因行而异,因此计算列的每一行可能有不同的值。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.

计算列的为 Null 性是由数据库引擎Database Engine根据使用的表达式自动确定的。Based on the expressions that are used, the nullability of computed columns is determined automatically by the 数据库引擎Database Engine. 即使只有不可为空的列,大多数表达式的结果也认为是可为空的,因为可能的下溢或溢出也将生成 NULL 结果。The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows also produce NULL results. 使用带 AllowsNull 属性的 COLUMNPROPERTY 函数以调查表中任何计算列的为 Null 性 。Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. 通过使用 check_expression 常量指定 ISNULL(常量是替换所有 NULL 结果的非空值),可以将可为 Null 的表达式转换为不可为 Null 的表达式 。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. 对于基于公共语言运行时 (CLR) 用户定义类型表达式的计算列,需要对此类型有 REFERENCES 权限。REFERENCES permission on the type is required for computed columns based on common language runtime (CLR) user-defined type expressions.

PERSISTEDPERSISTED
指定SQL Server 数据库引擎SQL Server Database Engine将在表中物理存储计算值,并在计算列依赖的任何其他列发生更新时对这些计算值进行更新。Specifies that the SQL Server 数据库引擎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. 将计算列标记为 PERSISTED可以对具有确定性但不精确的计算列创建索引。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. 必须将用作已分区表的分区依据列的任何计算列显式标记为 PERSISTEDAny computed columns that are used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. 指定 PERSISTED 时,computed_column_expression 必须具有确定性 。computed_column_expression must be deterministic when PERSISTED is specified.

ON { partition_scheme | filegroup | "default" }ON { partition_scheme | filegroup | "default" }
指定存储表的分区架构或文件组。Specifies the partition scheme or filegroup on which the table is stored. 如果指定了 partition_scheme,则该表将成为已分区表,其分区存储在 partition_scheme 所指定的一个或多个文件组的集合中 。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. 如果指定了 filegroup,则该表将存储在已命名文件组中 。If filegroup is specified, the table is stored in the named filegroup. 数据库中必须存在该文件组。The filegroup must exist within the database. 如果指定了 "default",或者根本未指定 ON,表则存储在默认文件组中 。If "default" is specified, or if ON is not specified at all, the table is stored on the default filegroup. CREATE TABLE 中指定的表的存储机制以后不能进行更改。The storage mechanism of a table as specified in CREATE TABLE cannot be subsequently altered.

ON {partition_scheme | filegroup | "default"} 也可以在 PRIMARY KEY 约束或 UNIQUE 约束中指定 。ON {partition_scheme | filegroup | "default"} can also be specified in a PRIMARY KEY or UNIQUE constraint. 这些约束会创建索引。These constraints create indexes. 如果 filegroup 未指定,则索引会存储在已命名文件组中 。If filegroup is specified, the index is stored in the named filegroup. 如果指定了 "default",或者根本未指定 ON,索引则将与表存储在同一文件组中 。If "default" is specified, or if ON is not specified at all, the index is stored in the same filegroup as the table. 如果 PRIMARY KEYUNIQUE 约束创建聚集索引,则表的数据页将与索引存储在同一文件组中。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. 如果指定了 CLUSTERED 或约束另外创建了聚集索引,并且指定的 partition_scheme 不同于表定义的 partition_scheme 或 filegroup 或相反,则只接受约束定义,而忽略其他定义 。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.

备注

在此上下文中,default 不是关键字 。In this context, default is not a keyword. 它是默认文件组的标识符,并且必须进行分隔(类似于 ON "default" 或 ON [default]) 。It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [ default ]. 如果指定了 "default",当前会话的 QUOTED_IDENTIFIER 选项则必须为 ON 。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

在创建分区表后,请考虑将表的 LOCK_ESCALATION 选项设置为 AUTOAfter you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. 这可通过将锁升级到分区 (HoBT) 级而不是表级来改善并发性。This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table. 有关详细信息,请参阅 ALTER TABLEFor more information, see ALTER TABLE.

TEXTIMAGE_ON { filegroup| "default" }TEXTIMAGE_ON { filegroup| "default" }
指示 text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 和 CLR 用户定义类型的列(包括几何图形和地理)存储在指定文件组 。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_ONTEXTIMAGE_ON is not allowed if there are no large value columns in the table. 如果指定了 partition_scheme,则不能指定 TEXTIMAGE_ONTEXTIMAGE_ON cannot be specified if partition_scheme is specified. 如果指定了 "default",或者根本未指定 TEXTIMAGE_ON,较大值列则将存储在默认文件组中 。If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup. 以后不能对 CREATE TABLE 中指定的任何较大值列数据的存储进行更改。The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.

备注

Varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型 UDT 值直接存储在数据行中(最大限制为 8,000 个字节,只要记录中可以容纳此值)。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. 如果记录中容纳不下该值,则指针存储在行内,其余内容存储在 LOB 存储空间中的行外。If the value does not fit in the record, a pointer is sorted in-row and the rest is stored out of row in the LOB storage space. 0 是默认值,表示所有值都直接存储在数据行中。0 is the default value, which indicates that all values are stored directly in the data row.

TEXTIMAGE_ON 仅更改“LOB 存储空间”的位置,不影响数据存储在行内的时间。TEXTIMAGE_ON only changes the location of the "LOB storage space", it does not affect when data is stored in-row. 使用 sp_tableoption 的 large value types out of row 选项将整个 LOB 值存储在行外。Use large value types out of row option of sp_tableoption to store the entire LOB value out of the row.

在此上下文中,default 不是关键字。In this context, default is not a keyword. 它是默认文件组的标识符,并且必须进行分隔(类似于 TEXTIMAGE_ON "default"TEXTIMAGE_ON [default])。It is an identifier for the default filegroup and must be delimited, as in TEXTIMAGE_ON "default" or TEXTIMAGE_ON [default]. 如果指定了 "default",当前会话的 QUOTED_IDENTIFIER 选项则必须为 ON 。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

FILESTREAM_ON { partition_scheme_name | filegroup | " default" }FILESTREAM_ON { partition_scheme_name | filegroup | " default " }
适用范围SQL ServerSQL ServerSQL Server 2008 R2SQL Server 2008 R2SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2008 R2SQL Server 2008 R2 through SQL Server 2017SQL Server 2017). Azure SQL DatabaseAzure SQL Database 不支持 FILESTREAMdoes not support FILESTREAM.

指定 FILESTREAM 数据的文件组。Specifies the filegroup for FILESTREAM data.

如果表包含 FILESTREAM 数据并且已分区,则必须包含 FILESTREAM_ON 子句并指定 FILESTREAM 文件组的分区方案。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.

如果该表未分区,则无法对 FILESTREAM 列分区。If the table is not partitioned, the FILESTREAM column cannot be partitioned. 表的 FILESTREAM 数据必须存储在单个文件组中。FILESTREAM data for the table must be stored in a single filegroup. 此文件组是在 FILESTREAM_ON 子句中指定的。This filegroup is specified in the FILESTREAM_ON clause.

如果表未分区并且未指定 FILESTREAM_ON 子句,则使用设置了 DEFAULT 属性的 FILESTREAM 文件组。If the table is not partitioned and the FILESTREAM_ON clause is not specified, the FILESTREAM filegroup that has the DEFAULT property set is used. 如果没有 FILESTREAM 文件组,将引发错误。If there is no FILESTREAM filegroup, an error is raised.

与 ON 和 TEXTIMAGE_ON 一样,无法更改通过使用 CREATE TABLEFILESTREAM_ON 设置的值,但以下情况除外:As with ON and TEXTIMAGE_ON, the value set by using CREATE TABLE for FILESTREAM_ON cannot be changed, except in the following cases:

  • CREATE INDEX 语句将堆转换为聚集索引。A CREATE INDEX statement converts a heap into a clustered index. 在这种情况下,可以指定不同的 FILESTREAM 文件组、分区方案或 NULL。In this case, a different FILESTREAM filegroup, partition scheme, or NULL can be specified.
  • DROP INDEX 语句将聚集索引转换为堆。A DROP INDEX statement converts a clustered index into a heap. 在这种情况下,可以指定不同的 FILESTREAM 文件组、分区方案或 "default" 。In this case, a different FILESTREAM filegroup, partition scheme, or "default" can be specified.

FILESTREAM_ON <filegroup> 子句中的文件组或在分区方案中指定的每个 FILESTREAM 文件组须定义有一个文件。The filegroup in the FILESTREAM_ON <filegroup> clause, or each FILESTREAM filegroup that is named in the partition scheme, must have one file defined for the filegroup. 须使用 CREATE DATABASEALTER DATABASE 语句来定义此文件;否则,会引发错误。This file must be defined by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.

有关 FILESTREAM 相关话题,请参阅 二进制大型对象 - Blob 数据For related FILESTREAM topics, see Binary Large Object - Blob Data.

[ type_schema_name .[ type_schema_name. ] type_name ] type_name
指定列的数据类型以及该列所属的架构。Specifies the data type of the column, and the schema to which it belongs. 对于基于磁盘的表,可以为以下数据类型之一:For disk-based tables, the data type can be one of the following:

  • 系统数据类型A system data type
  • 基于 SQL ServerSQL Server 系统数据类型的别名类型。An alias type based on a SQL ServerSQL Server system data type. 必须首先用 CREATE TYPE 语句创建别名数据类型,然后才能将它们用于表定义中。Alias data types are created with the CREATE TYPE statement before they can be used in a table definition. CREATE TABLE 语句中,可以覆盖别名数据类型的 NULL 或 NOT NULL 赋值。The NULL or NOT NULL assignment for an alias data type can be overridden during the CREATE TABLE statement. 但是,长度规格不能更改;不能在 CREATE TABLE 语句中指定别名数据类型的长度。However, the length specification cannot be changed; the length for an alias data type cannot be specified in a CREATE TABLE statement.
  • CLR 用户定义类型。A CLR user-defined type. 必须首先用 CREATE TYPE 语句创建 CLR 用户定义类型,然后才能将它们用于表定义中。CLR user-defined types are created with the CREATE TYPE statement before they can be used in a table definition. 若要创建 CLR 用户定义类型的列,则需要对此类型具有 REFERENCES 权限。To create a column on CLR user-defined type, REFERENCES permission is required on the type.

如果 type_schema_name 未指定,则 SQL Server 数据库引擎SQL Server Database Engine 按照下列顺序引用 type_name :If type_schema_name is not specified, the SQL Server 数据库引擎SQL Server Database Engine references type_name in the following order:

  • SQL ServerSQL Server 系统数据类型。The SQL ServerSQL Server system data type.
  • 当前数据库中当前用户的默认架构。The default schema of the current user in the current database.
  • 当前数据库中的 dbo 架构。The dbo schema in the current database.

有关内存优化表的信息,请参阅 In-Memory OLTP 的受支持数据类型,获取受支持系统类型。For memory-optimized tables, see Supported Data Types for In-Memory OLTP for a list of supported system types.

precision precision
指定的数据类型的精度。Is the precision for the specified data type. 有关有效精度值的详细信息,请参阅精度、小数位数和长度For more information about valid precision values, see Precision, Scale, and Length.

scale scale
是指定数据类型的小数位数。Is the scale for the specified data type. 有关有效小数位数值的详细信息,请参阅精度、小数位数和长度For more information about valid scale values, see Precision, Scale, and Length.

max max
仅应用于 varchar、nvarchar 和 varbinary 数据类型,存储 2^31 个字节的字符、二进制数据以及 2^30 个字节的 Unicode 数据 。Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31 bytes of character and binary data, and 2^30 bytes of Unicode data.

CONTENTCONTENT
指定 column_name 中 xml 数据类型的每个实例都可包含多个顶级元素 。Specifies that each instance of the xml data type in column_name can contain multiple top-level elements. CONTENT 仅适用于 xml 数据类型,并且只有在同时指定了 xml_schema_collection 时才能指定 CONTENT 。CONTENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified. 如果未指定,则 CONTENT 为默认行为。If not specified, CONTENT is the default behavior.

DOCUMENTDOCUMENT
指定 column_name 中 xml 数据类型的每个实例仅可包含一个顶级元素 。Specifies that each instance of the xml data type in column_name can contain only one top-level element. DOCUMENT 仅适用于 xml 数据类型,并且只有在同时指定了 xml_schema_collection 时才能指定 DOCUMENT 。DOCUMENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified.

xml_schema_collection xml_schema_collection
仅适用于 xml 数据类型,用于将 XML 架构集合与该类型相关联 。Applies only to the xml data type for associating an XML schema collection with the type. 在架构中键入 xml 列之前,须先使用 CREATE XML SCHEMA COLLECTION 在数据库中创建该架构 。Before typing an xml column to a schema, the schema must first be created in the database by using CREATE XML SCHEMA COLLECTION.

DEFAULTDEFAULT
如果在插入过程中未显式提供值,则指定为列提供的值。Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT 定义可适用于除定义为 timestamp 或带 IDENTITY 属性的列以外的任何列 。DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. 如果为用户定义类型列指定了默认值,则该类型应当支持从 constant_expression 到用户定义类型的隐式转换 。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 定义。DEFAULT definitions are removed when the table is dropped. 只有常量值(例如字符串)、标量函数(系统函数、用户定义函数或 CLR 函数)或 NULL 可用作默认值。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. 为了与 SQL ServerSQL Server 的早期版本兼容,可以为 DEFAULT 分配约束名称。To maintain compatibility with earlier versions of SQL ServerSQL Server, a constraint name can be assigned to a DEFAULT.

constant_expression constant_expression
常量、NULL 或用作列默认值的系统函数。Is a constant, NULL, or a system function that is used as the default value for the column.

memory_optimized_constant_expression memory_optimized_constant_expression
常量、NULL 或支持用作列默认值的系统函数。Is a constant, NULL, or a system function that is supported in used as the default value for the column. 本机编译的存储过程中必须支持它。Must be supported in natively compiled stored procedures. 有关本机编译已存储进程中内置函数的详细信息,请参阅本机编译 T-SQL 模块的受支持的功能For more information about built-in functions in natively compiled stored procedures, see Supported Features for Natively Compiled T-SQL Modules.

IDENTITYIDENTITY
指示新列是标识列。Indicates that the new column is an identity column. 在表中添加新行时,数据库引擎Database Engine将为该列提供一个唯一的增量值。When a new row is added to the table, the 数据库引擎Database Engine provides a unique, incremental value for the column. 标识列通常与 PRIMARY KEY 约束一起用作表的唯一行标识符。Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以将 IDENTITY 属性分配给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列 。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. 不能对标识列使用绑定默认值和 DEFAULT 约束。Bound defaults and DEFAULT constraints cannot be used with an identity column. 必须同时指定种子和增量,或者两者都不指定。Both the seed and increment or neither must be specified. 如果二者都未指定,则取默认值 (1,1)。If neither is specified, the default is (1,1).

seed seed
是装入表的第一行所使用的值。Is the value used for the very first row loaded into the table.

increment increment
添加到上一加载行的标识值的增量值。Is the incremental value added to the identity value of the previous row loaded.

NOT FOR REPLICATIONNOT FOR REPLICATION
CREATE TABLE 语句中,可以为 IDENTITY 属性、FOREIGN KEY 约束和 CHECK 约束指定 NOT FOR REPLICATION 子句。In the CREATE TABLE statement, the NOT FOR REPLICATION clause can be specified for the IDENTITY property, FOREIGN KEY constraints, and CHECK constraints. 如果为 IDENTITY 属性指定了此子句,复制代理执行插入时,标识列中的值将不会增加。If this clause is specified for the IDENTITY property, values are not incremented in identity columns when replication agents perform inserts. 如果为约束指定了此子句,则当复制代理执行插入、更新或删除操作时,将不会强制执行此约束。If this clause is specified for a constraint, the constraint is not enforced when replication agents perform insert, update, or delete operations.

GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] [ NOT NULL ]GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] [ NOT NULL ]
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定系统将使用指定的 datetime2 列来记录记录有效的开始时间或记录有效的结束时间。Specifies that a specified datetime2 column will be used by the system to record either the start time for which a record is valid or the end time for which a record is valid. 此列必须定义为 NOT NULLThe column must be defined as NOT NULL. 如果尝试将此列定义为 NULL,系统将引发错误。If you attempt to specify them as NULL, the system will throw an error. 如果不为期间列显式指定 NOT NULL,系统则会将此列默认定义为 NOT NULLIf you do not explicitly specify NOT NULL for a period column, the system will define the column as NOT NULL by default. 将此参数与 PERIOD FOR SYSTEM_TIMEWITH SYSTEM_VERSIONING = ON 参数结合使用来对表启用系统版本控制。Use this argument in conjunction with the PERIOD FOR SYSTEM_TIME and WITH SYSTEM_VERSIONING = ON arguments to enable system versioning on a table. 有关详细信息,请参阅 Temporal TablesFor more information, see Temporal Tables.

可将一个或两个时间段列标记为 HIDDEN 标志,隐式隐藏这些列,这样 SELECT * FROM<table> 就不会返回这些列中的值 。You can mark one or both period columns with HIDDEN flag to implicitly hide these columns such that SELECT * FROM<table> does not return a value for those columns. 默认情况下,时间段列不会处于隐藏状态。By default, period columns are not hidden. 若要使用隐藏的列,则它必须显式包含在直接引用时态表的所有查询中。In order to be used, hidden columns must be explicitly included in all queries that directly reference the temporal table. 若要更改现有时间段列的 HIDDEN 特性,须先删除 PERIOD,再使用不同的隐藏标志重新创建 。To change the HIDDEN attribute for an existing period column, PERIOD must be dropped and recreated with a different hidden flag.

INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column_name [ ASC | DESC ] [ ,... n ] ) INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column_name [ ASC | DESC ] [ ,... n ] )
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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.

INDEX index_name CLUSTERED COLUMNSTORE INDEX index_name CLUSTERED COLUMNSTORE
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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 is not sorted in alphabetical or numeric order since the rows are organized to gain columnstore compression benefits.

INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] ) INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] )
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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.

ON partition_scheme_name ( column_name ) ON partition_scheme_name(column_name)
指定分区方案,该方案定义要将分区索引的分区映射到的文件组。Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. 须通过执行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME,使数据库中存在该分区方案。The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name 指定对已分区索引进行分区所依据的列 。column_name specifies the column against which a partitioned index will be partitioned. 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配 。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 不限于索引定义中的列 。column_name is not restricted to the columns in the index definition. 除了在对 UNIQUE 索引分区时,必须从用作唯一键的列中选择 column_name 外,还可以指定基表中的任何列 。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. 通过此限制,数据库引擎Database Engine可验证单个分区中的键值唯一性。This restriction allows the 数据库引擎Database Engine to verify uniqueness of key values within a single partition only.

备注

在对非唯一的聚集索引进行分区时,如果尚未指定分区依据列,则默认情况下数据库引擎Database Engine将在聚集索引键列表中添加分区依据列。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. 在对非唯一的非聚集索引进行分区时,如果尚未指定分区依据列,则数据库引擎Database Engine会添加分区依据列作为索引的非键(包含)列。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.

如果未指定 partition_scheme_name 或 filegroup 且该表已分区,则索引会与基础表使用相同分区依据列并被放入同一分区方案中 。If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

备注

您不能对 XML 索引指定分区方案。You cannot specify a partitioning scheme on an XML index. 如果基表已分区,则 XML 索引与该表使用相同的分区方案。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.

ON filegroup_name ON filegroup_name
为指定文件组创建指定索引。Creates the specified index on the specified filegroup. 如果未指定位置且表或视图尚未分区,则索引将与基础表或视图使用相同的文件组。If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. 该文件组必须已存在。The filegroup must already exist.

ON "default" ON "default"
为默认文件组创建指定索引。Creates the specified index on the default filegroup.

在此上下文中,“default”一词不是关键字。The term default, in this context, is not a keyword. 它是默认文件组的标识符,并且必须进行分隔(类似于 ON "default" 或 ON [default]) 。It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. 如果指定了 "default",当前会话的 QUOTED_IDENTIFIER 选项则必须为 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
适用范围SQL ServerSQL ServerSQL Server 2008 R2SQL Server 2008 R2SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2008 R2SQL Server 2008 R2 through SQL Server 2017SQL Server 2017).

在创建聚集索引时,指定表的 FILESTREAM 数据的位置。Specifies the placement of FILESTREAM data for the table when a clustered index is created. FILESTREAM_ON 子句用于将 FILESTREAM 数据移动到不同的 FILESTREAM 文件组或分区方案。The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.

filestream_filegroup_name 是 FILESTREAM 文件组的名称 。filestream_filegroup_name is the name of a FILESTREAM filegroup. 该文件组须包含一个使用 CREATE DATABASEALTER DATABASE 语句为该文件组定义的文件;否则,会引发错误。The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.

如果表已分区,则必须包含 FILESTREAM_ON 子句并且必须指定 FILESTREAM 文件组的分区方案,此分区方案需使用与此表分区方案相同的分区功能和分区列。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.

如果该表未分区,则无法对 FILESTREAM 列分区。If the table is not partitioned, the FILESTREAM column cannot be partitioned. 此表的 FILESTREAM 数据必须存储在一个由 FILESTREAM_ON 子句指定的文件组中。FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.

如果正在创建一个聚集索引并且此表不包含 FILESTREAM 列,则可以在 CREATE INDEX 语句中指定 FILESTREAM_ON NULLFILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

有关详细信息,请参阅 FILESTREAMFor more information, see FILESTREAM.

ROWGUIDCOLROWGUIDCOL
指示新列是行 GUID 列。Indicates that the new column is a row GUID column. 对于每个表,只能将其中的一个 uniqueidentifier 列指定为 ROWGUIDCOL 列 。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. 应用 ROWGUIDCOL 属性能够实现通过使用 $ROWGUID 引用列。Applying the ROWGUIDCOL property enables the column to be referenced using $ROWGUID. ROWGUIDCOL 属性只能分配给 uniqueidentifier 列 。The ROWGUIDCOL property can be assigned only to a uniqueidentifier column. 用户定义数据类型列不能使用 ROWGUIDCOL 指定。User-defined data type columns cannot be designated with ROWGUIDCOL.

ROWGUIDCOL 属性并不强制列中所存储值的唯一性。The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. ROWGUIDCOL 也不会为插入表的新行自动生成值。ROWGUIDCOL also does not automatically generate values for new rows inserted into the table. 若要为每列生成唯一值,请使用 INSERT 语句上的 NEWIDNEWSEQUENTIALID 函数,或将这些函数用作该列的默认值。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.

ENCRYPTED WITHENCRYPTED WITH
使用 Always Encrypted 功能指定加密列。Specifies encrypting columns by using the Always Encrypted feature.

COLUMN_ENCRYPTION_KEY = key_name COLUMN_ENCRYPTION_KEY = key_name
指定列加密密钥。Specifies the column encryption key. 有关详细信息,请参阅 CREATE COLUMN ENCRYPTION KEYFor more information, see CREATE COLUMN ENCRYPTION KEY.

ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED }ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED }
确定性加密 使用一种对任何给定的纯文本值始终生成相同的加密值的方法。Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. 使用确定性加密可基于加密值进行下列操作:使用相等性比较进行搜索、分组、使用相等性联接联接各表,此外,还允许未经授权的用户通过检查加密列中的模式来猜测加密值的相关信息。Using deterministic encryption allows searching using equality comparison, grouping, and joining tables using equality joins based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. 只有使用相同的列加密密钥对两列进行加密,才能在已进行确定性加密的该列上联结两个表。Joining two tables on columns encrypted deterministically is only possible if both columns are encrypted using the same column encryption key. 确定性加密必须使用具有字符列的 binary2 排序顺序的列排序规则。Deterministic encryption must use a column collation with a binary2 sort order for character columns.

随机加密 使用一种以更不可预测地方式加密数据的方法。Randomized encryption uses a method that encrypts data in a less predictable manner. 随机加密更为安全,但会阻止对加密列进行任何计算和索引编制,除非你的 SQL Server 实例支持具有安全 enclave 的 Always Encrypted。Randomized encryption is more secure, but it prevents any computations and indexing on encrypted columns, unless your SQL Server instance supports Always Encrypted with secure enclaves. 请参阅具有安全 Enclave 的 Always Encrypted 以了解详细信息。Please see Always Encrypted with secure enclaves for details.

如果使用的是 Always Encrypted(不带安全 enclave),请对要使用参数或分组参数搜索的列使用确定性加密,例如政府 ID 号。If you are using Always Encrypted (without secure enclaves), use deterministic encryption for columns that will be searched with parameters or grouping parameters, for example a government ID number. 对以下数据使用随机加密,即未与其他记录分组的数据、未用于联结表的数据、因使用其他列(例如事务号)查找包含已加密的兴趣列,而不用于搜索的数据。Use randomized encryption, for data such as a credit card number, which is not grouped with other records or used to join tables, and which is not searched for because you use other columns (such as a transaction number) to find the row which contains the encrypted column of interest.

如果使用的是具有安全 enclave 的 Always Encrypted,则随机加密是推荐的加密类型。If you are using Always Encrypted with secure enclaves, randomized encryption is a recommended encryption type.

列必须是符合条件的数据类型。Columns must be of a qualifying data type.

ALGORITHMALGORITHM
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

须是“AEAD_AES_256_CBC_HMAC_SHA_256” 。Must be 'AEAD_AES_256_CBC_HMAC_SHA_256'.

有关包括功能约束在内的详细信息,请参阅 Always EncryptedFor more information including feature constraints, see Always Encrypted.

SPARSESPARSE
指示列为稀疏列。Indicates that the column is a sparse column. 稀疏列已针对 NULL 值进行了存储优化。The storage of sparse columns is optimized for null values. 不能将稀疏列指定为 NOT NULL。Sparse columns cannot be designated as NOT NULL. 有关稀疏列的其他限制和详细信息,请参阅使用稀疏列For additional restrictions and more information about sparse columns, see Use Sparse Columns.

MASKED WITH ( FUNCTION = ' mask_function ') MASKED WITH ( FUNCTION = ' mask_function ')
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

指定动态数据掩码。Specifies a dynamic data mask. mask_function 是具有相应参数的掩码函数的名称 。mask_function is the name of the masking function with the appropriate parameters. 有四个函数可供选择:Four functions are available:

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

有关函数参数的信息,请参阅动态数据掩码For function parameters, see Dynamic Data Masking.

FILESTREAMFILESTREAM
适用范围:SQL ServerSQL ServerSQL Server 2008 R2SQL Server 2008 R2SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server (SQL Server 2008 R2SQL Server 2008 R2 through SQL Server 2017SQL Server 2017)

仅对 varbinary(max) 列有效 。Valid only for varbinary(max) columns. 请为 varbinary(max) BLOB 数据指定 FILESTREAM 存储 。Specifies FILESTREAM storage for the varbinary(max) BLOB data.

表中还必须包含一个具有 ROWGUIDCOL 特性的 uniqueidentifier 数据类型列 。The table must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. 此列不得为空值且必须具有 UNIQUE 或 PRIMARY KEY 单列约束。This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint. 该列的 GUID 值可由应用程序在插入数据时提供,也可由使用 NEWID () 函数的 DEFAULT 约束提供。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.

如果为表定义了 FILESTREAM 列,则不能删除 ROWGUIDCOL 列并且不能更改相关的约束。The ROWGUIDCOL column cannot be dropped and the related constraints cannot be changed while there is a FILESTREAM column defined for the table. 仅当删除了最后一个 FILESTREAM 列后,才能删除 ROWGUIDCOL 列。The ROWGUIDCOL column can be dropped only after the last FILESTREAM column is dropped.

当为某个列指定了 FILESTREAM 存储属性时,该列的所有值都将存储在文件系统上的 FILESTREAM 数据容器中。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.

COLLATE collation_name COLLATE collation_name
指定列的排序规则。Specifies the collation for the column. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or an SQL collation name. collation_name 仅适用于 char、varchar、text、nchar、nvarchar 和 ntext 数据类型列 。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.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 Windows 排序规则名称SQL 排序规则名称For more information about the Windows and SQL collation names, see Windows Collation Name and SQL Collation Name.

有关详细信息,请参阅 COLLATEFor more information, see COLLATE.

CONSTRAINTCONSTRAINT
可选关键字,表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY 或 CHECK 约束定义的开始。Is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

constraint_name constraint_name
是约束的名称。Is the name of a constraint. 约束名称必须在表所属的架构中唯一。Constraint names must be unique within the schema to which the table belongs.

NULL | NOT NULLNULL | NOT NULL
确定列中是否允许使用空值。Determine whether null values are allowed in the column. 严格来讲,NULL 不是约束,但可以像指定 NOT NULL 那样指定它。NULL is not strictly a constraint but can be specified just like NOT NULL. 只有同时指定了 PERSISTED 时,才能为计算列指定 NOT NULL。NOT NULL can be specified for computed columns only if PERSISTED is also specified.

PRIMARY KEYPRIMARY KEY
通过唯一索引对给定的一列或多列强制实体完整性的约束。Is a constraint that enforces entity integrity for a specified column or columns through a unique index. 每个表只能创建一个 PRIMARY KEY 约束。Only one PRIMARY KEY constraint can be created per table.

UNIQUEUNIQUE
一个约束,该约束通过唯一索引为一个或多个指定列提供实体完整性。Is a constraint that provides entity integrity for a specified column or columns through a unique index. 一个表可以有多个 UNIQUE 约束。A table can have multiple UNIQUE constraints.

CLUSTERED | NONCLUSTEREDCLUSTERED | NONCLUSTERED
指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY 约束默认为 CLUSTERED,UNIQUE 约束默认为 NONCLUSTERED。PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.

CREATE TABLE 语句中,可以只为一个约束指定 CLUSTERED。In a CREATE TABLE statement, CLUSTERED can be specified for only one constraint. 如果在为 UNIQUE 约束指定 CLUSTERED 的同时又指定了 PRIMARY KEY 约束,则 PRIMARY KEY 将默认为 NONCLUSTERED。If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.

FOREIGN KEY REFERENCESFOREIGN KEY REFERENCES
为列中的数据提供引用完整性的约束。Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY 约束要求列中的每个值在所引用的表中对应的被引用列中都存在。FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY 约束只能引用在所引用的表中是 PRIMARY KEY 或 UNIQUE 约束的列,或所引用的表中在 UNIQUE INDEX 内的被引用列。FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table. 计算列上的外键也必须标记为 PERSISTED。Foreign keys on computed columns must also be marked PERSISTED.

[ schema_name . ] referenced_table_name][ schema_name.] referenced_table_name]
FOREIGN KEY 约束引用的表名,以及该表所属架构的名称。Is the name of the table referenced by the FOREIGN KEY constraint, and the schema to which it belongs.

( ref_column [ , ... n ] ) 是 FOREIGN KEY 约束所引用的表中的一列或多列。( ref_column [ ,... n ] ) Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
指定当已创建表中的行具有引用关系并且被引用行已从父表中删除时将对这些行采取的操作。Specifies what action happens to rows in the table created, if those rows have a referential relationship and the referenced row is deleted from the parent table. 默认值为 NO ACTION。The default is NO ACTION.

NO ACTIONNO ACTION
数据库引擎Database Engine将引发错误,并回滚对父表中行的删除操作。The 数据库引擎Database Engine raises an error and the delete action on the row in the parent table is rolled back.

CASCADECASCADE
如果从父表中删除一行,则将从引用表中删除相应行。Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

SET NULLSET NULL
如果父表中对应的行被删除,则组成外键的所有值都将设置为 NULL。All the values that make up the foreign key are set to NULL if the corresponding row in the parent table is deleted. 若要执行此约束,外键列必须可为空值。For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULTSET DEFAULT
如果父表中对应的行被删除,则组成外键的所有值都将设置为默认值。All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is deleted. 若要执行此约束,所有外键列都必须有默认定义。For this constraint to execute, all foreign key columns must have default definitions. 如果某个列可为空值,并且未设置显式的默认值,则将使用 NULL 作为该列的隐式默认值。If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.

如果该表将包含在使用逻辑记录的合并发布中,则不要指定 CASCADE。Do not specify CASCADE if the table will be included in a merge publication that uses logical records. 有关逻辑记录的详细信息,请参阅通过逻辑记录对相关行的更改进行分组For more information about logical records, see Group Changes to Related Rows with Logical Records.

如果表中已存在 INSTEAD OF 触发器 ON DELETE,则无法定义 ON DELETE CASCADEON DELETE CASCADE cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table.

例如,在 AdventureWorks2012AdventureWorks2012 数据库中,ProductVendor 表与 Vendor 表有引用关系 。For example, in the AdventureWorks2012AdventureWorks2012 database, the ProductVendor table has a referential relationship with the Vendor table. ProductVendor.BusinessEntityID 外键引用 Vendor.BusinessEntityID 主键 。The ProductVendor.BusinessEntityID foreign key references the Vendor.BusinessEntityID primary key.

如果对 Vendor 表中的某行执行 DELETE 语句,并且为 ProductVendor.BusinessEntityID 指定 ON DELETE CASCADE 操作,则 数据库引擎Database Engine 将检查 ProductVendor 表中的一个或多个依赖行 。If a DELETE statement is executed on a row in the Vendor table, and an ON DELETE CASCADE action is specified for ProductVendor.BusinessEntityID, the 数据库引擎Database Engine checks for one or more dependent rows in the ProductVendor table. 如果存在依赖行,则 ProductVendor 表中的依赖行将随 Vendor 表中的被引用行一同删除 。If any exist, the dependent rows in the ProductVendor table are deleted, and also the row referenced in the Vendor table.

相反,如果指定了 NO ACTION,并且 ProductVendor 表中至少有一行引用了 Vendor 行,则 数据库引擎Database Engine 会引发错误并回滚对 Vendor 进行行的删除操作 。Conversely, if NO ACTION is specified, the 数据库引擎Database Engine raises an error and rolls back the delete action on the Vendor row if there is at least one row in the ProductVendor table that references it.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
指定在发生更改的表中,如果行有引用关系且引用的行在父表中被更新,则对这些行采取什么操作。Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. 默认值为 NO ACTION。The default is NO ACTION.

NO ACTIONNO ACTION
数据库引擎Database Engine将引发错误,并回滚对父表中相应行的更新操作。The 数据库引擎Database Engine raises an error, and the update action on the row in the parent table is rolled back.

CASCADECASCADE
如果在父表中更新了一行,则将在引用表中更新相应的行。Corresponding rows are updated in the referencing table when that row is updated in the parent table.

SET NULLSET NULL
如果更新了父表中的相应行,则会将构成外键的所有值设置为 NULL。All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated. 若要执行此约束,外键列必须可为空值。For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULTSET DEFAULT
如果更新了父表中的相应行,则会将构成外键的所有值都设置为其默认值。All the values that make up the foreign key are set to their default values when the corresponding row in the parent table is updated. 若要执行此约束,所有外键列都必须有默认定义。For this constraint to execute, all foreign key columns must have default definitions. 如果某个列可为空值,并且未设置显式的默认值,则将使用 NULL 作为该列的隐式默认值。If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.

如果此表将包含在使用逻辑记录的合并发布中,则不要指定 CASCADEDo not specify CASCADE if the table will be included in a merge publication that uses logical records. 有关逻辑记录的详细信息,请参阅通过逻辑记录对相关行的更改进行分组For more information about logical records, see Group Changes to Related Rows with Logical Records.

如果被更改的表中已有 INSTEAD OF 触发器 ON UPDATE,则不能定义 ON UPDATE CASCADESET NULLSET DEFAULTON UPDATE CASCADE, SET NULL, or SET DEFAULT cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the table that is being altered.

例如,在 AdventureWorks2012AdventureWorks2012 数据库中,ProductVendor 表与 Vendor 表有引用关系 :ProductVendor.BusinessEntity 外键引用 Vendor.BusinessEntityID 主键 。For example, in the AdventureWorks2012AdventureWorks2012 database, the ProductVendor table has a referential relationship with the Vendor table: ProductVendor.BusinessEntity foreign key references the Vendor.BusinessEntityID primary key.

如果对 Vendor 表中的行执行 UPDATE 语句,并且为 ProductVendor.BusinessEntityID 指定了 ON UPDATE CASCADE 操作,则 数据库引擎Database Engine 将检查 ProductVendor 表中的一个或多个依赖行 。If an UPDATE statement is executed on a row in the Vendor table, and an ON UPDATE CASCADE action is specified for ProductVendor.BusinessEntityID, the 数据库引擎Database Engine checks for one or more dependent rows in the ProductVendor table. 如果存在依赖行,则 ProductVendor 表中的依赖行将随 Vendor 表中的被引用行一同更新 。If any exist, the dependent rows in the ProductVendor table are updated, and also the row referenced in the Vendor table.

反之,如果指定了 NO ACTION,并且 ProductVendor 表中至少有一行引用 Vendor 行,则 数据库引擎Database Engine 将引发错误并回滚对 Vendor 行的更新操作 。Conversely, if NO ACTION is specified, the 数据库引擎Database Engine raises an error and rolls back the update action on the Vendor row if there is at least one row in the ProductVendor table that references it.

CHECKCHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. 计算列上的 CHECK 约束也必须标记为 PERSISTED。CHECK constraints on computed columns must also be marked PERSISTED.

logical_expression logical_expression
返回 TRUE 或 FALSE 的逻辑表达式。Is a logical expression that returns TRUE or FALSE. 别名数据类型不能作为表达式的一部分。Alias data types cannot be part of the expression.

column column
用括号括起来的一列或多列,在表约束中表示这些列用在约束定义中。Is a column or list of columns, in parentheses, used in table constraints to indicate the columns used in the constraint definition.

[ ASC | DESC ] [ ASC | DESC ]
指定加入到表约束中的一列或多列的排序顺序。Specifies the order in which the column or columns participating in table constraints are sorted. 默认值为 ASC。The default is ASC.

partition_scheme_name partition_scheme_name
分区方案的名称,该分区方案定义要将已分区表的分区映射到的文件组。Is the name of the partition scheme that defines the filegroups onto which the partitions of a partitioned table will be mapped. 数据库中必须存在该分区架构。The partition scheme must exist within the database.

[ partition_column_name .[ partition_column_name. ]]
指定对已分区表进行分区所依据的列。Specifies the column against which a partitioned table will be partitioned. 此列必须与 partition_scheme_name 在数据类型、长度和精度方面使用的分区函数中指定的列相匹配 。The column must match that specified in the partition function that partition_scheme_name is using in terms of data type, length, and precision. 必须将参与分区函数的计算列显式标记为 PERSISTED。A computed columns that participates in a partition function must be explicitly marked PERSISTED.

重要

建议您对分区表的分区列以及作为 ALTER TABLE...SWITCH 操作源或目标的非分区表指定 NOT NULL。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. 这样做可确保分区列上的所有 CHECK 约束都不必检查 Null 值。Doing this makes sure that any CHECK constraints on partitioning columns do not have to check for null values.

WITH FILLFACTOR = fillfactor WITH FILLFACTOR =fillfactor
指定 数据库引擎Database Engine 存储索引数据时每个索引页的填充程度。Specifies how full the 数据库引擎Database Engine should make each index page that is used to store the index data. 用户指定的 fillfactor 值的范围可以为 1 到 100 。User-specified fillfactor values can be from 1 through 100. 如果未指定值,则默认值为 0。If a value is not specified, the default is 0. 填充因子的值 0 和 100 在所有方面都是相同的。Fill factor values 0 and 100 are the same in all respects.

重要

将 WITH FILLFACTOR = fillfactor 记录为适用于 PRIMARY KEY 或 UNIQUE 约束的唯一索引选项是为了保持向后兼容,但在未来的版本中将不会以此方式进行记录 。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.

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
列集的名称。Is the name of the column set. 列集是一种非类型化的 XML 表示形式,它将表的所有稀疏列合并为一种结构化的输出。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.

PERIOD FOR SYSTEM_TIME (system_start_time_column_name , system_end_time_column_name ) PERIOD FOR SYSTEM_TIME (system_start_time_column_name , system_end_time_column_name )
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定系统用于记录有效记录时间段的列的名称。Specifies the names of the columns that the system will use to record the period for which a record is valid. 将此参数与 GENERATED ALWAYS AS ROW { START | END } 参数和 WITH SYSTEM_VERSIONING = ON 参数结合使用,启用表的系统版本控制。Use this argument in conjunction with the GENERATED ALWAYS AS ROW { START | END } and WITH SYSTEM_VERSIONING = ON arguments to enable system versioning on a table. 有关详细信息,请参阅 Temporal TablesFor more information, see Temporal Tables.

COMPRESSION_DELAYCOMPRESSION_DELAY
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure 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 ServerSQL Server 根据行的最近更新时间选择要进行压缩的特定行。selects specific rows to compress according to their last update time. 例如,如果行在两个小时内频繁更改,则可以设置 COMPRESSION_DELAY = 120 Minutes 以确保在 SQL Server 压缩此行之前完成更新。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.

对于基于磁盘的表,延迟指定增量行组中处于关闭状态的增量行组在 SQL ServerSQL Server 可以将它压缩为压缩行组之前必须保持为增量行组的最小分钟数。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 ServerSQL Server can compress it into the compressed rowgroup. 由于基于磁盘的表不对单个行跟踪插入和更新时间,因此 SQL ServerSQL Server 会将此延迟应用于处于关闭状态的增量行组。Since disk-based tables don't track insert and update times on individual rows, SQL ServerSQL Server applies the delay to delta rowgroups in the CLOSED state.

默认为 0 分钟。The default is 0 minutes.

有关何时使用 COMPRESSION_DELAY 的建议,请参阅开始使用实时运营分析的列存储索引For recommendations on when to use COMPRESSION_DELAY, please see Get started with Columnstore for real time operational analytics

< table_option> ::=< table_option> ::=
指定一个或多个表选项。Specifies one or more table options.

DATA_COMPRESSIONDATA_COMPRESSION
为指定的表、分区号或分区范围指定数据压缩选项。Specifies the data compression option for the specified table, partition number, or range of partitions. 选项如下所示:The options are as follows:

NONE
不压缩表或指定的分区。Table or specified partitions are not compressed.

ROWROW
使用行压缩来压缩表或指定的分区。Table or specified partitions are compressed by using row compression.

PAGEPAGE
使用页压缩来压缩表或指定的分区。Table or specified partitions are compressed by using page compression.

COLUMNSTORECOLUMNSTORE

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE 指定使用性能最高的列存储压缩进行压缩。COLUMNSTORE specifies to compress with the most performant columnstore compression. 这是典型选择。This is the typical choice.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE_ARCHIVE 会进一步将表或分区压缩得更小。COLUMNSTORE_ARCHIVE will further compress the table or partition to a smaller size. 这可用于存档,或者用于要求更小存储大小并且可以付出更多时间来进行存储和检索的其他情形。This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

有关详细信息,请参阅 Data CompressionFor more information, see Data Compression.

ON PARTITIONS ( { <partition_number_expression> | [ , ...n ] ) ON PARTITIONS ( { <partition_number_expression> | [ ,...n ] )
指定对其应用 DATA_COMPRESSION 设置的分区。Specifies the partitions to which the DATA_COMPRESSION setting applies. 如果未对表进行分区,则 ON PARTITIONS 参数将生成错误。If the table is not partitioned, the ON PARTITIONS argument will generate an error. 如果未提供 ON PARTITIONS 子句,DATA_COMPRESSION 选项则将应用于分区表的所有分区。If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option will apply to all partitions of a partitioned table.

可以按以下方式指定 partition_number_expression :partition_number_expression can be specified in the following ways:

  • 提供分区的分区号,例如:ON PARTITIONS (2)Provide the partition number of a partition, for example: ON PARTITIONS (2)
  • 提供若干单独分区的分区号,并用逗号分隔,例如:ON PARTITIONS (1, 5)Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5)
  • 同时提供范围和单独分区,例如:ON PARTITIONS (2, 4, 6 TO 8)Provide both ranges and individual partitions, for example: ON PARTITIONS (2, 4, 6 TO 8)

可以将 <range> 指定为由单词 TO 隔开的分区号,例如:ON PARTITIONS (6 TO 8)<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

若要为不同分区设置不同的数据压缩类型,请多次指定 DATA_COMPRESSION 选项,例如: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)
)

<index_option> ::=<index_option> ::=
指定一个或多个索引选项。Specifies one or more index options. 有关这些操作的完整说明,请参阅 CREATE INDEXFor a complete description of these options, see CREATE INDEX.

PAD_INDEX = { ON | OFF } PAD_INDEX = { ON | OFF }
如果为 ON,则 FILLFACTOR 指定的可用空间百分比将应用于该索引的中间级别页。When ON, the percentage of free space specified by FILLFACTOR is applied to the intermediate level pages of the index. 如果未指定 OFF 或 FILLFACTOR 值,则考虑到中间级别页的键集,将中间级别页填充到一个近似容量,以留出足够的空间来容纳至少一个索引的最大行。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. 默认为 OFF。The default is OFF.

FILLFACTOR = fillfactor FILLFACTOR =fillfactor
指定一个百分比,指示在数据库引擎Database Engine创建或修改索引的过程中,应将每个索引页面的叶级填充到什么程度。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 必须是 1 到 100 之间的整数 。fillfactor must be an integer value from 1 to 100. 默认值为 0。The default is 0. 填充因子的值 0 和 100 在所有方面都是相同的。Fill factor values 0 and 100 are the same in all respects.

IGNORE_DUP_KEY = { ON | OFF } IGNORE_DUP_KEY = { ON | OFF }
指定在插入操作尝试向唯一索引插入重复键值时的错误响应。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 当执行 CREATE INDEXALTER INDEXUPDATE 时,该选项无效。The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. 默认为 OFF。The default is OFF.

ONON
向唯一索引插入重复键值时将出现警告消息。A warning message will occur when duplicate key values are inserted into a unique index. 只有违反唯一性约束的行才会失败。Only the rows violating the uniqueness constraint will fail.

OFFOFF
向唯一索引插入重复键值时将出现错误消息。An error message will occur when duplicate key values are inserted into a unique index. 整个 INSERT 操作将被回滚。The entire INSERT operation will be rolled back.

对于针对视图创建的索引、非唯一索引、XML 索引、空间索引以及筛选的索引,IGNORE_DUP_KEY 不能设置为 ON。IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

若要查看 IGNORE_DUP_KEY,请使用 sys.indexesTo view IGNORE_DUP_KEY, use sys.indexes.

在后向兼容语法中,WITH IGNORE_DUP_KEY 等同于 WITH IGNORE_DUP_KEY = ONIn backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF } STATISTICS_NORECOMPUTE = { ON | OFF }
如果为 ON,则过期的索引统计信息不会自动重新计算。When ON, out-of-date index statistics are not automatically recomputed. 如果为 OFF,则启用自动统计信息更新。When OFF, automatic statistics updating are enabled. 默认为 OFF。The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF } ALLOW_ROW_LOCKS = { ON | OFF }
如果为 ON,则访问索引时允许使用行锁。When ON, row locks are allowed when you access the index. 数据库引擎Database Engine确定何时使用行锁。The 数据库引擎Database Engine determines when row locks are used. 如果为 OFF,则不使用行锁。When OFF, row locks are not used. 默认值为 ON。The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF } ALLOW_PAGE_LOCKS = { ON | OFF }
如果为 ON,则访问索引时允许使用页锁。When ON, page locks are allowed when you access the index. 数据库引擎Database Engine确定何时使用页锁。The 数据库引擎Database Engine determines when page locks are used. 如果为 OFF,则不使用页锁。When OFF, page locks are not used. 默认值为 ON。The default is ON.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF } 适用于SQL Server 2019SQL Server 2019 及更高版本。OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF } Applies to: SQL Server 2019SQL Server 2019 and later.
指定是否针对最后一页插入争用进行优化。Specifies whether or not to optimize for last-page insert contention. 默认为 OFF。The default is OFF. 有关详细信息,请参阅“CREATE INDEX”页的顺序键部分。See the Sequential Keys section of the CREATE INDEX page for more information.

FILETABLE_DIRECTORY = directory_name FILETABLE_DIRECTORY = directory_name

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

指定与 windows 兼容的 FileTable 目录名称。Specifies the windows-compatible FileTable directory name. 此名称应在数据库的所有 FileTable 目录名称中唯一。This name should be unique among all the FileTable directory names in the database. 无论排序规则如何设置,唯一性比较都不区分大小写。Uniqueness comparison is case-insensitive, regardless of collation settings. 如果未指定此值,则使用 filetable 这个名称。If this value is not specified, the name of the filetable is used.

FILETABLE_COLLATE_FILENAME = { collation_name | database_default } FILETABLE_COLLATE_FILENAME = { collation_name | database_default }

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017). Azure SQL DatabaseAzure SQL Database 不支持 FILETABLEdoes not support FILETABLE.

指定要应用于 FileTable 的“名称” 列的排序规则名称。Specifies the name of the collation to be applied to the Name column in the FileTable. 排序规则必须不区分大小写,以遵守 Windows 文件命名语义。The collation must be case-insensitive to comply with Windows file naming semantics. 如果未指定此值,则使用数据库默认排序规则。If this value is not specified, the database default collation is used. 如果数据库默认排序规则区分大小写,将引发错误,CREATE TABLE 操作将失败。If the database default collation is case-sensitive, an error is raised and the CREATE TABLE operation fails.

collation_name collation_name
不区分大小写的排序规则的名称。The name of a case-insensitive collation.

database_defaultdatabase_default
指定应使用数据库的默认排序规则。Specifies that the default collation for the database should be used. 此排序规则必须不区分大小写。This collation must be case-insensitive.

FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name
适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

指定要对自动为 FileTable 创建的主键约束使用的名称。Specifies the name to be used for the primary key constraint that is automatically created on the FileTable. 如果未指定此值,则系统将为该约束生成一个名称。If this value is not specified, the system generates a name for the constraint.

FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name
适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

指定要对自动为 FileTable 中的 stream_id 列创建的唯一约束使用的名称 。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 is not specified, the system generates a name for the constraint.

FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name
适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

指定要对自动为 FileTable 中的 parent_path_locator 和 name 列创建的唯一约束使用的名称 。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 is not specified, the system generates a name for the constraint.

SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name .history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name .history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database).

如果数据类型、为 Null 性约束和主键约束需要都满足了,则可启动系统版本控制。Enables system versioning of the table if the datatype, nullability constraint, and primary key constraint requirements are met. 如果未使用 HISTORY_TABLE 参数,系统将在与当前表相同的文件组中生成一个符合当前表架构的新历史记录表,从而在两个表之间创建链接,使系统能在历史记录表中记录当前表中每行的历史记录。If the HISTORY_TABLE argument is not used, 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. 此历史记录表的名称为 MSSQL_TemporalHistoryFor<primary_table_object_id>The name of this history table will be MSSQL_TemporalHistoryFor<primary_table_object_id>. 默认情况下,历史记录表是经过 PAGE 压缩的。By default, the history table is PAGE compressed. 如果 HISTORY_TABLE 参数用于创建指向现有历史记录表的链接并使用此表,则会在当前表和指定表之间创建链接。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 is not replicated automatically from the current table to the history table. 如果历史记录表的名称在历史记录表创建期间指定,则必须指定架构和表的名称。If the name of a history table is specified during history table creation, you must specify the schema and table name. 创建现有历史记录表的链接时,可以选择执行数据一致性检查。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 do not overlap. 系统默认执行数据一致性检查。Performing the data consistency check is the default. 将此参数与 PERIOD FOR SYSTEM_TIMEGENERATED ALWAYS AS ROW { START | END } 参数结合使用来对表启用系统版本控制。Use this argument in conjunction with the PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW { START | END } arguments to enable system versioning on a table. 有关详细信息,请参阅 Temporal TablesFor more information, see Temporal Tables.

REMOTE_DATA_ARCHIVE = { ON [ ( table_stretch_options [,...n] ) ] | OFF ( MIGRATION_STATE = PAUSED ) } REMOTE_DATA_ARCHIVE = { ON [ ( table_stretch_options [,...n] ) ] | OFF ( MIGRATION_STATE = PAUSED ) }
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

创建已启用或禁用 Stretch Database 的新表。Creates the new table with Stretch Database enabled or disabled. 有关详细信息,请参阅 Stretch DatabaseFor more info, see Stretch Database.

为表启用 Stretch Database Enabling Stretch Database for a table

指定 ON 为表启用 Stretch 时,可选择指定 MIGRATION_STATE = OUTBOUND 立即开始迁移数据,也可指定 MIGRATION_STATE = PAUSED 推迟迁移数据。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. 默认值是 MIGRATION_STATE = OUTBOUNDThe default value is MIGRATION_STATE = OUTBOUND. 有关为表启用 Stretch 的详细信息,请参阅为表启用 Stretch DatabaseFor more info about enabling Stretch for a table, see Enable Stretch Database for a table.

先决条件Prerequisites. 为表启用 Stretch 之前,必须在服务器和数据库上启用 Stretch。Before you enable Stretch for a table, you have to enable Stretch on the server and on the database. 有关详细信息,请参阅 Enable Stretch Database for a databaseFor more info, see Enable Stretch Database for a database.

权限Permissions. 为数据库或表启用 Stretch 需要 db_owner 权限。Enabling Stretch for a database or a table requires db_owner permissions. 为表启用 Stretch 还需具有表的 ALTER 权限。Enabling Stretch for a table also requires ALTER permissions on the table.

[ FILTER_PREDICATE = { null | predicate } ] [ FILTER_PREDICATE = { null | predicate } ]
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

根据需要,指定一个筛选器谓词,从包含历史数据和最新数据的表中选择要迁移的行。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. 有关详细信息,请参阅为表启用 Stretch Database使用筛选器函数选择要迁移的行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 通过使用 CROSS APPLY 运算符将筛选器谓词应用到表。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.

指定筛选器谓词时,还须同时指定 MIGRATION_STATE 。When you specify a filter predicate, you also have to specify MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

  • 指定 OUTBOUND 以将数据从 SQL ServerSQL Server 迁移到 Azure SQL DatabaseAzure SQL DatabaseSpecify OUTBOUND to migrate data from SQL ServerSQL Server to Azure SQL DatabaseAzure SQL Database.

  • 指定 INBOUND 以将表的远程数据从 Azure SQL DatabaseAzure SQL Database 复制回 SQL ServerSQL Server,然后为此表禁用 Stretch Database。Specify INBOUND to copy the remote data for the table from Azure SQL DatabaseAzure SQL Database back to SQL ServerSQL Server and to disable Stretch for the table. 有关详细信息,请参阅 禁用 Stretch Database 并恢复远程数据For more info, see Disable Stretch Database and bring back remote data.

    此操作会产生数据传输成本,并且不能取消。This operation incurs data transfer costs, and it can't be canceled.

  • 指定 PAUSED 可暂停或推迟数据迁移。Specify PAUSED to pause or postpone data migration. 有关详细信息,请参阅暂停和恢复数据迁移 - Stretch DatabaseFor more info, see Pause and resume data migration -Stretch Database.

MEMORY_OPTIMIZEDMEMORY_OPTIMIZED
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database)。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database). Azure SQL DatabaseAzure SQL Database 托管实例不支持内存优化表。managed instance does not support memory optimized tables.

ON 值指示表是否为内存优化表。The value ON indicates that the table is memory optimized. 内存优化表是内存中 OLTP 功能的一部分,用于优化事务处理的性能。Memory-optimized tables are part of the In-Memory OLTP feature, which is used to optimized the performance of transaction processing. 若要开始使用内存中 OLTP,请参阅快速入门 1:可提高 Transact SQL 性能的内存中 OLTP 技术To get started with In-Memory OLTP see Quick Start 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance. 有关内存优化表的详细信息,请参阅内存优化表For more in-depth information about memory-optimized tables see Memory-Optimized Tables.

默认值 OFF 指示表是基于磁盘的表。The default value OFF indicates that the table is disk-based.

DURABILITYDURABILITY
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

SCHEMA_AND_DATA 的值指示表具有持久性,这意味着更改会持久保留在磁盘上,重新启动或故障转移后仍然存在。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 是默认值。SCHEMA_AND_DATA is the default value.

SCHEMA_ONLY 的值指示表是非持久表。The value of SCHEMA_ONLY indicates that the table is non-durable. 表架构具有持久化性,但是,数据库重新启动或故障转移之后,任何数据更改都不会保留。The table schema is persisted but any data updates are not persisted upon a restart or failover of the database. 仅允许将 DURABILITY = SCHEMA_ONLY 用于 MEMORY_OPTIMIZED = ONDURABILITY = SCHEMA_ONLY is only allowed with MEMORY_OPTIMIZED = ON.

警告

当使用 DURABILITY = SCHEMA_ONLY 创建表,随后使用 ALTER DATABASE 更改 READ_COMMITTED_SNAPSHOT 时,表中的数据将丢失 。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.

BUCKET_COUNTBUCKET_COUNT
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database)。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

指示应在哈希索引中创建的存储桶数。Indicates the number of buckets that should be created in the hash index. 哈希索引中 BUCKET_COUNT 的最大值为 1,073,741,824。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 是必需的参数。Bucket_count is a required argument.

INDEX 适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL Database)。INDEX Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database).

可将列索引和表索引指定为 CREATE TABLE 语句的一部分。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

HASHHASH
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指示创建哈希索引。Indicates that a HASH index is created.

只有内存优化表支持哈希索引。Hash indexes are supported only on memory-optimized tables.

RemarksRemarks

有关获批准的表、列、约束和索引数目的信息,请参阅 SQL Server 的最大容量规范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. ALTER DATABASESET MIXED_PAGE_ALLOCATION 选项设置为 TRUE 或设置为始终先于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 时,在创建表或索引后,将从混合盘区为此表或索引分配页面,直到其拥有足够的页面以形成统一盘区为止。When the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE is set to TRUE, or always prior to SQL Server 2016 (13.x)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. 若要获得关于由表分配和使用的空间量的报表,请执行 sp_spaceusedFor a report about the amount of space allocated and used by a table, execute sp_spaceused.

数据库引擎Database Engine在列定义中并不强制以特定的顺序指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列约束。The 数据库引擎Database Engine does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

创建表后,即使 QUOTED IDENTIFIER 选项在创建表时设置为 OFF,该选项在表的元数据中仍存储为 ON。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.

临时表Temporary Tables

可以创建本地临时表和全局临时表。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 cannot be partitioned.

本地临时表的名称前缀有一个数字符号 (#table_name),而全局临时表的名称前缀有两个数字符号 (##table_name) 。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-SQLTransact-SQL 语句通过使用 CREATE TABLE 语句中为 table_name 指定的值引用临时表,例如 :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.

创建或访问临时表时,如果包括 schema_name,它将被忽略 。If you include a schema_name when you create or access a temporary table, it is ignored. 所有临时表都在 dbo 架构中进行创建。All temporary tables are created in the dbo schema.

如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建,则数据库引擎Database Engine必须能够区分由不同用户创建的表。If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the 数据库引擎Database Engine must be able to distinguish the tables created by the different users. 为此,数据库引擎Database Engine在内部为每个本地临时表的表名追加一个数字后缀。The 数据库引擎Database Engine does this by internally appending a numeric suffix to each local temporary table name. 存储在 tempdb 的 sysobjects 表中的临时表,其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成 。The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. 为了可追加后缀,为本地临时表指定的 table_name 不能超过 116 个字符 。To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

除非使用 DROP TABLE 显式删除临时表,否则临时表将在退出其作用域时由系统自动删除:Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

  • 当存储过程完成时,将自动删除在存储过程中创建的本地临时表。A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 但调用创建此表的存储过程的进程无法引用此表。The table cannot be referenced by the process that called the stored procedure that created the table.
  • 所有其他本地临时表在当前会话结束时都将被自动删除。All other local temporary tables are dropped automatically at the end of the current session.
  • 全局临时表在创建此表的会话结束且其他所有任务停止对其引用时将被自动删除。Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. 任务与表之间的关联只在单个 Transact-SQLTransact-SQL 语句的生存周期内保持。The association between a task and a table is maintained only for the life of a single Transact-SQLTransact-SQL statement. 换言之,当创建全局临时表的会话结束时,最后一条引用此表的 Transact-SQLTransact-SQL 语句完成后,将自动删除此表。This means that a global temporary table is dropped at the completion of the last Transact-SQLTransact-SQL statement that was actively referencing the table when the creating session ended.

在存储过程或触发器中创建的本地临时表的名称可以与在调用存储过程或触发器之前创建的临时表名称相同。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 is not 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

当创建了本地或全局临时表后,CREATE TABLE 语法将支持除 FOREIGN KEY 约束以外的其他所有约束定义。When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions except for FOREIGN KEY constraints. 如果临时表中指定了 FOREIGN KEY 约束,则该语句将返回一条表明已跳过此约束的警告消息。If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message that states the constraint was skipped. 此表仍将创建,但不使用 FOREIGN KEY 约束。The table is still created without the FOREIGN KEY constraints. 在 FOREIGN KEY 约束中不能引用临时表。Temporary tables cannot 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 cannot be executed simultaneously by multiple users.

数据库作用域内全局临时表(Azure SQL 数据库)Database scoped global temporary tables (Azure SQL Database)

SQL ServerSQL Server 的全局临时表(表名以 ## 开头)存储在 tempdb 中,并跨整个 SQL ServerSQL Server 实例在所有用户会话之间共享。Global temporary tables for SQL ServerSQL Server (initiated with ## table name) are stored in tempdb and shared among all users' sessions across the whole SQL ServerSQL Server instance. 有关 SQL 表类型的信息,请参阅上述“创建表”章节。For information on SQL table types, see the above section on Create Tables.

Azure SQL DatabaseAzure SQL Database支持存储在 tempdb 中,且范围限定为数据库级别的全局临时表。supports global temporary tables that are also stored in tempdb and scoped to the database level. 也就是说,全局临时表对同一 Azure SQL DatabaseAzure SQL Database 中的所有用户会话进行共享。This means that global temporary tables are shared for all users' sessions within the same Azure SQL DatabaseAzure SQL Database. 其他数据库中的用户会话无法访问全局临时表。User sessions from other databases cannot access global temporary tables.

Azure SQL DatabaseAzure SQL Database的全局临时表遵循 SQL ServerSQL Server 对临时表使用的相同语法和语义。Global temporary tables for Azure SQL DatabaseAzure SQL Database follow the same syntax and semantics that SQL ServerSQL Server uses for temporary tables. 同样,全局临时存储过程也在 Azure SQL DatabaseAzure SQL Database中将范围限定为数据库级别。Similarly, global temporary stored procedures are also scoped to the database level in Azure SQL DatabaseAzure SQL Database. 局部临时表(表名以 # 开头)也受 Azure SQL DatabaseAzure SQL Database支持,并遵循 SQL ServerSQL Server 使用的相同语法和语义。Local temporary tables (initiated with # table name) are also supported for Azure SQL DatabaseAzure SQL Database and follow the same syntax and semantics that SQL ServerSQL Server uses. 请参阅上述临时表章节。See the above section on Temporary Tables.

重要

此功能适用于 Azure SQL DatabaseAzure SQL DatabaseThis feature is available for Azure SQL DatabaseAzure SQL Database.

排查 Azure SQL 数据库的全局临时表存在的问题Troubleshooting global temporary tables for Azure SQL Database

有关 tempdb 疑难解答,请参阅如何监视 tempdb 使用情况For the troubleshooting the tempdb, see How to Monitor tempdb use.

备注

只有服务器管理员才能访问 Azure SQL DatabaseAzure SQL Database中的疑难解答 DMV。Only a server admin can access the troubleshooting DMVs in Azure SQL DatabaseAzure SQL Database.

权限Permissions

任何用户都可以创建全局临时对象。Any user can create global temporary objects. 用户只能访问自己的对象,除非他们获得更多的权限。Users can only access their own objects, unless they receive additional permissions.

分区表Partitioned tables

使用 CREATE TABLE 创建已分区表前,必须首先创建分区函数以指定表分区的方式。Before creating a partitioned table by using CREATE TABLE, you must first create a partition function to specify how the table becomes partitioned. 分区函数是使用 CREATE PARTITION FUNCTION 创建的。A partition function is created by using CREATE PARTITION FUNCTION. 其次,必须创建分区架构,以指定将保存由分区函数指示的分区的文件组。Second, you must create a partition scheme to specify the filegroups that will hold the partitions indicated by the partition function. 分区方案是使用 CREATE PARTITION SCHEME 创建的。A partition scheme is created by using CREATE PARTITION SCHEME. 对于已分区表,不能指定用于分隔文件组的 PRIMARY KEY 或 UNIQUE 约束的位置。Placement of PRIMARY KEY or UNIQUE constraints to separate filegroups cannot be specified for partitioned tables. 有关详细信息,请参阅 Partitioned Tables and IndexesFor more information, see Partitioned Tables and Indexes.

PRIMARY KEY 约束PRIMARY KEY Constraints

  • 一个表只能包含一个 PRIMARY KEY 约束。A table can contain only one PRIMARY KEY constraint.

  • 由 PRIMARY KEY 约束生成的索引不会使表中的非聚集索引超过 999 个,聚集索引超过 1 个。The index generated by a PRIMARY KEY constraint cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.

  • 如果没有为 PRIMARY KEY 约束指定 CLUSTERED 或 NONCLUSTERED,并且没有为 UNIQUE 约束指定聚集索引,则将对该 PRIMARY KEY 约束使用 CLUSTERED。If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.

  • 在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL。All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. 如果没有指定为 Null 性,则加入 PRIMARY KEY 约束的所有列的为 Null 性都将设置为 NOT NULL。If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

    备注

    内存优化表可具有可为空的键列。For memory-optimized tables, the NULLable key column is allowed.

  • 如果在 CLR 用户定义类型的列中定义主键,则该类型的实现必须支持二进制排序。If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. 有关详细信息,请参阅 CLR 用户定义类型For more information, see CLR User-Defined Types.

UNIQUE 约束UNIQUE Constraints

  • 如果没有为 UNIQUE 约束指定 CLUSTERED 或 NONCLUSTERED,则默认使用 NONCLUSTERED。If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
  • 每个 UNIQUE 约束都生成一个索引。Each UNIQUE constraint generates an index. UNIQUE 约束的数目不会使表中的非聚集索引超过 999 个,聚集索引超过 1 个。The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.
  • 如果在 CLR 用户定义类型的列中定义唯一约束,则该类型的实现必须支持二进制或基于运算符的排序。If a unique constraint is defined on a CLR user-defined type column, the implementation of the type must support binary or operator-based ordering. 有关详细信息,请参阅 CLR 用户定义类型For more information, see CLR User-Defined Types.

Foreign Key 约束FOREIGN KEY Constraints

  • 如果在 FOREIGN KEY 约束的列中输入非 NULL 值,则此值必须在被引用列中存在;否则,将返回违反外键约束的错误信息。When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

  • 如果未指定源列,则 FOREIGN KEY 约束适用于前面所讲的列。FOREIGN KEY constraints are applied to the preceding column, unless source columns are specified.

  • FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。FOREIGN KEY constraints can reference only tables within the same database on the same server. 跨数据库的引用完整性必须通过触发器实现。Cross-database referential integrity must be implemented through triggers. 有关详细信息,请参阅 CREATE TRIGGERFor more information, see CREATE TRIGGER.

  • FOREIGN KEY 约束可引用同一表中的其他列。FOREIGN KEY constraints can reference another column in the same table. 此行为称为自引用。This is referred to as a self-reference.

  • 列级 FOREIGN KEY 约束的 REFERENCES 子句只能列出一个引用列。The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column. 此列的数据类型必须与定义约束的列的数据类型相同。This column must have the same data type as the column on which the constraint is defined.

  • 表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. 每个引用列的数据类型也必须与列表中相应列的数据类型相同。The data type of each reference column must also be the same as the corresponding column in the column list.

  • 如果类型为 timestamp 的列是外键或被引用键的一部分,则不能指定 CASCADE、SET NULL 或 SET DEFAULT 。CASCADE, SET NULL or SET DEFAULT cannot be specified if a column of type timestamp is part of either the foreign key or the referenced key.

  • 可将 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION 在相互存在引用关系的表上进行组合。CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. 如果 数据库引擎Database Engine 遇到 NO ACTION,它将停止并回滚相关的 CASCADE、SET NULL 和 SET DEFAULT 操作。If the 数据库引擎Database Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. 如果 DELETE 语句导致 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION 操作的组合,则在 数据库引擎Database Engine 检查所有 NO ACTION 前,将应用所有 CASCADE、SET NULL 和 SET DEFAULT 操作。When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the 数据库引擎Database Engine checks for any NO ACTION.

  • 对于表可包含的引用其他表的 FOREIGN KEY 约束的数目或其他表所拥有的引用特定表的 FOREIGN KEY 约束的数目, 数据库引擎Database Engine 都没有预定义的限制。The 数据库引擎Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table.

    尽管如此,可使用的 FOREIGN KEY 约束的实际数目还是受硬件配置以及数据库和应用程序设计的限制。Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. 建议表中包含的 FOREIGN KEY 约束不要超过 253 个,并且引用该表的 FOREIGN KEY 约束也不要超过 253 个。We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. 有效的限制还是或多或少取决于应用程序和硬件。The effective limit for you may be more or less depending on the application and hardware. 在设计数据库和应用程序时应考虑强制 FOREIGN KEY 约束的开销。Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.

  • 对于临时表不强制 FOREIGN KEY 约束。FOREIGN KEY constraints are not enforced on temporary tables.

  • FOREIGN KEY 约束只能引用所引用的表的 PRIMARY KEY 或 UNIQUE 约束中的列或所引用的表上 UNIQUE INDEX 中的列。FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.

  • 如果在 CLR 用户定义类型的列上定义外键,则该类型的实现必须支持二进制排序。If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. 有关详细信息,请参阅 CLR 用户定义类型For more information, see CLR User-Defined Types.

  • 参与构造外键关系的列必须定义为具有同一长度和小数位数。Columns participating in a foreign key relationship must be defined with the same length and scale.

DEFAULT 定义DEFAULT definitions

  • 每列只能有一个 DEFAULT 定义。A column can have only one DEFAULT definition.

  • DEFAULT 定义可以包含常量值、函数、SQL 标准 niladic 函数或 NULL。A DEFAULT definition can contain constant values, functions, SQL standard niladic functions, or NULL. 下表显示 niladic 函数及其在执行 INSERT 语句时返回的默认值。The following table shows the niladic functions and the values they return for the default during an INSERT statement.

    SQL-92 niladic 函数SQL-92 niladic function 返回的值Value returned
    CURRENT_TIMESTAMPCURRENT_TIMESTAMP 当前日期和时间。Current date and time.
    CURRENT_USERCURRENT_USER 执行插入的用户的名称。Name of user performing an insert.
    SESSION_USERSESSION_USER 执行插入的用户的名称。Name of user performing an insert.
    SYSTEM_USERSYSTEM_USER 执行插入的用户的名称。Name of user performing an insert.
    UserUSER 执行插入的用户的名称。Name of user performing an insert.
  • DEFAULT 定义中的 constant_expression 不能引用表中的其他列,也不能引用其他表、视图或存储过程 。constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures.

  • 不能对数据类型为 timestamp 的列或具有 IDENTITY 属性的列创建 DEFAULT 定义 。DEFAULT definitions cannot be created on columns with a timestamp data type or columns with an IDENTITY property.

  • 如果别名数据类型绑定到默认对象,则不能对该别名数据类型的列创建 DEFAULT 定义。DEFAULT definitions cannot be created for columns with alias data types if the alias data type is bound to a default object.

CHECK 约束CHECK Constraints

  • 列可以有任意多个 CHECK 约束,并且约束条件中可以包含用 AND 和 OR 组合起来的多个逻辑表达式。A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. 列上的多个 CHECK 约束按创建顺序进行验证。Multiple CHECK constraints for a column are validated in the order they are created.

  • 搜索条件必须取值为布尔表达式,并且不能引用其他表。The search condition must evaluate to a Boolean expression and cannot reference another table.

  • 列级 CHECK 约束只能引用被约束的列,表级 CHECK 约束只能引用同一表中的列。A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

    当执行 INSERT 和 UPDATE 语句时,CHECK CONSTRAINTS 和规则具有相同的数据验证功能。CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and UPDATE statements.

  • 当列上存在规则和一个或多个 CHECK 约束时,将验证所有限制。When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.

  • 不能在 text、ntext 或 image 列上定义 CHECK 约束 。CHECK constraints cannot be defined on text, ntext, or image columns.

其他约束信息Additional Constraint information

  • 无法使用 DROP INDEX 删除为约束创建的索引;必须使用 ALTER TABLE 来删除约束。An index created for a constraint cannot be dropped by using DROP INDEX; the constraint must be dropped by using ALTER TABLE. 可以使用 ALTER INDEX ... REBUILD 重新生成已创建的约束用索引。An index created for and used by a constraint can be rebuilt by using ALTER INDEX ... REBUILD. 有关详细信息,请参阅 重新组织和重新生成索引For more information, see Reorganize and Rebuild Indexes.
  • 除了不能以数字符号 (#) 开头以外,约束名称还必须符合标识符规则。Constraint names must follow the rules for identifiers, except that the name cannot start with a number sign (#). 如果未提供 constraint_name,则会将系统生成的名称分配给约束 。If constraint_name is not supplied, a system-generated name is assigned to the constraint. 约束名将出现在所有与违反约束有关的错误信息中。The constraint name appears in any error message about constraint violations.
  • INSERTUPDATEDELETE 语句中违反了约束时,将终止执行该语句。When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is ended. 但是,当 SET XACT_ABORT 设置为 OFF 时,如果该语句是显式事务的一部分,则继续处理此语句。However, when SET XACT_ABORT is set to OFF, the transaction, if the statement is part of an explicit transaction, continues to be processed. SET XACT_ABORT 设置为 ON 时,将回滚整个事务。When SET XACT_ABORT is set to ON, the whole transaction is rolled back. 还可以通过检查 @@ERROR 系统函数将 ROLLBACK TRANSACTION 语句与事务定义一起使用。You can also use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@ERROR system function.
  • 如果 ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCK = ON,可以在访问索引时使用行级别、页级别和表级别锁定。When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when you access the index. 数据库引擎Database Engine将选择相应的锁,并且可以将锁从行锁或页锁升级到表锁。The 数据库引擎Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock. 如果 ALLOW_ROW_LOCKS = OFF 并且 ALLOW_PAGE_LOCK = OFF,则当访问索引时将仅允许表级别的锁。When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when you access the index.
  • 如果某个表具有 FOREIGN KEY 或 CHECK CONSTRAINTS 及触发器,则将在触发器执行前先检查约束条件。If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.

若要获得关于表以及其列的报告,请使用 sp_helpsp_helpconstraintFor a report on a table and its columns, use sp_help or sp_helpconstraint. 若要重命名表,请使用 sp_renameTo rename a table, use sp_rename. 若要获得依赖于表的视图和存储过程的报表,请使用 sys.dm_sql_referenced_entitiessys.dm_sql_referencing_entitiesFor a report on the views and stored procedures that depend on a table, use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

表定义中的为 Null 性规则Nullability rules within a table definition

列的为 Null 性决定该列中是否允许以空值 (NULL) 作为其数据。The nullability of a column determines whether that column can allow a null value (NULL) as the data in that column. NULL 不为零或空白:NULL 表示没有生成任何项或没有提供显式 NULL,它通常暗指该值未知或不可用。NULL is not zero or blank: NULL means no entry was made or an explicit NULL was supplied, and it typically implies that the value is either unknown or not applicable.

使用 CREATE TABLEALTER TABLE 来创建或更改表时,数据库和会话设置会影响并且可能会替代列定义中所用的数据类型的为 Null 性。When you use CREATE TABLE or ALTER TABLE to create or alter a table, database and session settings influence and possibly override the nullability of the data type that is used in a column definition. 建议您始终将列显式定义为非计算列的 NULL 或 NOT NULL,或者,如果使用用户定义的数据类型,则建议您允许该列使用此数据类型的默认为空性。We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type. 稀疏列必须始终允许 NULL。Sparse columns must always allow NULL.

如果未显式指定列的为 Null 性,则遵循下表显示的规则。When column nullability is not explicitly specified, column nullability follows the rules shown in the following table.

列数据类型Column data type 规则Rule
别名数据类型Alias data type 数据库引擎Database Engine使用创建数据类型时指定的为 Null 性。The 数据库引擎Database Engine uses the nullability that is specified when the data type was created. 若要确定数据类型的默认为 Null 性,请使用 sp_help 。To determine the default nullability of the data type, use sp_help.
CLR 用户定义类型 (CLR user-defined type)CLR user-defined type 根据列定义确定为 Null 性。Nullability is determined according to the column definition.
系统提供的数据类型System-supplied data type 如果系统提供的数据类型只有一个选项,则优先使用该选项。If the system-supplied data type has only one option, it takes precedence. timestamp 数据类型必须为 NOT NULL 。timestamp data types must be NOT NULL. 当任何会话设置通过 SET 设置为 ON 时:When any session settings are set ON by using SET:
如果 ANSI_NULL_DFLT_ON = ON,则分配 NULL 。ANSI_NULL_DFLT_ON = ON, NULL is assigned.
如果 ANSI_NULL_DFLT_OFF = ON,则分配 NOT NULL 。ANSI_NULL_DFLT_OFF = ON, NOT NULL is assigned.

当任何数据库设置通过 ALTER DATABASE 进行配置时:When any database settings are configured by using ALTER DATABASE:
如果 ANSI_NULL_DEFAULT_ON = ON,则分配 NULL 。ANSI_NULL_DEFAULT_ON = ON, NULL is assigned.
如果 ANSI_NULL_DEFAULT_OFF = ON,则分配 NOT NULL 。ANSI_NULL_DEFAULT_OFF = ON, NOT NULL is assigned.

若要查看 ANSI_NULL_DEFAULT 的数据库设置,请使用 sys.databases 目录视图 To view the database setting for ANSI_NULL_DEFAULT, use the sys.databases catalog view

如果没有为会话设置任何 ANSI_NULL_DFLT 选项,并且将数据库设置为默认值(ANSI_NULL_DEFAULT 为 OFF),则会分配默认值 NOT NULL。When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULT is OFF), the default of NOT NULL is assigned.

如果该列是计算列,则其为 Null 性总是由数据库引擎Database Engine自动确定。If the column is a computed column, its nullability is always automatically determined by the 数据库引擎Database Engine. 若要查找此类型列的为 Null 性,请使用带 AllowsNull 属性的 COLUMNPROPERTY 函数 。To find out the nullability of this type of column, use the COLUMNPROPERTY function with the AllowsNull property.

备注

SQL Server ODBC 驱动程序和 SQL Server OLE DB 驱动程序都默认将 ANSI_NULL_DFLT_ON 设置为 ON。The SQL Server ODBC driver and SQL Server OLE DB driver both default to having ANSI_NULL_DFLT_ON set to ON. ODBC 和 OLE DB 用户可以在 ODBC 数据源中配置该设置,或通过应用程序设置的连接特性或属性配置该设置。ODBC and OLE DB users can configure this in ODBC data sources, or with connection attributes or properties set by the application.

Data CompressionData Compression

不能为系统表启用压缩功能。System tables cannot be enabled for compression. 在创建表时,除非另外指定,否则,将数据压缩设置为 NONE。When you are creating a table, data compression is set to NONE, unless specified otherwise. 如果指定的分区列表或分区超出范围,将生成错误。If you specify a list of partitions or a partition that is out of range, an error will be generated. 有关数据压缩的详细信息,请参阅 数据压缩For a more information about data compression, see Data Compression.

若要评估更改压缩状态将对表、索引或分区有何影响,请使用 sp_estimate_data_compression_savings 存储过程。To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

权限Permissions

需要在数据库中具有 CREATE TABLE 权限,以及对创建表所在的架构具有 ALTER 权限。Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

如果 CREATE TABLE 语句中的任何列被定义为用户定义类型,则需要对用户定义类型具有 REFERENCES 权限。If any columns in the CREATE TABLE statement are defined to be of a user-defined type, REFERENCES permission on the user-defined type is required.

如果 CREATE TABLE 语句中的任何列被定义为 CLR 用户定义类型,则需要具有对此类型的所有权或 REFERENCES 权限。If any columns in the CREATE TABLE statement are defined to be of a CLR user-defined type, either ownership of the type or REFERENCES permission on it is required.

如果 CREATE TABLE 语句中的任何列具有与其关联的 XML 架构集合,则需要具有对 XML 架构集合的所有权或 REFERENCES 权限。If any columns in the CREATE TABLE statement have an XML schema collection associated with them, either ownership of the XML schema collection or REFERENCES permission on it is required.

任何用户都可以在 tempdb 中创建临时表。Any user can create temporary tables in tempdb.

示例Examples

A.A. 对列创建 PRIMARY KEY 约束Create a PRIMARY KEY constraint on a column

以下示例显示对 EmployeeID 表的 Employee 列具有聚集索引的 PRIMARY KEY 约束的列定义。The following example shows the column definition for a PRIMARY KEY constraint with a clustered index on the EmployeeID column of the Employee table. 因为未指定约束名称,所以系统提供了约束名称。Because a constraint name is not specified, the system supplies the constraint name.

CREATE TABLE dbo.Employee (EmployeeID int
PRIMARY KEY CLUSTERED);

B.B. 使用 FOREIGN KEY 约束Using FOREIGN KEY constraints

FOREIGN KEY 约束用于引用其他表。A FOREIGN KEY constraint is used to reference another table. FOREIGN KEY 可以是单列键或多列键。Foreign keys can be single-column keys or multicolumn keys. 以下示例显示 SalesOrderHeader 表上引用 SalesPerson 表的单列 FOREIGN KEY 约束。This following example shows a single-column FOREIGN KEY constraint on the SalesOrderHeader table that references the SalesPerson table. 对于单列 FOREIGN KEY 约束,只需要 REFERENCES 子句。Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

SalesPersonID int NULL
REFERENCES SalesPerson(SalesPersonID)

也可以显式使用 FOREIGN KEY 子句并复述列特性。You can also explicitly use the FOREIGN KEY clause and restate the column attribute. 请注意,在这两个表中列名不必相同。Note that the column name does not have to be the same in both tables.

FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

多列键约束作为表约束创建。Multicolumn key constraints are created as table constraints. AdventureWorks2012AdventureWorks2012 数据库中,SpecialOfferProduct 表包含多列 PRIMARY KEY。In the AdventureWorks2012AdventureWorks2012 database, the SpecialOfferProduct table includes a multicolumn PRIMARY KEY. 以下示例显示如何从其他表中引用此键(可选择显式约束名)。The following example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
 (ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)

C.C. 使用 UNIQUE 约束Using UNIQUE constraints

UNIQUE 约束用于强制非主键列的唯一性。UNIQUE constraints are used to enforce uniqueness on nonprimary key columns. 以下示例强制的限制是,Name 表的 Product 列必须唯一。The following example enforces a restriction that the Name column of the Product table must be unique.

Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

D.D. 使用 DEFAULT 定义Using DEFAULT definitions

使用 INSERT 和 UPDATE 语句时,如果没有提供值,则默认值会提供值。Defaults supply a value (with the INSERT and UPDATE statements) when no value is supplied. 例如,AdventureWorks2012AdventureWorks2012 数据库可包括一个查找表,此表列出该公司的员工可以填充的不同工作。For example, the AdventureWorks2012AdventureWorks2012 database could include a lookup table listing the different jobs employees can fill in the company. 在描述每个工作的列的下面,如果没有显式输入实际的描述,则字符串默认值可提供一个描述。Under a column that describes each job, a character string default could supply a description when an actual description is not entered explicitly.

DEFAULT 'New Position - title not formalized yet'

除了常量以外,DEFAULT 定义还可以包含函数。In addition to constants, DEFAULT definitions can include functions. 使用以下示例获取输入项的当前日期。Use the following example to get the current date for an entry.

DEFAULT (getdate())

niladic 函数扫描也可改善数据完整性。A niladic-function scan can also improve data integrity. 若要跟踪插入行的用户,请使用 USER 的 niladic 函数。To keep track of the user that inserted a row, use the niladic-function for USER. 不要用括号将 niladic 函数括起来。Do not enclose the niladic-functions with parentheses.

DEFAULT USER

E.E. 使用 CHECK 约束Using CHECK constraints

以下示例显示对于在 CreditRating 表的 Vendor 列中输入的值所做的限制。The following example shows a restriction made to values that are entered into the CreditRating column of the Vendor table. 此约束未命名。The constraint is unnamed.

CHECK (CreditRating >= 1 and CreditRating <= 5)

此示例显示一个命名约束,它对于在表的列中输入的字符数据有模式限制。This example shows a named constraint with a pattern restriction on the character data entered into a column of a table.

CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

此示例指定这些值必须在特定的列表中或遵循指定的模式。This example specifies that the values must be within a specific list or follow a specified pattern.

CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')

F.F. 显示完整的表定义Showing the complete table definition

以下示例显示在 AdventureWorks2012AdventureWorks2012 数据库中创建的 PurchaseOrderDetail 表的完整表定义,其中包含所有约束定义。The following example shows the complete table definitions with all constraint definitions for table PurchaseOrderDetail created in the AdventureWorks2012AdventureWorks2012 database. 请注意,若要运行此示例,表架构应改为 dboNote that to run the sample, the table schema is changed to dbo.

CREATE TABLE dbo.PurchaseOrderDetail
(
    PurchaseOrderID int NOT NULL
        REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
    LineNumber smallint NOT NULL,
    ProductID int NULL
        REFERENCES Production.Product(ProductID),
    UnitPrice money NULL,
    OrderQty smallint NULL,
    ReceivedQty float NULL,
    RejectedQty float NULL,
    DueDate datetime NULL,
    rowguid uniqueidentifier ROWGUIDCOL NOT NULL
        CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid()),
    ModifiedDate datetime NOT NULL
        CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (getdate()),
    LineTotal AS ((UnitPrice*OrderQty)),
    StockedQty AS ((ReceivedQty-RejectedQty)),
    CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber
               PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber)
               WITH (IGNORE_DUP_KEY = OFF)
)
ON PRIMARY;

G.G. 创建其 xml 列键入 XML 架构集合的表Creating a table with an xml column typed to an XML schema collection

以下示例创建一个表,其 xml 列将键入 XML 架构集合 HRResumeSchemaCollectionThe following example creates a table with an xml column that is typed to XML schema collection HRResumeSchemaCollection. DOCUMENT 关键字指定 column_name 中 xml 数据类型的每个实例只能包含一个顶级元素 。The DOCUMENT keyword specifies that each instance of the xml data type in column_name can contain only one top-level element.

CREATE TABLE HumanResources.EmployeeResumes
   (LName nvarchar(25), FName nvarchar(25),
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

H.H. 创建已分区表Creating a partitioned table

以下示例创建一个分区函数,将表或索引分为四个分区。The following example creates a partition function to partition a table or index into four partitions. 然后,此示例创建用于指定保存四个分区的文件组的分区架构。Then, the example creates a partition scheme that specifies the filegroups in which to hold each of the four partitions. 最后,此示例创建使用此分区架构的表。Finally, the example creates a table that uses the partition scheme. 此示例假定数据库中已经存在文件组。This example assumes the filegroups already exist in the database.

CREATE PARTITION FUNCTION myRangePF1 (int)
    AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    TO (test1fg, test2fg, test3fg, test4fg) ;
GO  
  
CREATE TABLE PartitionTable (col1 int, col2 char(10))
    ON myRangePS1 (col1) ;
GO

根据 col1PartitionTable 列的值,将分区按照下列方式分配。Based on the values of column col1 of PartitionTable, the partitions are assigned in the following ways.

文件组Filegroup test1fgtest1fg test2fgtest2fg test3fgtest3fg test4fgtest4fg
分区 Partition 11 22 33 44
Values col 1 <= 1col 1 <= 1 col1 > 1 AND col1 <= 100col1 > 1 AND col1 <= 100 col1 > 100 AND col1 <= 1,000col1 > 100 AND col1 <= 1,000 col1 > 1000col1 > 1000

I.I. 在列中使用 uniqueidentifier 数据类型Using the uniqueidentifier data type in a column

下面的示例创建一个包含 uniqueidentifier 列的表。The following example creates a table with a uniqueidentifier column. 该示例使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值,并在 NEWSEQUENTIALID() 约束中使用 DEFAULT 函数为新行提供值。The example uses a PRIMARY KEY constraint to protect the table against users inserting duplicated values, and it uses the NEWSEQUENTIALID() function in the DEFAULT constraint to provide values for new rows. 将 ROWGUIDCOL 属性应用到 uniqueidentifier 列,以便可以使用 $ROWGUID 关键字对其进行引用。The ROWGUIDCOL property is applied to the uniqueidentifier column so that it can be referenced using the $ROWGUID keyword.

CREATE TABLE dbo.Globally_Unique_Data
    (guid uniqueidentifier
        CONSTRAINT Guid_Default DEFAULT
        NEWSEQUENTIALID() ROWGUIDCOL,
    Employee_Name varchar(60)
    CONSTRAINT Guid_PK PRIMARY KEY (guid) );

J.J. 对计算列使用表达式Using an expression for a computed column

以下示例显示如何使用用于计算 (low + high)/2 计算列的表达式 (myavg)。The following example shows the use of an expression ((low + high)/2) for calculating the myavg computed column.

CREATE TABLE dbo.mytable
    ( low int, high int, myavg AS (low + high)/2 ) ;

K.K. 基于用户定义类型列创建计算列Creating a computed column based on a user-defined type column

以下示例将创建一个表,其中一列定义为用户定义类型 utf8string,并假设此类型的程序集和类型本身已在当前数据库中创建。The following example creates a table with one column defined as user-defined type utf8string, assuming that the type's assembly, and the type itself, have already been created in the current database. 另一列是基于 utf8string 定义的,使用 type(class)utf8stringToString() 方法计算列值 。A second column is defined based on utf8string, and uses method ToString() of type(class)utf8string to compute a value for the column.

CREATE TABLE UDTypeTable
    ( u utf8string, ustr AS u.ToString() PERSISTED ) ;

L.L. 对计算列使用 USER_NAME 函数Using the USER_NAME function for a computed column

以下示例在 USER_NAME() 列中使用 myuser_name 函数。The following example uses the USER_NAME() function in the myuser_name column.

CREATE TABLE dbo.mylogintable
    ( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;

M.M. 创建具有 FILESTREAM 列的表Creating a table that has a FILESTREAM column

下面的示例创建一个包含 FILESTREAMPhoto 的表。The following example creates a table that has a FILESTREAM column Photo. 如果某个表包含一个或多个 FILESTREAM 列,该表必须包含一个 ROWGUIDCOL 列。If a table has one or more FILESTREAM columns, the table must have one ROWGUIDCOL column.

CREATE TABLE dbo.EmployeePhoto
    (
     EmployeeId int NOT NULL PRIMARY KEY
    ,Photo varbinary(max) FILESTREAM NULL
    ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
        UNIQUE DEFAULT NEWID()
    );

N.N. 创建使用行压缩的表Creating a table that uses row compression

下面的示例创建一个使用行压缩的表。The following example creates a table that uses row compression.

CREATE TABLE dbo.T1
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);

有关其他数据压缩示例,请参阅数据压缩For additional data compression examples, see Data Compression.

O.O. 创建具有稀疏列和列集的表Creating a table that has sparse columns and a column set

下面的示例说明了如何创建具有稀疏列的表,以及具有两个稀疏列和一个列集的表。The following examples show to how to create a table that has a sparse column, and a table that has two sparse columns and a column set. 这些示例使用基本语法。The examples use the basic syntax. 有关更复杂的示例,请参阅使用稀疏列使用列集For more complex examples, see Use Sparse Columns and Use Column Sets.

此示例创建一个包含稀疏列的表。This example creates a table that has a sparse column.

CREATE TABLE dbo.T1
    (c1 int PRIMARY KEY,
    c2 varchar(50) SPARSE NULL ) ;

此示例创建一个表,该表具有两个稀疏列和一个名 CSet 的列集。This example creates a table that has two sparse columns and a column set named CSet.

CREATE TABLE T1
    (c1 int PRIMARY KEY,
    c2 varchar(50) SPARSE NULL,
    c3 int SPARSE NULL,
    CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;

P.P. 创建由系统版本控制的、基于磁盘的临时表Creating a system-versioned disk-based temporal table

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

下列示例显示如何创建链接到新历史记录表的临时表,以及如何创建链接到现有历史记录表的临时表。The following examples show how to create a temporal table linked to a new history table, and how to create a temporal table linked to an existing history table. 请注意,临时表须有主键,定义为为表启用和为系统版本控制启用。Note that the temporal table must have a primary key defined to be enabled for the table to be enabled for system versioning. 有关显示如何在现有表中添加或删除系统版本控制的示例,请参阅示例中的系统版本控制。For examples showing how to add or remove system versioning on an existing table, see System Versioning in Examples. 有关使用情况,请参阅临时表For use cases, see Temporal Tables.

此例表示创建链接到新历史记录表的新临时表。This example creates a new temporal table linked to a new history table.

CREATE TABLE Department
(
    DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,
    DepartmentName varchar(50) NOT NULL,
    ManagerID int NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

此例表示创建链接到现有历史记录表的新临时表。This example creates a new temporal table linked to an existing history table.

--Existing table
CREATE TABLE Department_History
(
    DepartmentNumber char(10) NOT NULL,
    DepartmentName varchar(50) NOT NULL,
    ManagerID int NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 NOT NULL,
    SysEndTime datetime2 NOT NULL
);
--Temporal table
CREATE TABLE Department
(
    DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,
    DepartmentName varchar(50) NOT NULL,
    ManagerID INT NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
    (SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = dbo.Department_History, DATA_CONSISTENCY_CHECK = ON )
    );

Q.Q. 创建系统版本控制的内存优化临时表Creating a system-versioned memory-optimized temporal table

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

下列示例显示如何创建链接到基于磁盘的新历史记录表的新系统版本控制的内存优化临时表。The following example shows how to create a system-versioned memory-optimized temporal table linked to a new disk-based history table.

此例表示创建链接到新历史记录表的新临时表。This example creates a new temporal table linked to a new history table.

CREATE SCHEMA History
GO
CREATE TABLE dbo.Department
(
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,
    DepartmentName varchar(50) NOT NULL,
    ManagerID int NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
    (
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
            SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )
    );

此例表示创建链接到现有历史记录表的新临时表。This example creates a new temporal table linked to an existing history table.

--Existing table
CREATE TABLE Department_History
(
    DepartmentNumber char(10) NOT NULL,
    DepartmentName varchar(50) NOT NULL,
    ManagerID int NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 NOT NULL,
    SysEndTime datetime2 NOT NULL
);
--Temporal table
CREATE TABLE Department
(
    DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,
    DepartmentName varchar(50) NOT NULL,
    ManagerID INT NULL,
    ParentDepartmentNumber char(10) NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
    (SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = dbo.Department_History, DATA_CONSISTENCY_CHECK = ON )
    );

R.R. 创建具有加密列的表Creating a table with encrypted columns

下列示例表示创建包含两个加密列的表。The following example creates a table with two encrypted columns. 有关详细信息,请参阅 Always EncryptedFor more information, see Always Encrypted.

CREATE TABLE Customers (
    CustName nvarchar(60)
        ENCRYPTED WITH
            (
             COLUMN_ENCRYPTION_KEY = MyCEK,
             ENCRYPTION_TYPE = RANDOMIZED,
             ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
            ),
    SSN varchar(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH
            (
             COLUMN_ENCRYPTION_KEY = MyCEK,
             ENCRYPTION_TYPE = DETERMINISTIC ,
             ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
            ),
    Age int NULL
);

S.S. 创建内联筛选索引Create an inline filtered index

创建内联已筛选索引的表。Creates a table with an inline filtered index.

CREATE TABLE t1
(
    c1 int,
    index IX1 (c1) WHERE c1 > 0
);

T.T. 创建内联索引Create an inline index

下面演示如何为基于磁盘的表使用 NONCLUSTERED 内联:The following shows how to use NONCLUSTERED inline for disk-based tables:

CREATE TABLE t1 
(
    c1 int, 
    INDEX ix_1 NONCLUSTERED (c1)
);

CREATE TABLE t2 
(
    c1 int, 
    c2 int INDEX ix_1 NONCLUSTERED
);

CREATE TABLE t3 
(
    c1 int, 
    c2 int, 
    INDEX ix_1 NONCLUSTERED (c1,c2)
);

U.U. 创建包含匿名复合主键的临时表Create a temporary table with an anonymously named compound primary key

创建包含匿名复合主键的临时表。Creates a table with an anonymously named compound primary key. 这有助于避免发生以下运行时冲突:两个会话范围内临时表(分别位于单独的会话中)对约束的命名相同。This is useful to avoid run-time conflicts where two session-scoped temp tables, each in a separate session, use the same name for a constraint.

CREATE TABLE #tmp
(
    c1 int,
    c2 int,
    PRIMARY KEY CLUSTERED ([c1], [c2])
);
GO

如果显式命名约束,另一个会话就会生成如下错误:If you explicitly name the constraint, the second session will generate an error such as:

Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_#tmp' in the database.
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.

之所以会出现问题是因为,虽然临时表名已唯一化,但约束名并未唯一化。The problem arises from the fact that while the temp table name is uniquified, the constraint names are not.

V.V. 使用 Azure SQL 数据库中的全局临时表Using global temporary tables in Azure SQL Database

会话 A 在 Azure SQL DatabaseAzure SQL Database testdb1 中创建全局临时表 ##test,并添加 1 行Session A creates a global temp table ##test in Azure SQL DatabaseAzure SQL Database testdb1 and adds 1 row

CREATE TABLE ##test (
    a int, 
    b int
);
INSERT INTO ##test 
VALUES (1,1);

--Obtain object ID for temp table ##test
SELECT OBJECT_ID('tempdb.dbo.##test') AS 'Object ID';

下面是结果集:Here is the result set.

1253579504

获取 tempdb (2) 中给定对象 ID 1253579504 的全局临时表名称Obtain global temp table name for a given object ID 1253579504 in tempdb (2)

SELECT name FROM tempdb.sys.objects WHERE object_id = 1253579504

下面是结果集:Here is the result set.

##test

会话 B 连接到 Azure SQL DatabaseAzure SQL Database testdb1,并能访问会话 A 创建的 ##test 表Session B connects to Azure SQL DatabaseAzure SQL Database testdb1 and can access table ##test created by session A

SELECT * FROM ##test

下面是结果集:Here is the result set.

1,1

会话 C 连接到 Azure SQL DatabaseAzure SQL Database testdb2 中的另一个数据库,并希望访问在 testdb1 中创建的 ##test 表。Session C connects to another database in Azure SQL DatabaseAzure SQL Database testdb2 and wants to access ##test created in testdb1. 此选择因全局临时表的数据库作用域而失败This select fails due to the database scope for the global temp tables

SELECT * FROM ##test

这将生成以下错误:Which generates the following error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '##test'

从当前用户数据库 testdb1 寻址 Azure SQL DatabaseAzure SQL Database tempdb 中的系统对象Addressing system object in Azure SQL DatabaseAzure SQL Database tempdb from current user database testdb1

SELECT * FROM tempdb.sys.objects
SELECT * FROM tempdb.sys.columns
SELECT * FROM tempdb.sys.database_files

另请参阅See Also

ALTER TABLE ALTER TABLE
COLUMNPROPERTY COLUMNPROPERTY
CREATE INDEX CREATE INDEX
CREATE VIEW CREATE VIEW
数据类型 Data Types
DROP INDEX DROP INDEX
sys.dm_sql_referenced_entities sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities sys.dm_sql_referencing_entities
DROP TABLE DROP TABLE
CREATE PARTITION FUNCTION CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME CREATE PARTITION SCHEME
CREATE TYPE CREATE TYPE
EVENTDATA EVENTDATA
sp_help sp_help
sp_helpconstraint sp_helpconstraint
sp_rename sp_rename
sp_spaceusedsp_spaceused