T-SQL的CREATE TABLE語法(上)

 
建立新表。
語法
CREATE TABLE
    [ database_name.[ owner ] .| owner.] table_name
    ( { < column_definition >
        | column_name AS computed_column_expression
        | < table_constraint > ::= [ CONSTRAINT constraint_name ] }

             | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
    [ COLLATE < collation_name > ]
    [ [ DEFAULT constant_expression ]
        | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ]
     [ ROWGUIDCOL]
    [ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
     { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ON {filegroup | DEFAULT} ] ]
        ]
         | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        ]
         | CHECK [ NOT FOR REPLICATION ]
        ( logical_expression )
     }

< table_constraint > ::= [ CONSTRAINT constraint_name ]
     { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
         { ( column [ ASC | DESC ] [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
    ]
     | FOREIGN KEY
        [ ( column [ ,...n ] ) ]
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { CASCADE | NO ACTION } ]
        [ ON UPDATE { CASCADE | NO ACTION } ]
        [ NOT FOR REPLICATION ]
    | CHECK [ NOT FOR REPLICATION ]
        ( search_conditions )
     }
引數

database_name

是要在其中建立表的資料庫名稱。database_name 必須是現有資料庫的名稱。如果不指定資料庫，I5無件TV管+b)]網
(Ks05k件管軟4E中#\無K
gg-28YP絡TzrV

database_name 預設為當前資料庫。當前連線的登入必須在 database_name 所指定的資料庫中有關聯的現有使用者 ID，而該使用者 ID 必須具有建立表的許可權。

owner

是新表所有者的使用者 ID 名，owner 必須是 database_name 所指定的資料庫中的現有使用者 ID，owner 預設為與 database_name 所指定的資料庫中的當前連線相關聯的使用者 ID。如果 CREATE TABLE 語句由 sysadmin 固定伺服器角色成員或 database_name 所指定的資料庫中的 db_dbowner 或 db_ddladmin 固定資料庫角色成員執行，則 owner 可以指定與當前連線的登入相關聯的使用者 ID 以外的其它使用者 ID。如果與執行 CREATE TABLE 語句的登入相關聯的使用者 ID 僅具有建立表的許可權，則 owner 必須指定與當前登入相關聯的使用者 ID。sysadmin 固定伺服器角色成員或別名為 dbo 使用者的登入與使用者 ID dbo 相關聯；因此，由這些使用者建立的表的預設所有者為 dbo。不是由上述兩種角色的登入建立的表所有者預設為與該登入相關聯的使用者 ID。

table_name

是新表的名稱。表名必須符合識別符號規則。資料庫中的 owner.table_name 組合必須唯一。table_name 最多可包含 128 個字元，但本地臨時表的表名（名稱前有一個編號符 #）最多隻能包含 116 個字元。

column_name

是表中的列名。列名必須符合識別符號規則，並且在表內唯一。以 timestamp 資料型別建立的列可以省略 column_name。如果不指定 column_name，timestamp 列的名稱預設為 timestamp。

computed_column_expression

是定義計算列值的表示式。計算列是物理上並不儲存在表中的虛擬列。計算列由同一表中的其它列通過表示式計算得到。例如，

!c%(v+2)7ZT育*uw育~

計算列可以這樣定義：cost AS price * qty。表示式可以是非計算列的列名、常量、函式、變數，也可以是用一個或多個運算子連線的上述元素的任意組合。表示式不能為子查詢。

計算列可用於選擇列表、WHERE 子句、ORDER BY 子句或任何其它可使用常規表示式的位置，但下列情況除外：

    * 計算列不能用作 DEFAULT 或 FOREIGN KEY 約束定義，也不能與 NOT NULL 約束定義一起使用。但是，如果計算列由具有確定性的表示式定義，並且索引列中允許計算結果的資料型別，則可將該列用作索引中的鍵列，或用作 PRIMARY KEY 或 UNIQUE 約束的一部分。

      例如，如果表中含有整型列 a 和 b，則可以在計算列 a+b 上建立索引。但不能在計算列 a+DATEPART(dd, GETDATE()) 上建立索引，因為在以後的呼叫中，其值可能發生改變。
    * 計算列不能作為 INSERT 或 UPDATE 語句的目標。

      說明   表中計算列所使用的列值因行而異，[@o軟N垠:V!V管}供的
      tXl供iZ]業S-絡
      因此每行的計算列值可能不同。

      計算列的為空性是由 SQL Server 根據使用的表示式自動確定的。即使只有不可為空的列，大多數表示式的結果也認為是可為空的，因為可能的下溢或溢位也將生成 NULL 結果。使用 COLUMNPROPERTY 函式（AllowsNull 屬性）檢視錶中任何計算列的為空性。通過指定 ISNULL(check_expression, constant)，其中常量為替代任何 NULL 結果的非 NULL 值，可為空的表示式 expr 可以轉換為不可為空的表示式。

