SQL Server CREATE TABLE

Learning Objective

In this tutorial, you will learn how to create a new table in SQL Server using CREATE TABLE statement in SQL Server.

SQL Server CREATE TABLE Introduction

We all know the importance of tables in any database. The table holds the data for business. The name of the table is unique in a schema in the database. A database can hold multiple tables with the same name but in different schemas. Each table holds one or more columns and every column is associated with a data type that defines the kind of data it can store e.g. INT, char, strings, etc.

CREATE TABLE statement allows you to create a new table in SQL Server database.

SQL Server CREATE TABLE Syntax

Following is the syntax of CREATE TABLE syntax in SQL Server database.

CREATE TABLE [database_name.][schema_name.]table_name
(
   pk_column data_type PRIMARY KEY,
   column_1 data_type column_constraint,
   column_2 data_type,
   …..
   table_constraints
);

In this syntax,

• CREATE TABLE – The keyword to create a new table in the SQL Server database.
• database_name – Defines the name of the database where you want to create the table.
• schema_name – Defines the schema name to which the new table belongs.
• table_name – The name of the new table you want to create.
• pk_column – Each table has one primary key which consists of one or more columns. Generally, primary key columns are listed first followed by other table columns. If your table contains only one column as the primary key, then you can specify it after mentioning the data_type of the column by appending the PRIMARY KEY keyword at the end. In case your table has a primary key consisting of more than one column, you have mentioned it as table_constraint at the end of the column definition.
• column_constraint – A cloumn can have one or more constraints such as NOT NULL, IDENTITY, and UNIQUE.
• table_constraint – A table can have one or more constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

SQL Server CREATE TABLE Example

The following statement create a new table named employees under company schema.

CREATE TABLE company.employees
(
   emp_id INT PRIMARY KEY IDENTITY (1,1),
   first_name nVARCHAR(50) NOT NULL,
   last_name nVARCHAR(50) NOT NULL,
   dob DATE NOT NULL,
   join_date DATE NOT NULL,
   phone nVARCHAR(20),
   dept_id INT NOT NULL,
   FOREIGN KEY (dept_id) REFERENCES company.department (dept_id)
);

In the above example, we have not specified any database name. So in which database it will be created?

The table employees is created in the dbo database and we have mentioned the schema name explicitly, so it created in the company schema.

The table employees contains seven columns. Now let’s try to understand each column in great details.

1. emp_id – The emp_id column is the primary key column of the table. The IDENTITY(1,1) defines a identity column, here this keyword instructs SQL Server to create values for the column
   automatically e.g. starting from the integer one and increment the value by one for each row.
2. first_name & last_name – The column first_name and last_name defines with nVARCHAR(50) to hold the character values up to 50. Also NOT NULL in these instructs SQL Server not to accept any NULL values for these columns.
3. dob & join_date – The column dob and join_date define with DATE datatype which holds the date of birth and joining date of the employee in DATE format.
4. phone – The phone column is a varying character column that holds data up to 50 characters and can contain NULL.
5. dept_id – The dept_id contains integer number, but can not holds NULL values.
6. FOREIGN_KEY – In the end, we have defined one FOREIGN KEY table constraint. This foreign key constraint ensures that the dept_id value which inserts into the employees table must be present in the company.department table.

Summary

In this tutorial, you have learned how to create a new table in SQL Server using the CREATE TABLE statement in SQL Server.