Create Tables in SQL Server with T-SQL

By: Gauri Mahajan   |   Updated: 2019-12-23   |   Comments   |   Related: > Database Design

Problem

Tables are one of the most used database objects and are used to store data in SQL Server databases. In my last tip I covered creating tables in SQL Server using SQL Server Management Studio Table Designer. In this tip, we will cover how to work with tables using T-SQL code.

Solution

Most of you may already know, T-SQL is Transact-SQL which is an extension of Structured Query Language (SQL). It is broadly used in all SQL Server databases and database objects like tables, functions, stored procedures, etc. One of the ways to run T-SQL statements is to connect to an instance of the SQL Server Database Engine and execute code in SQL Server Management Studio (SSMS).

Let's move forward and see how we can employ T-SQL to create tables in SQL Server.

Prerequisites

For a demonstration of this article, we need access to a SQL Server instance using SSMS. I am using SQL Server 2017 and SSMS v18.1, in case you don't have it, download SSMS from here.

Create Demo Database

Let's open SSMS and create a database named DemoDB (for demo purposes). You can use any existing database or you can simply create a new database to follow along. To create a new database, in SSMS right click on Databases, select New Database and enter your Database name.

Since, we are talking about T-SQL here, let's quickly create a database using a T-SQL statement, CREATE DATABASE. Execute the below command to create this database.

CREATE DATABASE DemoDB 

Now, let's consider an arbitrary case, where there is a requirement to monitor and track Grade 3 students data. As a layman, one would think of sorting and storing student-related information in different spreadsheets in Excel. For instance, personal details in one tab, departmental details in other and so on. This arrangement would work fine if there are not many changes in the personal data (like address, contact number, etc.) of students, but as you begin to get new admissions and also frequent updates in the existing data, it will cause redundancy leading to inefficiency. Also, you might have to put way more effort into maintaining data consistency and integrity. 

As a SQL developer, the first thought that would cross your mind is to create a table in a SQL Server database and store Grade 3 students data in it. You can further create tables to build relationships between them. Before creating a table, we need to go over certain factors like, what will be the table name, what columns will it have, what will be the data types and if the columns will contain null/not null values. From this brief discussion, let's quickly derive the basic syntax to create a table using T-SQL.

Basic simple syntax to create a table using T-SQL in SQL Server

CREATE TABLE database_name.schema_name.table_name
(
col1 datatype [NULL | NOT NULL],
col2 datatype [NULL | NOT NULL],
...
)

Here, the syntax uses the CREATE TABLE statement to create a new table with a specified existing schema and in the specified existing database name. This table name has to be unique and we can list the column names in the column definition along with its data type and indicate if the column will allow nulls or not.

CREATE TABLE examples using T-SQL

Let's understand and explore the above syntax with a few examples.

Note, I will be using the IF EXISTS clause to drop a table if it exists, this was introduced in SQL Server 2016. This tip shows the old and new syntax.

Execute the below query to create a table named, Grade3Students in the current database. Since we have not mentioned any schema name, it will be contained in the default schema dbo (dbo is the default, but a database user could have a different default schema). This table will store basic student information like StudentId, FirstName, LastName, DateOfBirth, Address, PhoneNumber and DepartmentId.

USE DemoDB
GO

-- drop the table if it already exists and you have permission to delete
-- be careful with this, there is not an undo feature with this command
DROP TABLE IF EXISTS Grade3Students

CREATE TABLE Grade3Students
(
StudentId    int          NOT NULL,
FirstName    varchar(20)  NOT NULL, 
LastName     varchar(20)  NOT NULL,
DateOfBirth  date         NOT NULL,
Address      varchar(30)  NULL,
PhoneNumber  nvarchar(10) NULL,
DepartmentId int          NOT NULL
)

After the execution, refresh the Databases folder and you will be able to see table dbo.Grade3Students in the database DemoDB as shown below.

Creating tables under a different schema and a different database

If the database name is not explicitly specified, it will create the table in the current database. To create a table in a different database and under a different schema, we have to specify the database name followed by the schema name and the table name.

Say, we intend to create a table in the existing AdventureWorksDW2017 database and under schema Grade3, see four part naming for more info.

Let's first create the schema Grade3 using the T-SQL CREATE SCHEMA statement and execute the following query to have a table created in the specified database and schema.

USE AdventureWorksDW2017
GO

CREATE SCHEMA Grade3;

DROP TABLE IF EXISTS AdventureWorksDW2017.Grade3.Grade3Students

CREATE TABLE AdventureWorksDW2017.Grade3.Grade3Students
(
StudentId int NOT NULL,
FirstName varchar(20) NOT NULL, 
LastName varchar(20) NOT NULL,
DateOfBirth date NOT NULL,
Address varchar(30) NULL,
PhoneNumber nvarchar(10) NULL,
DepartmentId int NOT NULL
)

After executing the above query, the below table is created in the AdventureWorksDW2017 database using schema Grade3.

Quick note – From now until the end of this tip, we will be working on the database DemoDB and default schema dbo. In case, you want to target any specific database and a schema, you can mention their names while creating the table.

