SQL Server: CREATE TABLE Statement

In this post explains how to use the CREATE TABLE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let's look at an example of how to use the CREATE TABLE statement in SQL Server (Transact-SQL).

For example:

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY
);

This SQL Server CREATE TABLE example creates a table called employees which has 4 columns.

• The first column is called employee which is created as an INT datatype and can not contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The fourth column is called salary which is a MONEY datatype which can contain NULL values.

Now the only problem with this CREATE TABLE statement is that you have not defined a primary key for the table in SQL Server. We could modify this CREATE TABLE statement and define the employee_id as the primary key as follows:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY
);

By specifying the words, PRIMARY KEY, after the employee_id field, SQL Server will create employee_id as the primary key for the employees table.