In this article, we are going to learn about the CREATE TABLE statement. This article consolidates the various scenarios. I have covered the following scenarios.

1. How to create a table in a specific schema of the database
2. How to create a global and local temporary table
3. How to create a table with constraints
4. How to create a table in a different filegroup
5. How to create a system-version temporal table

The CREATE TABLE statement is used to create a new table in a database. The syntax of CREATE TABLE statement is as follows:

In the syntax,

1. sch_name: Specify the name of the schema in which you want to create a table. You can read this article to learn more about SQL Server Schema.
2. tbl_name: Specify the desired table name.
3. col_name: Specify the column name that you want to add to a table.
4. Datatype: Specify the datatype of the column.
5. length: Specify the length of the column.
6. contrsint_Name: Specify the constraint name.
7. filegroupName: Specify the filegroup name in which you want to create the table. You can read this article to learn more about SQL Server filegroups.

Let us understand various use cases to create a table in a database.

Create a table in a specific schema

To create table in specific schema, we must use two-part name. The schema in which you are creating the table must exists in the database. For example, you want to create a table named tblPatient in Patients schema, the Create table definition will be as following:

Screenshot

The above command creates a table named tblPatient in Patients schema.

Create a table with constraints

Now, let us understand how we can create a table with a constraint. A table can have the following constraints.

1. NOT NULL: This constraint ensures that a query does not insert a NULL value in a column on which the constraint has been created.
2. UNIQUE: This constraint ensures that the query inserts only unique values in the column on which the constraint has been created.
3. CHECK: When we insert a record in a table, the check constraint ensures that the values that we are inserting in a column must satisfy the condition specified in a CHECK constraint.
4. DEFAULT: The DEFAULT constraint inserts a default value. If we do not specify the values for the column on which the default constraint, it inserts the default value specified in the DEFAULT constraints.
5. PRIMARY KEY: The Primary key constraint is used to identify each record uniquely. Following are the characteristics of the Primary key
   1. The primary key consists of one or more columns.
   2. The primary key contains unique values and cannot contain a NULL value.
   3. We can create one primary key in a table.
   4. When we create a primary key, a clustered index will be created automatically.
6. FOREIGN KEY: The foreign key is a column that creates a link between two tables. The foreign key of any table refers to the Primary Key of another table. A table can have one or more foreign keys. A foreign key constraint prevents the UPDATE and DELETE statement that breaks the link between two tables.

Now, let us create a table named tblPatient with NOT NULL constraint.

Now, run the following query to insert a record with a NULL value.

Query output

Msg 515, Level 16, State 2, Line 12
Cannot insert the value NULL into column ‘Patient_code’, table ‘VSDatabase.dbo.tblPatient’;
column does not allow nulls. INSERT fails.

Screenshot

As you can see, the query has returned an error.

Create a table in the secondary filegroup

To create a table in the secondary filegroup, we must specify the filegroup name in the CREATE TABLE statement. Make sure that the filegroup in which you are creating a table must exist on the database. Creating a table in a secondary filegroup is useful when you want to keep your frequently accessed tables on a separate disk.

Suppose we want to create table tblPatient in a secondary filegroup named FG_Patient. The CREATE TABLE statement is as following:

Screenshot

The table tblpatient has been created. Now, to view the tables created in the FG_Patient filegroup, run the following query.

Query Output

As you can see, the tblpatient has been created in the FG_Patients filegroup.

Create a temporary table

The temporary tables are session-specific tables and are used to store the data temporarily. It is good practice to create and store the desired dataset temporary table and perform complex operations when performing complex string or arithmetic functions. The Temp tables are created in the TempDB database. There are two types of the temporary tables

1. Local temp tables
2. Global Temp tables.

The syntax of the regular table and temp tables are the same. The only difference is the prefix that we used before the table name. To create the local temp table, we must use # before the name of the table. To create a global temp table, we must use ## before the name of the table. For example, we want to create a local temp table named #tblpatient, the CREATE TABLE statement written as follows.

If you want to create a global temp table named ##tblpatient, the CREATE TABLE statement is written as follows:

To view the temp tables, run the following query.

Query output

Create a SYSTEM VERSION temporal table

The system version temporal tables were introduced in SQL Server 2016. These tables are special types of tables used to keep the history of the data modification. You can use them to analyse the data at a specific point in time analysis.

When we create a temporal table, we must specify two-period columns with datetime2 datatype. When the data changes occur in the table, the system records the datetime of the data change in the period tables. Along with the period columns, we must specify the history table, which stores the information about the data change.

The syntax to create a temporal table named tblPatient is the following:

In the syntax, frm_date and to_date are the period columns. The datatype of frm_date and to_date is datetime2. The data changes will be recorded in the tblPatient_History table. Now, let us insert some dummy data to analyse the behaviour.

Once records are inserted, run the SELECT query on the tblPatient table.

Output

Run the following query on the history table.

Output

As you can see, the records are inserted in tblPatient with date and time. No records are inserted in the tblPatient_history table. Now, run an UPDATE query to change the data of a specific patient.

Query

Run select query on tblPatient table.

Output

After updating the data, the date and time of the period columns are changed. Let us look at the history table. Run the SELECT query on the tblPatient_History table.

Query

Query Output

As you can see, the old values of the tblPatient have been inserted in the history table.

Sometimes, the temporal tables can be used to revert the changes made by an UPDATE or DELETE query. I will write a separate article on it.

Summary

This article taught us about the CREATE TABLE statement in SQL Server. We have learned various use cases that can be used to create a table. We learned:

• How to create a table in a specific schema of the database.
• How to create a global and local temporary table.
• How to create a table with constraints.
• How to create a table in a different filegroup.
• How to create a system-version temporal table.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022