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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

[SQL Server]SQL ServerAzure SQL DatabaseAzure SQL Database 中建立新的資料表。Creates a new table in [SQL Server]SQL 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 ] . | schema_name . ] table_name   
    ( { <column_definition> } [ ,...n ] )   
[ ; ]  

完整語法Full Syntax

--Disk-Based CREATE TABLE Syntax  
CREATE TABLE   
    [ database_name . [ schema_name ] . | schema_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}   
  | 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 
LE Syntax  
CREATE TABLE  
    [database_name . [schema_name ] . | schema_name . ] table_name  
    ( { <column_definition>  
    | [ <table_constraint> ] [ ,... n ]  
    | [ <table_index> ]  
      [ ,... n ] }   
      [ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name   
        , system_end_time_column_name ) ]  
)  
    [ WITH ( <table_option> [ ,... n ] ) ]  
 [ ; ]  
  
<column_definition> ::=  
column_name <data_type>  
    [ COLLATE collation_name ]  
    [ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]   
    [ NULL | NOT NULL ]  
[  
    [ CONSTRAINT constraint_name ] DEFAULT memory_optimized_constant_expression ]  
    | [ IDENTITY [ ( 1, 1 ) ]  
]  
    [ <column_constraint> ]  
    [ <column_index> ]  
  
<data type> ::=  
 [type_schema_name . ] type_name [ (precision [ , scale ]) ]  
  
<column_constraint> ::=  
 [ CONSTRAINT constraint_name ]  
{   
  { PRIMARY KEY | UNIQUE }    
      {   NONCLUSTERED   
        | NONCLUSTERED HASH WITH (BUCKET_COUNT = bucket_count)   
      }   
  | [ FOREIGN KEY ]   
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]   
  | CHECK ( logical_expression )   
}  
  
< table_constraint > ::=  
 [ CONSTRAINT constraint_name ]  
{    
   { PRIMARY KEY | UNIQUE }  
     {   
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])  
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )   
                    }   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    | CHECK ( logical_expression )   
}  
  
<column_index> ::=  
  INDEX index_name  
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count)  }  
  
<table_index> ::=  
  INDEX index_name  
{   [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)   
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )   
      [ ON filegroup_name | default ]  
  | CLUSTERED COLUMNSTORE [WITH ( COMPRESSION_DELAY = {0 | delay [Minutes]})]  
      [ ON filegroup_name | default ]  
  
}  
  
<table_option> ::=  
{  
    MEMORY_OPTIMIZED = ON   
  | DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}  
  | SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name  
        [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]   
  
}  

引數Arguments

database_namedatabase_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 指定的資料庫中現有使用者識別碼有關聯,且這個使用者識別碼必須具有 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_nameschema_name
這是新的資料表所屬的結構描述名稱。Is the name of the schema to which the new table belongs.

table_nametable_name
這是新資料表的名稱。Is the name of the new table. 資料表名稱必須遵照識別碼的規則。Table names must follow the rules for identifiers. table_name 最多可有 128 個字元,但本機暫存資料表名稱 (名稱前附加一個數字符號 (#)) 除外,其不可超過 116 個字元。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 Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: 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. 如需有關 FileTable 的詳細資訊,請參閱 FileTables (SQL Server)For more information about FileTables, see FileTables (SQL Server).

column_namecolumn_name
computed_column_expressioncomputed_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.

    例如,如果資料表有整數資料行 ab,您可以建立計算資料行 a+b 的索引,但不能建立計算資料行 computed column 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.

Database EngineDatabase Engine 會依據所用的運算式,來自動判斷計算資料行的 Null 屬性。Based on the expressions that are used, the nullability of computed columns is determined automatically by the Database EngineDatabase Engine. 大部分運算式的結果都會視為可為 Null,即使只存在不可為 Null 的資料行也是如此,這是因為可能出現的反向溢位或溢位也會產生 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. 請將 COLUMNPROPERTY 函數與 AllowsNull 屬性搭配使用,以調查資料表中任何計算資料行的 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 結果的非 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. 以 Common Language Runtime (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 Database EngineSQL Server Database Engine 會實際將計算值儲存在資料表中,以及在計算資料行所依賴的任何其他資料行有了更新時,也會更新這些值。Specifies that the SQL Server Database EngineSQL 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. 任何用為分割資料表之分割資料行的計算資料行,皆須明確標示為 PERSISTED。Any 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 KEY 或 UNIQUE 條件約束建立叢集索引,資料表的資料頁面會儲存在索引的相同檔案群組中。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_schemefilegrouppartition_scheme (反之亦然),則只會遵守常數定義,其他一概予以忽略。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_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

注意

在您建立分割區資料表之後,請考慮將資料表的 LOCK_ESCALATION 選項設定為 AUTO。After 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 TABLE (Transact-SQL)For more information, see ALTER TABLE (Transact-SQL).

TEXTIMAGE_ON { filegroup| " default " }TEXTIMAGE_ON { filegroup| " default " }
指示 textntextimagexml 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_ON。TEXTIMAGE_ON is not allowed if there are no large value columns in the table. 如果指定了 partition_scheme,便不能指定 TEXTIMAGE_ON。TEXTIMAGE_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 值直接儲存在資料列中,最多 8000 個位元組。Varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 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. 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_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

FILESTREAM_ON { partition_scheme_name | filegroup | " default " }FILESTREAM_ON { partition_scheme_name | filegroup | " default " }

適用於[SQL Server]SQL ServerApplies to: [SQL Server]SQL Server. Azure SQL Database 不支援 FILESTREAMAzure SQL Database does 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,使用 FILESTREAM_ON 的 CREATE TABLE 所設定的值無法變更,但下列情況除外: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) 資料 (SQL Server)For related FILESTREAM topics, see Binary Large Object (Blob) Data (SQL Server).

[ 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 Server]SQL Server 系統資料類型的別名資料類型。An alias type based on a [SQL Server]SQL 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. CLR 使用者定義型別是利用 CREATE TYPE 陳述式來建立的,之後,才能在資料表定義中使用它們。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_nameSQL Server Database EngineSQL Server Database Engine 會按照以下順序參考 type_nameIf type_schema_name is not specified, the SQL Server Database EngineSQL Server Database Engine references type_name in the following order:

  • [SQL Server]SQL Server 系統資料類型。The [SQL Server]SQL Server system data type.

  • 目前資料庫中之目前使用者的預設結構描述。The default schema of the current user in the current database.

  • 目前資料庫中的 dbo 結構描述。The dbo schema in the current database.

如需了解記憶體最佳化資料表,請參閱記憶體中 OLTP 支援的資料類型,以取得支援的系統類型清單。For memory-optimized tables, see Supported Data Types for In-Memory OLTP for a list of supported system types.

有效位數precision
這是指定之資料類型的有效位數。Is the precision for the specified data type. 如需有關有效位數值的詳細資訊,請參閱有效位數、小數位數和長度For more information about valid precision values, see Precision, Scale, and Length.

scalescale
這是指定資料類型的小數位數。Is the scale for the specified data type. 如需有關有效小數位數值的詳細資訊,請參閱有效位數、小數位數和長度For more information about valid scale values, see Precision, Scale, and Length.

maxmax
只適用於 varcharnvarcharvarbinary 資料類型,可用來儲存 2^31-1 位元組的字元和二進位資料以及 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_namexml 資料類型的每個執行個體都可以包含多個最上層元素。Specifies that each instance of the xml data type in column_name can contain multiple top-level elements. CONTENT 只適用於 xml 資料類型,而且只有在同時指定 xml_schema_collection 時,才能指定。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_namexml 資料類型的每個執行個體只可以包含單一最上層元素。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 applies only to the xml data type and can be specified only if xml_schema_collection is also specified.

xml_schema_collectionxml_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. 除了定義為 timestamp 或含有 IDENTITY 屬性的資料行之外,任何資料行都可以套用 DEFAULT 定義。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 Server]SQL Server 的相容性,您可以將條件約束名稱指派給 DEFAULT。To maintain compatibility with earlier versions of [SQL Server]SQL Server, a constraint name can be assigned to a DEFAULT.

constant_expressionconstant_expression
這是用來做為資料行預設值的常數、NULL 或系統函數。Is a constant, NULL, or a system function that is used as the default value for the column.

memory_optimized_constant_expressionmemory_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 EngineDatabase Engine 會提供資料行的唯一累加值。When a new row is added to the table, the Database EngineDatabase 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 屬性指派給 tinyintsmallintintbigintdecimal(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).

seedseed
這是載入資料表的第一個資料列所用的值。Is the value used for the very first row loaded into the table.

incrementincrement
這是加入先前載入的資料列之識別值的累加值。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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure 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.

指定系統會使用規定的 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 NULL。The 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 NULL 。If 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_TIME 和 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* ] )

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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 COLUMNSTOREINDEX index_name CLUSTERED COLUMNSTORE

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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 Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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_nameAny 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 EngineDatabase Engine 只在單一分割區內驗證索引鍵值的唯一性。This restriction allows the Database EngineDatabase Engine to verify uniqueness of key values within a single partition only.

注意

當您分割一個非唯一的叢集索引時,如果尚未指定分割區資料行,依預設,Database EngineDatabase Engine 會將它加入至叢集索引鍵清單。When you partition a non-unique, clustered index, the Database EngineDatabase Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. 當您分割一個非唯一的非叢集索引時,如果尚未指定分割區資料行,Database EngineDatabase Engine 會將它新增為索引的非索引鍵 (內含) 資料行。When partitioning a non-unique, nonclustered index, the Database EngineDatabase Engine adds the partitioning column as a non-key (included) column of the index, if it is not already specified.

如果未指定 partition_scheme_namefilegroup,且已分割資料表,則會使用相同的分割資料行,將索引放在與基礎資料表相同的資料分割配置中。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_nameON 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_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

適用於[SQL Server]SQL ServerApplies to: [SQL Server]SQL Server.

指定在建立叢集索引時,資料表之 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 NULL。FILESTREAM_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.

如需詳細資訊,請參閱 FILESTREAM (SQL Server)For more information, see FILESTREAM (SQL Server).

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 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_nameCOLUMN_ENCRYPTION_KEY = key_name
指定資料行加密金鑰。Specifies the column encryption key. 如需詳細資訊,請參閱 CREATE COLUMN ENCRYPTION KEY (Transact-SQL)For more information, see CREATE COLUMN ENCRYPTION KEY (Transact-SQL).

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 執行個體支援具有安全記憶體保護區的 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. 請參閱具有安全記憶體保護區的 Always Encrypted 以取得詳細資料。Please see Always Encrypted with secure enclaves for details.

若您使用 Always Encrypted (不具有安全記憶體保護區),請針對將使用參數或群組參數進行搜尋的資料行使用決定性加密,例如政府識別碼。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.

如果您使用具有安全記憶體保護區的 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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: 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'.

如需包括功能條件約束的詳細資訊,請參閱永遠加密 (Database Engine)For more information including feature constraints, see Always Encrypted (Database Engine).

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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: 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. 可用的函數有三個:Three functions are available:

  • default()default()

  • email()email()

  • partial()partial()

  • random()random()

如需函式參數,請參閱動態資料遮罩For function parameters, see Dynamic Data Masking.

FILESTREAMFILESTREAM

適用於[SQL Server]SQL ServerApplies to: [SQL Server]SQL Server.

僅適用於 varbinary(max) 資料行。Valid only for varbinary(max) columns. 指定 FILESTREAM 儲存體來儲存 varbinary(max) BLOB 資料。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. 這個資料行不能允許 null 值,且必須具有 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.