ON {filegroup | DEFAULT}

指定儲存表的檔案組。如果指定 filegroup，則表將儲存在指定的檔案組中。資料庫中必須存在該檔案組。如果指定 DEFAULT，或者根本未指定 ON 引數，則表儲存在預設檔案組中。

ON {filegroup | DEFAULT} 也可以在 PRIMARY KEY 約束或 UNIQUE 約束中指定。這些約束會建立索引。如果指定 filegroup，則索引將儲存在指定的檔案組中。如果指定 DEFAULT，則索引將儲存在預設檔案組中。如果約束中沒有指定檔案組，則索引將與表儲存在同一檔案組中。如果 PRIMARY KEY 約束或 UNIQUE 約束建立聚集索引，則表的資料頁將與索引儲存在同一檔案組中。

說明   在 ON {filegroup | DEFAULT} 和 TEXTIMAGE_ON {filegroup | DEFAULT} 的上下文中，DEFAULT 並不是關鍵字。DEFAULT 是預設檔案組的識別符號並需對其進行定界，如 ON "DEFAULT"、ON [DEFAULT] 和 TEXTIMAGE_ON "DEFAULT" 或 TEXTIMAGE_ON [DEFAULT]。

TEXTIMAGE_ON

是表示 text、ntext 和 image 列儲存在指定檔案組中的關鍵字。如果表中沒有 text、ntext 或 image 列，則不能使用 TEXTIMAGE ON。如果沒有指定 TEXTIMAGE_ON，則 text、ntext 和 image 列將與表儲存在同一檔案組中。

data_type

指定列的資料型別。可以是系統資料型別或使用者定義資料型別。使用者定義資料型別必須先用 sp_addtype 建立，然後才能在表定義中使用。

在 CREATE TABLE 語句中，使用者定義資料型別的 NULL/NOT NULL 賦值可被替代。但長度標準不能更改；不能在 CREATE TABLE 語句中指定使用者定義資料型別的長度。

DEFAULT

如果在插入過程中未顯式提供值，則指定為列提供的值。DEFAULT 定義可適用於除定義為 timestamp 或帶 IDENTITY 屬性的列以外的任何列。除去表時，將刪除 DEFAULT 定義。只有常量值（如字串）、系統函式（如 SYSTEM_USER()）或 NULL 可用作預設值。為保持與 SQL Server 早期版本的相容，可以給 DEFAULT 指派約束名。

constant_expression

是用作列的預設值的常量、NULL 或系統函式。

IDENTITY

表示新列是標識列。當向表中新增新行時，gaIp9:ts='}:Microsoft® SQL Server™ 將為該標識列提供一個唯一的、遞增的值。標識列通常與 PRIMARY KEY 約束一起用作表的唯一行識別符號。可以將 IDENTITY 屬性指派給 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。對於每個表只能建立一個標識列。不能對標識列使用繫結預設值和 DEFAULT 約束。必須同時指定種子和增量，或者二者都不指定。如果二者都未指定，則取預設值 (1,1)。

seed

是裝入表的第一行所使用的值。

increment

是新增到前一行的標識值的增量值。

NOT FOR REPLICATION

表示當複製登入（如 sqlrepl）向表中插入資料時，不強制 IDENTITY 屬性。複製的行必須保留髮布資料庫中所賦予的鍵值；NOT FOR REPLICATION 子句確保不向複製程式所插入的行賦予新的標識值。其它登入所插入的行仍然具有以通常的方式建立的新標識值。建議同時使用具有 NOT FOR REPLICATION 的 CHECK 約束，以確保賦予的標識值處於當前資料庫所需的範圍內。

ROWGUIDCOL

表示新列是行的全域性唯一識別符號列。對於每個表只能指派一個 uniqueidentifier 列作為 ROWGUIDCOL 列。ROWGUIDCOL 屬性只能指派給 uniqueidentifier 列。如果資料庫相容級別小於或等於 65，則 ROWGUIDCOL 關鍵字無效。有關更多資訊，請參見 sp_dbcmptlevel。

ROWGUIDCOL 屬性並不強制列中所儲存值的唯一性。該屬性也不會為插入到表中的新行自動生成值。若要為每列生成唯一值，那麼或者在 INSERT 語句中使用 NEWID 函式，或者將 NEWID 函式指定為該列的預設值。