Creating Keys for Tables

We just discussed the simple syntax of creating a table using T-SQL, however, in relational databases like SQL Server, we mostly deal with constraints like Primary, Foreign, Unique, etc. to maintain logical relationships between tables and to enforce referential integrity.

A Primary Key is a special column or a combination of columns that uniquely identify a row or a record in a table. We can create only one primary key constraint per table. We usually use the keyword 'PRIMARY KEY' after the primary key column name to define a column as a primary key column, and if the primary key is comprised of two or more columns, we can move this primary key constraint to the table_constraints section. If these terms are too much for you to absorb for now, don't worry, we will look at them in detail shortly.

Below is the general syntax of creating a table with a column as a primary key and other table constraints.

CREATE TABLE database_name.schema_name.table_name 
( 
Pk_Col datatype PRIMARY KEY, 
Col1   datatype [NULL | NOT NULL], 
Col2   datatype [NULL | NOT NULL], 
... 
table_constraints 
); 

Let's go ahead and work on the implementation with some examples using T-SQL.

Adding a Primary Key and Identity Column using T-SQL

It is pretty simple to set a column as a primary key and enable the identity property for it in a SQL table. In case, you are not aware of the identity property in a SQL table, it is basically a column whose value increments automatically with a given starting point and increment number. For information about Identity property in SQL Server, see here.

In the below syntax, to create StudentId as both a Primary Key constraint and an Identity column, we added keywords PRIMARY KEY and IDENTITY(1,1) to it. IDENTITY(1,1) means this column will start with Id 1 and increment the Id by 1 for a new record inserted. The primary key constraint column represents unique data, so it is a good idea to define it on an Identity column. In a nutshell, the StudentId column is an auto-incremented Primary Key in this table.

USE DemoDB
GO

DROP TABLE IF EXISTS Grade3Students

CREATE TABLE Grade3Students
(
StudentId    int PRIMARY KEY IDENTITY(1,1),
FirstName    varchar(20) NOT NULL, 
LastName     varchar(20) NOT NULL,
DateOfBirth  date NOT NULL,
Address      varchar(30) NULL,
PhoneNumber  nvarchar(10) NULL,
DepartmentId int NOT NULL
)

Highlight the table name, Grade3Students and use keyboard shortcut Alt + F1, to retrieve information about this table. It works the same way as this statement - sp_help Grade3Students. StudentId column has been identified as an Identity column and also set up as a Primary Key constraint, marked in red below. You can also see SQL Server has generated the constraint_name as pk_tablename_somerandomstring to keep it unique. By default, Database Engine creates a unique clustered index on the primary key column, if no particular index is specified.

At times, we have to refer to the name of the Primary Key constraint, especially in cases where we have to alter/drop this constraint. In such cases, it can be a hassle for you, other team members or DBA's to identify such complicated constraint names. 

There is a way to both create and specify the primary key constraint name at the same time. Let's rewrite the above query by simply placing the keyword CONSTRAINT after the primary key column definition and followed by constraint_name(pk_Grade3Students_StudentId, I have followed this convention here -pk_tablename_primarycolumnname) and PRIMARY KEY as shown below.

DROP TABLE IF EXISTS Grade3Students
CREATE TABLE Grade3Students
(
StudentId int IDENTITY(1,1) CONSTRAINT pk_Grade3Students_StudentId PRIMARY KEY,
FirstName varchar(20) NOT NULL, 
LastName varchar(20) NOT NULL,
DateOfBirth date NOT NULL,
Address varchar(30) NULL,
PhoneNumber nvarchar(10) NULL,
DepartmentId int NOT NULL
)

One more way to address the above query is by specifying the constraint name (pk_Grade3Students_StudentId) and the Primary key column (StudentId) at the end in the table constraints section as shown below.

DROP TABLE IF EXISTS Grade3Students
CREATE TABLE Grade3Students
(
StudentId int IDENTITY(1,1),
FirstName varchar(20) NOT NULL, 
LastName varchar(20) NOT NULL,
DateOfBirth date NOT NULL,
Address varchar(30) NULL,
PhoneNumber nvarchar(10) NULL,
DepartmentId int NOT NULL,
CONSTRAINT pk_Grade3Students_StudentId PRIMARY KEY(StudentId)
)

Options b) and c) have the upper side in the sense that it avoids system-generated constraint name, and creates a comprehendible primary key constraint name. Such practices help things be descriptive and clearer to understand especially when we have a team-based database development for the larger databases.

Enforcing Foreign Key relationships using T-SQL

One of the important concepts of relational databases is its referential integrity and Foreign Key (FK) constraint in SQL Server helps to enforce data integrity between tables. In simple words, FK is a column or a combination of columns in one table referencing the Primary Key of another table, thereby, it ensures that value in one table must be present in another table.

Let's try to understand this with the help of some simple scenarios:

The records in the table, Grade3Students we created above, can be associated with one or more records in other tables, say department they belong to, what courses they are enrolled in, extra-curricular activities they are participating in, and a lot more. Assuming, we have another table, named, Departments, each department can have many students enrolled in, leading to a one-to-many relationship.

