SQL Server series: Creating Data tables

A table is a logical structure used to store data and manipulate data, to organize and store data, all data in a relational database is represented as a table, and the data table consists of rows and columns. The data tables in SQL Server are temporary tables and permanent tables, which are stored in the tempdb system database and are automatically deleted when SQL Server is no longer used or exited; once created, the permanent table will remain in the database file unless the user deletes it.

There are two ways to create a data table: (1) created through Object Explorer, and (2) through Transact-SQL statements.

1. Transact-SQL CREATE TABLE

1> syntax

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] DEFAULTConstant_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 ascomputed_column_expression[PERSISTED [Not NULL] ][[CONSTRAINT constraint_name]    { PRIMARY KEY | UNIQUE }        [CLUSTERED | Nonclustered]        [with FILLFACTOR = FILLFACTOR | With (<index_option> [, ... n] )        ]    | [FOREIGN KEY]         REFERENCESReferenced_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 forAll_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] )         REFERENCESReferenced_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 series: Creating Data tables