Sql Server系列：建立資料表

標籤：des   style   blog   io   color   ar   使用   sp   for   

　　表是用來儲存資料和操作資料的邏輯結構，用來組織和儲存資料，關聯式資料庫中的所有資料都表現為表的形式，資料表由行和列組成。SQL Server中的資料表分為暫存資料表和永久表，暫存資料表儲存在tempdb系統資料庫中，當不再使用或退出SQL Server時，暫存資料表會自動刪除；永久表一旦建立之後，除非使用者刪除，否則將一直存在資料庫檔案中。

　　建立資料表的兩種方法：(1) 通過物件總管建立；(2) 通過Transact-SQL語句進行建立。

1. Transact-SQL建立表

1> 文法

CREATE TABLE     [ database_name . [ schema_name ] . | schema_name . ] table_name         ( { <column_definition> | <computed_column_definition>                 | <column_set_definition> }        [ <table_constraint> ] [ ,...n ] )     [ 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 ]     [ NULL | NOT NULL ]    [         [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]       | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]     ]    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]     [ SPARSE ] <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 ) } <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 ] )        ]    | [ 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 )     [ ON { partition_scheme_name ( partition_column_name )         | filegroup | "default" } ]] <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_option> ::={    DATA_COMPRESSION = { NONE | ROW | PAGE }      [ ON PARTITIONS ( { <partition_number_expression> | <range> }             [ , ...n ] ) ]}<index_option> ::={     PAD_INDEX = { ON | OFF }   | FILLFACTOR = fillfactor   | IGNORE_DUP_KEY = { ON | OFF }   | STATISTICS_NORECOMPUTE = { ON | OFF }   | ALLOW_ROW_LOCKS = { ON | OFF}   | ALLOW_PAGE_LOCKS ={ ON | OFF}   | DATA_COMPRESSION = { NONE | ROW | PAGE }       [ ON PARTITIONS ( { <partition_number_expression> | <range> }        [ , ...n ] ) ]}<range> ::= <partition_number_expression> TO <partition_number_expression>

 

Sql Server系列：建立資料表