首页 > 代码库 > Sql Server系列:创建数据表

Sql Server系列:创建数据表

  表是用来存储数据和操作数据的逻辑结构,用来组织和存储数据,关系数据库中的所有数据都表现为表的形式,数据表由行和列组成。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系列:创建数据表