We don't want to insert any record in the Grade3Students table that does not belong to any department. To ensure this data integrity, we will enforce an FK constraint in the CREATE Table statement.

To implement this, let's quickly create this master table, 'Departments' that contains only 2 columns, DepartmentId (Primary key) and DepartmentName. Below is the T-SQL syntax to create this table.

DROP TABLE IF EXISTS Departments

CREATE TABLE Departments
(
DepartmentId   int PRIMARY KEY,
DepartmentName varchar(20)
)

You can insert some sample data using the T-SQL INSERT INTO statement.

INSERT INTO Departments VALUES (1, 'Mechanical') 
INSERT INTO Departments VALUES (2, 'Chemical') 
INSERT INTO Departments VALUES (3, 'Electronic') 
INSERT INTO Departments VALUES (4, 'Textile') 
INSERT INTO Departments VALUES (5, 'Civil')

This is how this table looks like:

SELECT * FROM Departments

CREATE SQL Table with a Foreign Key reference

In order to enforce a relationship between these 2 tables, let's create a Foreign Key constraint. The following code creates the Grade3Students table, with the StudentId as a Primary Key and Identity column. The FK constraint references another table and links the DepartmentId in the Departments table to the DepartmentId in the Grade3Students table.

Also, FK (DepartmentId) in the Grade3Students table, can allow multiple instances of the same value. To dig in more about FK constraint, you can read this tip by one of my fellow authors and refer to this category as well.

DROP TABLE IF EXISTS Grade3Students
CREATE TABLE Grade3Students
(
StudentId int PRIMARY KEY IDENTITY(1,1),
FirstName varchar(20) NOT NULL, 
LastName varchar(20) NOT NULL,
DateOfBirth date NOT NULL,
Address varchar(30) NULL,
PhoneNumber nvarchar(10) NULL,
DepartmentId int NOT NULL,
FOREIGN KEY (DepartmentId) REFERENCES Departments (DepartmentId)
) 			

Alternatively, you can also create FK constraint with a specified 'FK constraint name' using the keyword CONSTRAINT fk_constraint_name as shown in the following query:

DROP TABLE IF EXISTS Grade3Students
CREATE TABLE Grade3Students
(
StudentId int PRIMARY KEY IDENTITY(1,1),
FirstName varchar(20) NOT NULL, 
LastName varchar(20) NOT NULL,
DateOfBirth date NOT NULL,
Address varchar(30) NULL,
PhoneNumber nvarchar(10) NULL,
DepartmentId int NOT NULL,
CONSTRAINT FK_Grade3Students_DepartmentId FOREIGN KEY (DepartmentId) REFERENCES Departments
(DepartmentId)
) 

Let's insert data into Grade3Students table using INSERT INTO statement.

INSERT INTO Grade3Students VALUES( 'Mark', 'Miller', '1980-05-31','Frank E Road', '5513478930', 1)
INSERT INTO Grade3Students VALUES( 'Joseph', 'Ruffalo', '1978-02-02','Chelliof Boulevard','5347893090', 1)
INSERT INTO Grade3Students VALUES( 'Tiffany', 'Paleo', '1982-07-29','300 Somerset Street', '2018978734', 2)
INSERT INTO Grade3Students VALUES( 'Jennifer', 'Lotus', '1977-01-21','Ferry Rogger Road', '3450982348', 3)
INSERT INTO Grade3Students VALUES( 'Shermus', 'Shroff', '1988-10-10','Angelio Clifflio Boulevard', '7719838970', 4)
INSERT INTO Grade3Students VALUES( 'Kelly', 'Vercher', '1975-01-01','Woodbridge lane', '7719838970',4 )

Execute the below query to see data inserted into table, Grade3Students. You can notice, multiple rows of the same FK make it a one-to-many relationship.

SELECT * FROM Grade3Students

Here is the summary of PK and FK in these tables:

Once the above create SQL table query is executed, the following is the PK and FK relationship established between SQL tables.

With this relationship being established, the SQL engine will not allow values to be inserted in the Foreign Key table (Grade3Students), if it does not exist in the Primary Key table (Departments). Let's say we try to insert the below row, with DepartmentId = 6, which is not available in the master Departments table,

INSERT INTO Grade3Students VALUES('John', 'Davious', '1969-09-10','Model Town', '7719838970',6) 

It throws the below error stating Foreign Key constraint conflicted in the table Departments, and column DepartmentId. To avoid this error, add a record first with DepartmentId = 6 into the Departments table.

Summary

We learned to create tables using T-SQL in this tip and also explored various important properties associated while creating these tables in SQL Server.

Next Steps

• Try working on the syntax and examples mentioned above to have a good grip on creating tables using T-SQL in SQL Server.
• To continue your learning on several SQL Server Database Design concepts, check out these tips.

About the author

Gauri Mahajan is very passionate about SQL Server Reporting Services, R, Python, Power BI, the Database engine, etc. and enjoys writing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips