CREATE TABLE Statement

For SQL Server 6.5 information, see CREATE TABLE Statement in the What's New for SQL Server 6.5.

Creates a new table.

Syntax

CREATE TABLE [database.[owner].]table_name
(
    {col_name column_properties [constraint [constraint [...constraint]]]
    | [[,] constraint]}
        [[,] {next_col_name | next_constraint}...]
)
[ON segment_name]

where

table_name

Is the name of the new table. Table names must conform to the rules for identifiers and must be unique within the database and for its owner.

You can create local and global temporary tables. Local temporary tables are visible in the current session only; global temporary tables are visible to all sessions. Temporary tables names are stored in the tempdb..sysobjects table by their names and a system-supplied numeric suffix. Local temporary tables are automatically dropped at the end of the current session or, for global temporary tables, at the end of the last session using the table. Normally, this is when the session that created the table ends.

Signify temporary tables by preceding the table_name with a single pound sign (#table_name) for local temporary tables and a double pound sign (##table_name) for global temporary tables. For local temporary tables, the complete name, including #, cannot exceed 20 characters.

When creating local or global temporary tables, the CREATE TABLE syntax supports constraint definitions; however, FOREIGN KEY constraints are not enforced on temporary tables.

If you are listed in that database's sysusers table and have CREATE TABLE permission in that database, you can create a table in a database different from the current one by fully qualifying the table_name.

col_name

Is the name of a column in the table. Column names must conform to the rules for identifiers and must be unique in the table.

column_properties =

datatype [NULL | NOT NULL | IDENTITY[(seed, increment)]]

datatype

Specifies the datatype of the column. System or user-defined datatypes are acceptable.

IDENTITY[(seed, increment)]

Generates incremental values for new rows based on the seed and increment parameters. If used, the seed value will be assigned to the first row in the table and each subsequent row will receive the next identity value, equal to the last identity plus the increment value. If neither argument is given, both will default to 1.

The IDENTITY property can be assigned to a tinyint, smallint, int, decimal(p,0), or numeric(p,0) column that does not allow null values. Only one column per table can be defined as an identity column. Defaults and DEFAULT constraints cannot be bound to an identity column, and an identity value cannot be changed. For more information, see "IDENTITY Property," later in this topic.

constraint =

Can include a table- or column-level constraint(s) where there are at most, one PRIMARY KEY per table, no more than 249 UNIQUE constraints, no more than 31 FOREIGN KEY constraints per table (each of which can reference at most 16 columns), one DEFAULT constraint per column, and any number of CHECK constraints. All can be entered within the same CREATE TABLE statement.

For a PRIMARY KEY constraint:

[CONSTRAINT constraint_name]
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 [..., col_name16]])
[ON segment_name]

For a UNIQUE constraint:

[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 [..., col_name16]])
[ON segment_name]

For a FOREIGN KEY constraint:

[CONSTRAINT constraint_name]
[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
REFERENCES [owner.]ref_table [(ref_col [, ref_col2
[..., ref_col16]])]

For a DEFAULT constraint:

[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL}
[FOR col_name]

For a CHECK constraint(s):

[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)

CONSTRAINT constraint_name

Names the given constraint within the database. Constraint names must follow the rules for identifiers, except that the name cannot begin with a pound sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint. The constraint_name will appear in any error message about constraint violations. If constraint requirements change, the constraint must be dropped and re-created using the ALTER TABLE statement.

PRIMARY KEY [CLUSTERED | NONCLUSTERED]

Enforces entity integrity for a given column or columns. All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint will have their nullability set to NOT NULL.

To enforce uniqueness of a primary key, SQL Server automatically creates a unique index on that column or columns. This unique index can be dropped only by dropping the associated table or the PRIMARY KEY constraint.

If no index type is specified, a CLUSTERED index is created by default. If NONCLUSTERED is specified or if CLUSTERED is specified for a different constraint in the same statement block, a NONCLUSTERED index is created.

Only one PRIMARY KEY constraint can be specified for a given table. However, "alternate" or "candidate" keys can be effectively created with a UNIQUE constraint.

UNIQUE [CLUSTERED | NONCLUSTERED]

Provides entity integrity for a given column or columns. Although columns participating in UNIQUE constraints can allow null values, it is not recommended that you use null values. If null values are necessary, the complete key (a single column or multiple columns for a composite index) cannot be NULL for more than one row. Multiple UNIQUE constraints can be defined on any given table. SQL Server automatically creates a unique index on this column or columns. Indexes created for use in UNIQUE constraint enforcement can be dropped only by dropping the associated table or constraint. If no index type is specified, a NONCLUSTERED index is created by default.

[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])]

Provide single- or multicolumn referential integrity. When defined, the number of columns and datatypes of each column specified in the FOREIGN KEY clause must identically match the REFERENCES clause. A non-null value entered in this column(s) must exist in the table and column(s) defined in the REFERENCES clause, and the referenced table's columns should have a PRIMARY KEY or UNIQUE constraint defined on them. For column-level constraints, using the FOREIGN KEY and col_name identifiers are optional.

Important REFERENCE constraints can reference only tables within the same database; this can include the same table on which the reference is defined (self-referenced tables). If you want cross-database referential integrity or custom messaging, implement these through triggers.

A table can have a maximum of 31 FOREIGN KEY references. This limit is an absolute upper limit, but the maximum may be lower depending on the number of work tables the server has to create to enforce the constraint, and the limit varies by the type of query being executed. FOREIGN KEY constraints are not enforced for temporary tables.

FOREIGN KEY constraints, unlike PRIMARY KEY constraints, do not create an index. To improve performance for data retrieval operations, use the CREATE INDEX statement to create an index on a foreign key column. This will allow for quicker execution times when a referenced key is modified.