ROWGUIDCOL 資料行無法卸除,而且當資料表有定義 FILESTREAM 資料行時,無法變更相關的條件約束。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_nameCOLLATE 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 僅適用於 charvarchartextncharnvarcharntext 資料類型的資料行。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.

如需有關 COLLATE 子句的詳細資訊,請參閱 COLLATE (Transact-SQL)For more information about the COLLATE clause, see COLLATE (Transact-SQL).

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_nameconstraint_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
決定資料行中是否允許使用 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.

下列範例顯示如何在磁碟資料表中使用 NONCLUSTERED:The following shows how to use NONCLUSTERED in a disk-based table:

CREATE TABLE t1 ( c1 int, INDEX ix_1 NONCLUSTERED (c1))   
CREATE TABLE t2( c1 int INDEX ix_1 NONCLUSTERED (c1))   
CREATE TABLE t3( c1 int, c2 int INDEX ix_1 NONCLUSTERED)   
CREATE TABLE t4( c1 int, c2 int, INDEX ix_1 NONCLUSTERED (c1,c2))  

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 ] )( ref_column [ ,... n ] )
這是 FOREIGN KEY 條件約束所參考之資料表中的某資料行或資料行清單。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 EngineDatabase Engine 會產生一則錯誤,且會回復父資料表中之資料列的刪除動作。The Database EngineDatabase 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. 若要執行這個條件約束,外部索引鍵資料行必須可為 Null。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 的資料行,但沒有設定明確的預設值,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 CASCADE。ON 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 EngineDatabase 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 EngineDatabase 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 EngineDatabase Engine 便會產生一則錯誤,且會回復該資料列的刪除動作。Conversely, if NO ACTION is specified, the Database EngineDatabase 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 EngineDatabase Engine 會產生一則錯誤,且會回復父資料表中之資料列的更新動作。The Database EngineDatabase 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. 若要執行這個條件約束,外部索引鍵資料行必須可為 Null。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 的資料行,但沒有設定明確的預設值,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 UPDATE 已經存在已警示的資料表,則無法定義ON UPDATE CASCADE、SET NULL、或 SET DEFAULT。ON 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 EngineDatabase 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 EngineDatabase 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 EngineDatabase Engine 便會產生一則錯誤,且會回復 Vendor 資料列的更新動作。Conversely, if NO ACTION is specified, the Database EngineDatabase 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_expressionlogical_expression
這是一個傳回 TRUE 或 FALSE 的邏輯運算式。Is a logical expression that returns TRUE or FALSE. 這個運算式不能含有別名資料類型。Alias data types cannot be part of the expression.

columncolumn
這是資料表條件約束中的一個資料行或一份資料行清單 (用括號括住),用來指示條件約束定義中所用的各個資料行。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_namepartition_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.

重要

我們建議您在分割區資料表的分割資料行上指定 NOT NULL,以及在非分割區資料表 (ALTER TABLE...SWITCH 作業的來源或目標) 上進行這項作業。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 =fillfactorWITH FILLFACTOR =fillfactor
指定用來儲存索引資料的每個索引頁面,Database EngineDatabase Engine 所應加以填滿的程度。Specifies how full the Database EngineDatabase 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_COLUMNScolumn_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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure 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.

指定資料行的名稱,系統會使用這個資料行來記載某一筆記錄的有效期。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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure 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.

為了記憶體最佳化,延遲會指定資料列在資料表中至少要保持不變多少分鐘。等過了這段時間後,就可以將它壓縮到資料行存放區索引。For a memory-optimized, delay specifies the minimum number of minutes a row must remain in the table, unchanged, before it is eligible for compression into the columnstore index. SQL Server 會根據上次更新時來選取要壓縮的具體資料列。SQL 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 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 Server can compress it into the compressed rowgroup. 因為磁碟資料表不會追蹤個別資料列的插入和更新時間,因此 SQL Server 會將這段延遲時間套用於關閉狀態下的差異資料列群組。Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.

預設值是 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 2017 以及 Azure 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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure 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_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.