collation_name

指定列的排序規則。排序規則名稱既可以是 Windows 排序規則名稱，也可以是 SQL 排序規則名稱。collation_name 僅適用於資料型別為 char、varchar、text、nchar、nvarchar 及 ntext 的列。如果沒有指定該引數，那麼如果列的資料型別是使用者定義的，則該列的排序規則就是使用者定義資料型別的排序規則，否則就是資料庫的預設排序規則。

有關 Windows 和 SQL 排序規則名稱的更多資訊，請參見 COLLATE。

CONSTRAINT

是可選關鍵字，表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY 或 CHECK 約束定義的開始。約束是特殊屬性，用於強制資料完整性並可以為表及其列建立索引。

constrain_name

是約束的名稱。約束名在資料庫內必須是唯一的。

NULL | NOT NULL

是確定列中是否允許空值的關鍵字。從嚴格意義上講，NULL 不是約束，但可以使用與指定 NOT NULL 同樣的方法指定。

PRIMARY KEY

是通過唯一索引對給定的一列或多列強制實體完整性的約束。對於每個表只能建立一個 PRIMARY KEY 約束。

UNIQUE

是通過唯一索引為給定的一列或多列提供實體完整性的約束。一個表可以有多個 UNIQUE 約束。

CLUSTERED | NONCLUSTERED

是表示為 PRIMARY KEY 或 UNIQUE 約束建立聚集或非聚集索引的關鍵字。PRIMARY KEY 約束預設為 CLUSTERED，UNIQUE 約束預設為 NONCLUSTERED。

在 CREATE TABLE 語句中只能為一個約束指定 CLUSTERED。如果在為 UNIQUE 約束指定 CLUSTERED 的同時又指定了 PRIMARY KEY 約束，則 PRIMARY KEY 將預設為 NONCLUSTERED。

[ WITH FILLFACTOR = fillfactor ]

指定 SQL Server 儲存索引資料時每個索引頁的充滿程度。使用者指定的 fillfactor 取值範圍從 1 到 100。如果沒有指定 fillfactor，則預設為 0。建立索引時，fillfactor 的值越低，不必分配新空間即可由新索引項使用的空間就越多。

FOREIGN KEY...REFERENCES

是為列中的資料提供引用完整性的約束。FOREIGN KEY 約束要求列中的每個值在被引用表中對應的被引用列中都存在。FOREIGN KEY 約束只能引用被引用表中為 PRIMARY KEY 或 UNIQUE 約束的列或被引用表中在 UNIQUE INDEX 內引用的列。

ref_table

是 FOREIGN KEY 約束所引用的表名。

(ref_column[,...n])

是 FOREIGN KEY 約束所引用的表中的一列或多列。

ON DELETE {CASCADE | NO ACTION}

指定當要建立的表中的行具有引用關係，並且從父表中刪除該行所引用的行時，要對該行採取的操作。預設設定為 NO ACTION。

如果指定 CASCADE，則從父表中刪除被引用行時，也將從引用表中刪除引用行。如果指定 NO ACTION，SQL Server 將產生一個錯誤並回滾父表中的行刪除操作。

例如，在 Northwind 資料庫中，Orders 表和 Customers 表之間有引用關係。Orders.CustomerID 外來鍵引用 Customers.CustomerID 主鍵。

如果對 Customers 表的某行執行 DELETE 語句，並且為 Orders.CustomerID 指定 ON DELETE CASCADE 操作，則 SQL Server 將在 Orders 表中檢查是否有與被刪除的行相關的一行或多行。如果存在相關行，則 Orders 表中的相關行將隨 Customers 表中的被引用行一同刪除。

反之，如果指定 NO ACTION，若在 Orders 表中至少有一行引用 Customers 表中要刪除的行，則 SQL Server 將產生一個錯誤並回滾 Customers 表中的刪除操作。

ON UPDATE {CASCADE | NO ACTION}

指定當要建立的表中的行具有引用關係，並且在父表中更新該行所引用的行時，要對該行採取的操作。預設設定為 NO ACTION。

如果指定 CASCADE，則在父表中更新被引用行時，也將在引用表中更新引用行。如果指定 NO ACTION，SQL Server 將產生一個錯誤並回滾父表中的行更新操作。

例如，在 Northwind 資料庫中，Orders 表和 Customers 表之間有引用關係：Orders.CustomerID 外來鍵引用 Customers.CustomerID 主鍵。