For a FOREIGN KEY to be successfully created, the user must have SELECT or REFERENCES permission on the column or columns to which the FOREIGN KEY refers. For details, see the GRANT statement. When a specific reference column(s) is not provided, the primary key for the referenced table is used.

DEFAULT constant_expression | niladic-function | NULL

Specifies the value that will be provided for the column when a value is not explicitly supplied during an insert. A DEFAULT constraint can contain constant values, functions, built-in functions that do not take arguments (niladic-functions), or NULL. If used, a constant_expression must follow the same rules as defaults.

DEFAULT constraints can be created on columns of any datatype except columns that are the timestamp datatype or have the IDENTITY property. If a DEFAULT constraint is bound to a column defined with a user-defined datatype that has a default bound to it, the DEFAULT constraint is not allowed and the CREATE TABLE statement will fail. The default must be unbound from the user-defined datatype before that datatype can be used in a table definition with a DEFAULT constraint.

Niladic-functions allow a system-supplied value to be inserted when no value is specified. ANSI-standard niladic-functions include:

• USER
• CURRENT_USER
• SESSION_USER
• SYSTEM_USER
• CURRENT_TIMESTAMP

In SQL Server 6.0, USER, CURRENT_USER, and SESSION_USER all default to the database username of the user performing the insert or update. SYSTEM_USER will provide the login ID, and CURRENT_TIMESTAMP will provide the same information as the GETDATE() function.

Benefits to using a DEFAULT constraint instead of a default (created with the CREATE DEFAULT statement) are that no explicit binding/unbinding is required and DEFAULT constraints are removed when the table is dropped.

When a DEFAULT is created as a table-level constraint, the column to which it applies is specified with FOR col_name.

CHECK (expression)

Enforces domain integrity by limiting the possible values that can be entered into a column or columns. The search condition must evaluate to a Boolean expression and cannot contain subqueries. CHECK constraints adhere to the same policies as rules; however, they are automatically bound to the columns on which they are defined. A column-level CHECK constraint can only reference the constrained column, and a table-level CHECK constraint can only reference columns of the constrained table. Multiple CHECK constraints can be defined for a table; however, only one can be defined per column per CREATE TABLE statement (although each column constraint can have multiple conditions). When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated. If defined on multiple columns, CHECK constraints must be defined as table-level constraints.

NOT FOR REPLICATION

Prevents the specified CHECK constraint from being enforced for the distribution process used by replication; however, CHECK constraints will be enforced for all other users (including the system administrator). NOT FOR REPLICATION is used to protect horizontally partitioned tables that receive "source" data from a publishing server of data modifications to the replicated data. When replication is in effect, any modifications to the data that is replicated should be made on the publishing server (the main source of the data) and not on any of the subscribing servers (each of which receives exact copies of the publishing server's data).

The NOT FOR REPLICATION CHECK constraint will be applied to both the "before" and "after" image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts will be checked; if they fall within the replicated range, they will be rejected.

ON segment_name

If specified with a PRIMARY KEY or UNIQUE constraint, creates only the index on the named segment. If that key is defined with a clustered index, the entire table will be placed on the named segment. For more information, see the CREATE INDEX statement.

If not specified with a constraint, ON segment_name creates the table on the named segment.

Caution If you create a table on a segment and then create a clustered index on that table without specifying a segment name, the entire table migrates to the default segment (unless the SORTED_DATA option is used).

When using ON segment_name, the logical device must already be assigned to the database by the CREATE DATABASE statement or the ALTER DATABASE statement, and the segment must have been previously created in the database with the sp_addsegment system stored procedure. To see a list of segment names available in your database, use the sp_helpsegment system stored procedure.

Important Constraints are not enforced in the same batch as CREATE TABLE. If you want constraints to take effect for all new rows, do not insert, delete, or update data in the batch in which CREATE TABLE is executed.

When a constraint is violated, the command is terminated. However, the transaction (if the statement is part of an explicit transaction) will continue to be processed. If desired, use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@error global variable.

FOREIGN KEY constraints are not enforced on temporary tables.

Remarks

SQL Server can have as many as 2 billion tables per database and 250 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum size of a database is 1 terabyte (TB). A table can be as large as 1 TB minus the size of the database catalog and any other objects. The maximum number of bytes per row is 1962. If you create tables with varchar or varbinary columns whose total defined width exceeds 1962 bytes, the table is created but a warning message appears. Trying to insert more than 1962 bytes into such a row or to update a row so that its total row size exceeds 1962, produces an error message and the statement fails. For details on calculating row size, see the Microsoft SQL Server Administrator's Companion.

The table is created in the currently open database unless a different database is explicitly specified in the CREATE TABLE statement with the optional database name. (Cross-database creation of tables and indexes is allowed as long as the creator is listed in the sysusers table of the other database and has CREATE TABLE permission in that database. However, cross-database creation of views, rules, defaults, stored procedures, and triggers is not allowed.)

User-defined datatypes are defined in terms of system datatypes. User-defined datatypes permit frequently used type information to be accessed by a name you choose with a specified rule, default, and display format attached to it. User-defined datatypes are created with the sp_addtype system stored procedure before they can be used in a table definition.

The NULL/NOT NULL assignment for a user-defined datatype can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; you cannot specify a length for a user-defined datatype in a CREATE TABLE statement.

For a report on a table and its columns, use the sp_help or sp_helpconstraint system stored procedure. To rename a table, use the sp_rename system stored procedure. For a report on the views and stored procedures that depend on a table, use the sp_depends system stored procedure.

Space is allocated to tables and indexes in increments of one extent, (eight pages) at a time. An extent is allocated when the table or index is created, and another extent is allocated each time the previous extent becomes full. For a report on the amount of space allocated and used by a table, execute the sp_spaceused system stored procedure.