SQL SERVER - How To Create a Table

In our Previous articles, We have already covered creating a table using Table Designer of SQL Server Management Studio. Now we will see how to create and alter the table using in SQL Server Text editor.

At first, we will see the syntax to Create Table statement.

Syntax :

CREATE TABLE
[ database_name . [schema_name] . | schema_name .] table_name ( { <column_definition> | } )

<column_definition> ::=
column_name <data_type>
    [NULL | NOT NULL]    |                                           [IDENTITY [(seed ,increment) ]]                                                     [ <column_constraint>  ]

<data type> ::=
[ type_schema_name . ] type_name
    [ ( precision [ , scale ] | max | ]

<column_constraint> ::=                                                                  [ CONSTRAINT constraint_name ]
{    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]        
         | [ FOREIGN KEY ]
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]         
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}

Database_name : Name of the existing database in which the table is created.

Schema_name : Name of the schema to which the new table belongs.

Table_name : Name of the new table.table_name can be a maximum of 128 characters

Column_name : Name of a column in the table. Column names must be unique in the table and can be up to 128 characters.

 

[type_schema_name.] type_name : Specifies the data type of the column, and the schema to which it belongs.

 

Precision : Is the precision for the specified data type.

Scale  : Is the scale for the specified data type.

max :   Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31 bytes of character and binary data, and 2^30 bytes of Unicode data.

 

Identity: Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column.  which serves as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

Seed  : Is the value used for the very first row loaded into the table.

Increment : Is the incremental value added to the identity value of the previous row loaded.

Constraint: Is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

Constraint_name  : Is the name of a constraint. Constraint names must be unique within the schema to which the table belongs.

Null | Not Null : Determine whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL. NOT NULL can be specified for computed columns only if PERSISTED is also specified.

Primary Key : Is a constraint that enforces entity integrity for a specified column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

Unique : Is a constraint that provides entity integrity for a specified column or columns through a unique index. A table can have multiple UNIQUE constraints.

Clustered | Nonclustered : Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.  In a CREATE TABLE statement, CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.

Foreign Key References: Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table. Foreign keys on computed columns must also be marked PERSISTED.

[ schema_name.] referenced_table_name]  : Is the name of the table referenced by the FOREIGN KEY constraint, and the schema to which it belongs.

(ref_column) : Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.      

                      

Check : Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. CHECK constraints on computed columns must also be marked PERSISTED.

Example :

Create Table [dbo].[Orders]
(

[OrderId] Int NOT NULL                                                          CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED ([OrderId]), 

[ProductId] Varchar(10) NOT NULL                                           REFERENCES Product(ProductID),

[OrderDate] [Datetime] NOT NULL                                         CONSTRAINT [Def_OrderDate]  DEFAULT (getdate()),

[Qty] Int

)