CREATE TABLE

Transact-SQL Syntax (Transact-SQL)

CREATE TABLE [database_name.[schema_name].|schema_name.]table_name (
    { <column_definition> | <computed_column_definition> }
    [ ,...n ]
)
[ <table_constraints> ]
[ ; ]

-- Where <column_definition> is:
column_name data_type
    [ | identity [ ( seed , increment ) ] [ ( NOT FOR REPLICATION ) ]
    [ | generated always as ( <expression> ) [ persisted [ [ with compensation ] ] ]
    [ | sparse ]
    [ column_collation ]
    [ null | not null ]
    [ | default { constant_expression | NULL | GETDATE() | NEWID() | SUSER_SNAME() }
        [ with values ]
    ]
    [ rowguidcol ]
    [ | constraint constraint_name
        rowguidcol
    ]
    [ | constraint constraint_name
        unique []
    ]
    [ | constraint constraint_name
        primary key [ CLUSTERED | nonclustered ] [ ( column [ ,...n ] ) ]
    ]
    [ | constraint constraint_name
        unique [ CLUSTERED | nonclustered ] [ ( column [ ,...n ] ) ]
    ]
    [ | constraint constraint_name
        foreign key
        [ references referenced_table
            [ ( referenced_column [ ,...n ] ) ]
        ]
        [ on delete  { no action | cascade | set null | set default } ]
        [ on update  { no action | cascade | set null | set default } ]
    ]
    [ | constraint constraint_name
        check ( logical_expression )
    ]
    [ | constraint constraint_name
        default { constant_expression | NULL }
        for column_name
    ]
    [ | constraint constraint_name
        timestamp
    ]
-- Where <table_constraints> is:
[ CONSTRAINT constraint_name ]
    {  { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        ( column [ ,...n ] )
    }
|
    { FOREIGN KEY [ references referenced_table [ ( referenced_column [ ,...n ] ) ] }
    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
|
    { CHECK ( logical_expression ) }
|
    { DEFAULT [ ( constant_expression | NULL ) ]
        FOR column }
)
[ INCLUDE ( column [ ,...n ] ) ]
[ WITH ( table_option [ ,...n ] ) ]
Note: For a complete list of Transact-SQL syntax, see Transact-SQL Syntax Conventions.

Description

The CREATE TABLE statement is used to create a new table in the database. A table consists of rows and columns. Each column has a name and a data type.

Parameters

  • database_name: The name of the database in which to create the table.
  • schema_name: The schema to which the new table belongs.
  • table_name: The name of the new table. Table names must follow the rules for identifiers.
  • column_definition: Defines the name, data type, and constraints for a column.
  • data_type: The data type of the column (e.g., INT, VARCHAR(50), DATE).
  • identity: Specifies that a column is an identity column.
  • sparse: Specifies that a column is sparse.
  • column_collation: The collation for the column.
  • null | not null: Specifies whether the column can contain NULL values.
  • default: Specifies a default value for the column.
  • constraint_name: The name of the constraint.
  • primary key: Defines a primary key constraint.
  • unique: Defines a unique constraint.
  • foreign key: Defines a foreign key constraint.
  • check: Defines a check constraint.
  • timestamp: A binary number that guarantees uniqueness within a table.
  • table_constraints: Defines constraints for the table.

Remarks

  • If schema_name is not specified, the default schema of the current user is used.
  • If database_name is not specified, the current database is used.
  • A table must have at least one column.
  • Constraints can be defined at the column level or table level.

Examples

A. Create a simple table

The following example creates a simple table named Product with three columns:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2)
);

B. Create a table with an identity column and a foreign key

The following example creates a Customer table and an Order table, with a foreign key relationship:

CREATE TABLE Customer (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);

CREATE TABLE OrderHeader (
    OrderID INT IDENTITY(1000,1) PRIMARY KEY,
    OrderDate DATETIME NOT NULL DEFAULT GETDATE(),
    CustomerID INT,
    CONSTRAINT FK_OrderHeader_Customer
        FOREIGN KEY (CustomerID)
        REFERENCES Customer (CustomerID)
        ON DELETE CASCADE
);
For information about creating tables in Azure SQL Database, see CREATE TABLE (Azure SQL Database).