如需與壓縮有關的詳細資訊,請參閱資料壓縮For more information about compression, 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_expressionpartition_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 INDEX (Transact-SQL)For a complete description of these options, see CREATE INDEX (Transact-SQL).

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 =fillfactorFILLFACTOR =fillfactor
指定一個百分比來指出在建立或改變索引期間,Database EngineDatabase Engine 應該使各索引頁面之分葉層級填滿的程度。Specifies a percentage that indicates how full the Database EngineDatabase 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 = ON。In 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 EngineDatabase Engine 會決定使用資料列鎖定的時機。The Database EngineDatabase 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 EngineDatabase Engine 會決定使用頁面鎖定的時機。The Database EngineDatabase Engine determines when page locks are used. 當設為 OFF 時,不會使用頁面鎖定。When OFF, page locks are not used. 預設值是 ON。The default is ON.

FILETABLE_DIRECTORY = directory_nameFILETABLE_DIRECTORY = directory_name

適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: 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 Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017. Azure SQL Database 不支援 FILETABLEAzure SQL Database does not support FILETABLE.

指定定序名稱,用於套用至 FileTable 中的 Name 資料行。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_namecollation_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_nameFILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name

適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: 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_nameFILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name

適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: 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_nameFILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name

適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定當系統在 FileTable 的 parent_path_locatorname 資料行上,自動建立唯一條件約束時,所要使用的名稱。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 .SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]

適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure 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.

如果符合資料類型、 可 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_TIME 和 GENERATED 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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: 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 DatabaseEnabling 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 Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: 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_STATEWhen you specify a filter predicate, you also have to specify MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }

適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure SQL。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, and Azure SQL .

MEMORY_OPTIMIZEDMEMORY_OPTIMIZED

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database. Azure SQL Database 受控執行個體不支援記憶體最佳化的資料表。Azure 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 Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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=ON 使用。DURABILITY=SCHEMA_ONLY is only allowed with MEMORY_OPTIMIZED=ON.

警告

使用 DURABILITY = SCHEMA_ONLY, 和 READ_COMMITTED_SNAPSHOT 建立的資料表,在使用 ALTER DATABASE 進行變更時, 資料表中的資料會遺失。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 Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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.

INDEXINDEX

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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 Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: 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 DATABASE 的 SET 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 EngineDatabase Engine 不會強制在資料行定義中指定 DEFAULT、IDENTITY、ROWGUIDCOL 或資料行條件約束的順序。The Database EngineDatabase Engine does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

當建立資料表時,一律會在資料表的中繼資料中將 QUOTED IDENTIFIER 選項儲存成 ON,即使建立資料表時,將選項設成 OFF,也是如此。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).

SQL 陳述式會利用 CREATE TABLE 陳述式中指定給 table_name 的值來參考暫存資料表,例如:SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:

CREATE TABLE #MyTempTable (cola 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 EngineDatabase 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 EngineDatabase Engine must be able to distinguish the tables created by the different users. Database EngineDatabase Engine 會在內部將數值後置詞附加至每個本機暫存資料表名稱上,以便區分它們。The Database EngineDatabase 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  
n    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)Database scoped global temporary tables (Azure SQL Database)