如果對 Customers 表的某行執行 UPDATE 語句，並且為 Orders.CustomerID 指定 ON UPDATE CASCADE 操作，則 SQL Server 將在 Orders 表中檢查是否有與被更新行相關的一行或多行。如果存在相關行，則 Orders 表中的相關行將隨 Customers 表中的被引用行一同更新。

反之，如果指定 NO ACTION，若在 Orders 表中至少有一行引用 Customers 行，則 SQL Server 將產生一個錯誤並回滾對 Customers 行的更新操作。

CHECK

是通過限制可輸入到一列或多列中的可能值強制域完整性的約束。

NOT FOR REPLICATION

是用於防止在複製所使用的分發過程中強制 CHECK 約束的關鍵字。當表是複製釋出的訂戶時，請不要直接更新訂閱表，而要更新發布表，然後讓複製程式將資料分發回訂閱表。可以在訂閱表上定義 CHECK 約束，以防使用者修改訂閱表。但是如果不使用 NOT FOR REPLICATION 子句，CHECK 約束同樣會防止複製程式將修改從釋出表分發給訂閱表。NOT FOR REPLICATION 子句表示對使用者的修改（而不是對複製程式）強加約束。

NOT FOR REPLICATION CHECK 約束適用於被更新記錄的前像和後像，以防在複製範圍中新增記錄或從複製範圍中刪除記錄。將檢查所有刪除和插入操作；如果操作在複製範圍內，則拒絕執行該操作。

如果對識別符號列使用此約束，則當複製使用者更新標識列時，SQL Server 將允許不必重新計算表標識列的種子值。

logical_expression

是返回 TRUE 或 FALSE 的邏輯表示式。

column

是用括號括起來的一列或多列，在表約束中表示這些列用在約束定義中。

[ASC | DESC]

指定加入到表約束中的一列或多列的排序次序。預設設定為 ASC。

n

是表示前面的項可重複 n 次的佔位符。
註釋

SQL Server 的每個資料庫最多可儲存 20 億個表，每個表可以有 1024 列。表的行數及總大小僅受可用儲存空間的限制。每行最多可以儲存 8,060 位元組。如果建立具有 varchar、nvarchar 或 varbinary 列的表，並且列的位元組總數超過 8,060 位元組，雖然仍可以建立此表，但會出現警告資訊。如果試圖插入超過 8,060 位元組的行或對行進行更新以至位元組總數超過 8,060，將出現錯誤資訊並且語句執行失敗。

包含 sql_variant 列的 CREATE TABLE 語句可以生成下列警告：

The total row size (xx) for table ''''yy'''' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

出現該警告是因為 sql_variant 的最大長度只能為 8016 位元組。當某個 sql_variant 列包含與最大長度接近的值時，它可以超過行的最大大小限制。

每個表最多可以有 249 個非聚集索引和一個聚集索引。其中包括所有為支援表中所定義的 PRIMARY KEY 和 UNIQUE 約束而生成的索引。

SQL Server 在列定義中並不強制以特定的順序指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列約束。
臨時表

可以建立本地和全域性臨時表。本地臨時表僅在當前會話中可見；全域性臨時表在所有會話中都可見。

本地臨時表的名稱前面有一個編號符 (#table_name)，而全域性臨時表的名稱前面有兩個編號符 (##table_name)。

SQL 語句使用 CREATE TABLE 語句中為 table_name 指定的名稱引用臨時表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

如果本地臨時表由儲存過程建立或由多個使用者同時執行的應用程式建立，則 SQL Server 必須能夠區分由不同使用者建立的表。為此，SQL Server 在內部為每個本地臨時表的表名追加一個數字字尾。儲存在 tempdb 資料庫的 sysobjects 表中的臨時表，其全名由 CREATE TABLE 語句中指定的表名和系統生成的數字字尾組成。為了允許追加字尾，為本地臨時表指定的表名 table_name 不能超過 116 個字元。

除非使用 DROP TABLE 語句顯式除去臨時表，否則臨時表將在退出其作用域時由系統自動除去：

    * 當儲存過程完成時，將自動除去在儲存過程中建立的本地臨時表。由建立表的儲存過程執行的所有巢狀儲存過程都可以引用此表。但呼叫建立此表的儲存過程的程式無法引用此表。
    * 所有其它本地臨時表在當前會話結束時自動除去。
    * 全域性臨時表在建立此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯只在單個 Transact-SQL 語句的生存週期內保持。換言之，當建立全域性臨時表的會話結束時，最後一條引用此表的 Transact-SQL 語句完成後，將自動除去此表。
*

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-584391/，如需轉載，請註明出處，否則將追究法律責任。