CREATE TABLE in MS SQL Server

In Microsoft SQL Server, there are different ways in which we can create the table. The table can be created with or without constraints.

Basic Syntax for creating a table is,

CREATE TABLE databaseName.tableName(  
   columnName1 datatype [NULL | NOT NULL], 
   columnName2 datatype [NULL | NOT NULL] 
);

databaseName – Name of the DB where the table will be created

tableName – Name of the table. 

columnName – Name of the column or field. 

datatype – Type of the data to be stored in the column. 

The column can be either null or non-null. But it is not mandatory to specify the nullability. It is purely optional to define the nullability of the column. If it is not specified, it is considered as NULL by default. Even though it is not mandatory, it is always a good practice to specify that.

Now that we have seen the basic syntax of create statement, we will see the different types of it with examples.

Create table with Primary Key

Syntax to create a table with primary key constraint

CREATE TABLE dbo.tblCountry 
( 
	ID INT NOT NULL, 
	countryName VARCHAR(50) NOT NULL, 
	countryCode CHAR(3) NOT NULL, 
	CONSTRAINT PK_tblCountry PRIMARY KEY (ID) 
);

PK_tblCountry – Name of the primary key of the tblCountry table.

Table with Identity Column

Syntax to create with an identity column

CREATE TABLE dbo.tblCountry 
( 
	ID INT NOT NULL IDENTITY(1,1), 
	countryName VARCHAR(50) NOT NULL, 
	countryCode CHAR(3) NOT NULL, 
	CONSTRAINT PK_tblCountry PRIMARY KEY (ID)
);

IDENTITY(1,1) – the First parameter is the seed value and the second parameter is the increment value.

Create table with Foreign Key

Syntax to create with foreign key constraint 

CREATE TABLE dbo.tblPlayer 
( 
	playerID INT NOT NULL IDENTITY(1,1), 
	firstName VARCHAR(100) NOT NULL, 
	age INT NOT NULL, 
	countryID INT NOT NULL, 
	createdDate DATETIME NOT NULL, 
	CONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), 
	CONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) 
);

FK_tblPlayer_tblCountry_CountryID – Name of the foreign key constraint. 

Table with Default constraint

Syntax to create with default constraint

CREATE TABLE dbo.tblPlayer 
( 
	playerID INT NOT NULL IDENTITY(1,1), 
	firstName VARCHAR(100) NOT NULL, 
	age INT NOT NULL, 
	countryID INT NOT NULL, 
	createdDate DATETIME NOT NULL CONSTRAINT DF_tblPlayer_createdDate DEFAULT GETDATE(), 
	CONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), 
	CONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) 
);

DF_tblPlayer_CreatedDate – Name of the default constraint. 

Table with Check constraint

Syntax to create with a check constraint

CREATE TABLE dbo.tblPlayer 
( 
	playerID INT NOT NULL IDENTITY(1,1), 
	firstName VARCHAR(100) NOT NULL, 
	age INT NOT NULL CONSTRAINT CK_tblPlayer_age CHECK (age >= 15), 
	countryID INT NOT NULL, 
	createdDate DATETIME NOT NULL CONSTRAINT DF_tblPlayer_createdDate DEFAULT GETDATE(), 
	CONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), 
	CONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) 
);

CK_tblPlayer_Age – Name of the check constraint. 

In the above examples, the constraints were created with meaningful names. Please refer to the below screenshot.

Syntax to create with SQL server-generated constraint names. It same as the above technique but the difference is that the name of the constraint will be chosen by SQL Server itself.

CREATE TABLE dbo.tblPlayer 
( 
	playerID INT NOT NULL IDENTITY(1,1), 
	firstName VARCHAR(100) NOT NULL, 
	age INT NOT NULL CHECK (age >= 15), 
	countryID INT NOT NULL, 
	createdDate DATETIME NOT NULL DEFAULT GETDATE(), 
	PRIMARY KEY (playerID), 
	FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) 
);

Check the constraint names generated by SQL server in the below table

Also, check “CREATE TABLE using SELECT in MS SQL Server“

Reference – Microsoft SQL Server 2017