The syntax of create table is:
create table [database.[owner].]table_name (column_name datatype
[default {constant_expression | user | null}]
{[{identity | null | not null}]
[off row | in row]
[[constraint constraint_name]
{{unique | primary key}
[clustered | nonclustered] [asc | desc]
[with {fillfactor = pct,
max_rows_per_page = num_rows,
reservepagegap = num_pages}]
[on segment_name]
| references [[database.]owner.]ref_table [
ref_column)]
| check (search_condition)}]}...
| [constraint constraint_name]
{{unique | primary key}
[clustered | nonclustered]
(column_name [asc | desc]
[{, column_name [asc | desc]}...])
[with {fillfactor = pct
max_rows_per_page = num_rows ,
reservepagegap = num_pages }]
[on segment_name]
|foreign key (column_name [{,column_name}...])
references [[database.]owner.]ref_table
[(ref_column [{, ref_column}...])]
| check (search_condition) ...}
[{, {next_column | next_constraint}}...])
[lock {datarows | datapages | allpages}]
[with {max_rows_per_page = num_rows,
exp_row_size = num_bytes,
reservepagegap = num_pages,
identity_gap = num_values}]
[on segment_name ]
[[external table] at pathname]
For a complete discussion of the syntax, see create
table in the Reference Manual.
The create table statement:
Defines each column in the table.
Provides the column name and datatype and specifies how each column handles null values.
Specifies which column, if any, has the IDENTITY property.
Defines column-level integrity constraints and table-level integrity constraints. Each table definition can have multiple constraints per column and per table.
For example, the create table statement for the titles table in the pubs2 database is:
create table titles (title_id tid, title varchar(80) not null, type char(12), pub_id char(4) null, price money null, advance money null, royalty int null, total_sales int null, notes varchar(200) null, pubdate datetime, contract bit not null)
The following sections describe components of table definition: system-supplied datatypes, user-defined datatypes, null types, and IDENTITY columns.
The on segment_name extension
to create table allows you to place your table
on an existing segment. segment_name points
to a specific database device or a collection of database devices.
Before creating a table on a segment, see a System Administrator
or the Database Owner for a list of segments that you can use. Certain
segments may be allocated to specific tables or indexes for performance
reasons, or for other considerations.