適用於 [SQL Server]SQL Server 的全域暫存資料表 (資料表名稱開頭是 ##) 會儲存在 tempdb 中,並在整個 [SQL Server]SQL Server 執行個體所有使用者的工作階段之間共用。Global temporary tables for [SQL Server]SQL Server (initiated with ## table name) are stored in tempdb and shared among all users' sessions across the whole [SQL Server]SQL 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 Server]SQL Server 用於暫存資料表的相同。Global temporary tables for Azure SQL DatabaseAzure SQL Database follow the same syntax and semantics that [SQL Server]SQL 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 Server]SQL 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 Server]SQL Server uses. 請參閱上述的 暫存資料表相關章節。See the above section on Temporary Tables.  

重要

Azure SQL DatabaseAzure SQL Database 提供此項功能。This feature is available for Azure SQL DatabaseAzure SQL Database.

對 Azure SQL Database 的全域暫存資料表問題進行疑難排解Troubleshooting global temporary tables for Azure SQL Database

如需解決 tempdb 的問題,請參閱解決 tempdb 磁碟空間不足的問題For the troubleshooting the tempdb, see Troubleshooting Insufficient Disk space in tempdb.

注意

只有伺服器系統管理員可以存取 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.

範例Examples

  • 工作階段 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'; 

---Result
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

---Result
##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
---Results
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
---Results
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

資料分割資料表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 Indexes>。For 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 條件約束的叢集索引,便使用 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.

    注意

    至於經記憶體最佳化的資料表,允許可為 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 條件約束的 NONCLUSTERED 或 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 TRIGGER (Transact-SQL)For more information, see CREATE TRIGGER (Transact-SQL).

  • 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 EngineDatabase Engine 發現 NO ACTION,它會停止和回復相關的 CASCADE、SET NULL 和 SET DEFAULT 動作。If the Database EngineDatabase 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 EngineDatabase 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 EngineDatabase Engine checks for any NO ACTION.

  • 在資料表所能包含參考其他資料表的 FOREIGN KEY 條件約束數目,及其他資料表所擁有參考特定資料表的 FOREIGN KEY 條件約束數目上, Database EngineDatabase Engine 並沒有預先定義的限制。The Database EngineDatabase 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. 下表顯示在 INSERT 陳述式期間,niladic 函數及它們傳回的預設值。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.
    使用者USER 執行插入的使用者名稱。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.

    CHECK CONSTRAINTS 和規則會在 INSERT 和 UPDATE 陳述式期間,提供相同的資料驗證功能。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.

  • textntextimage 資料行上,無法定義 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.

  • 當在 INSERT、UPDATE 或 DELETE 陳述式中違反條件約束時,陳述式便會結束。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 EngineDatabase Engine 會選擇適當的鎖定,且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。The Database EngineDatabase Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock. 如果 ALLOW_ROW_LOCKS = OFFALLOW_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 通常隱含了值不明或不適用的意思。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 TABLE 或 ALTER 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,如果您採用使用者自訂資料類型,建議您允許資料行使用資料類型的預設 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 屬性時,資料行 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 EngineDatabase Engine 會使用建立資料類型時所指定的 Null 屬性。The Database EngineDatabase Engine uses the nullability that is specified when the data type was created. 若要判斷資料類型的預設可 Null 性,請使用 sp_helpTo 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 EngineDatabase Engine 來自動決定。If the column is a computed column, its nullability is always automatically determined by the Database EngineDatabase 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 驅動程式和 Microsoft OLE DB Provider for SQL Server 都預設為將 ANSI_NULL_DFLT_ON 設為 ON。The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server 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 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

下列範例會顯示 PRIMARY KEY 條件約束的資料行定義,其中 EmployeeID 資料表的 Employee 資料行上包含叢集索引。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 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. 請注意,資料表結構描述會變更為 dbo,以執行範例。Note 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_namexml 資料類型的每個執行個體,都只能包含一個最上層元素。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 > 1000 col1 > 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

下列範例會建立一份含有 FILESTREAM 資料行 Photo 的資料表。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 2017 以及 Azure 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 2017 以及 Azure 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. 如需詳細資訊,請參閱永遠加密 (Database Engine)For more information, see Always Encrypted (Database Engine).

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   
 )
GO

T.T. 建立具有匿名具名複合主索引鍵的暫存資料表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.

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
COLUMNPROPERTY (Transact-SQL) COLUMNPROPERTY (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
資料類型 (Transact-SQL) Data Types (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL) sys.dm_sql_referencing_entities (Transact-SQL)
DROP TABLE (Transact-SQL) DROP TABLE (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL) CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE TYPE (Transact-SQL) CREATE TYPE (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helpconstraint (Transact-SQL) sp_helpconstraint (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sp_spaceused (Transact-SQL)sp_spaceused (Transact-SQL)