Create a Table in Microsoft SQL Server

By:   |   Updated: 2022-07-28   |   Comments (1)   |   Related: More > TSQL


Problem

In a previous tip, we worked through the basic steps of creating a database in SQL Server. The database cannot be used effectively on its own and tables are a very important second step in the process, because that is where information is kept. In this SQL tutorial, we will start with the basics of creating a table (syntax, column definition, data types, constraints, etc.) and build upon that to more advanced steps when creating a table.

Solution

We will create some test databases and then create tables in the databases. Starting with a simple example and building upon the examples.

SQL CREATE TABLE Statement

In the examples below we cover various aspects when creating a new table:

  1. Create a single integer column table
  2. Create a single varchar column table
  3. Create a single table that holds only names
  4. Create a table with an ID column
  5. Create a table and make the ID column the primary key constraint
  6. Create a table, make the ID column the primary key constraint and specify the ID column as an identity
  7. Create a table, make the ID column the primary key constraint, specify ID column as an identity and give the primary key a meaningful name

To begin with, we will create a SQL database for testing with the following syntax:

-- Set up a test environment by creating a TestDB USE master; 
GO 
  
DROP DATABASE IF EXISTS TestDB; 
GO 
  
CREATE DATABASE TestDB; 
GO 
  
USE TestDB; 
GO 

1 - Creating a single integer column table

--  Create a very basic table with 1 integer Column 
CREATE TABLE IntTable (intColumn int)
 
-- insert some data with comma separated values to create new rows
INSERT INTO IntTable (intColumn) 
VALUES (1), (2), (3), (4), (5) 

-- select data from the existing table
SELECT * FROM IntTable 

-- get details about the table
EXEC SP_HELP IntTable 

2 - Creating a single varchar column table for a character string

--  Create a very basic table with 1 character Column
CREATE TABLE VarTable (varColumn varchar(10))

-- insert some data with comma separated values to create new rows
INSERT INTO VarTable (varColumn) 
VALUES ('a'), ('b'), ('c'), ('d'), ('e')  ('c'), ('d'), ('e') 

-- select data from the existing table
SELECT * FROM VarTable 

-- get details about the table
EXEC SP_HELP VarTable 

3 - Creating a single table that holds only names

--  Create a very basic table with 1 character Column that holds names 
CREATE TABLE NamesTable (Name varchar(10))

-- insert some data with comma separated values to create new rows
INSERT INTO NamesTable (Names) 
VALUES ('John'), ('Mary'), ('Shaun'), ('Barry'), ('Gary')  ('Barry'), ('Gary') 

-- select data from the existing table
SELECT * FROM NamesTable 

-- get details about the table
EXEC SP_HELP NamesTable 

I have placed 3 query windows next to each other and ran the 3 SQL scripts (above) one in each query window. In the top part, you can see the contents of the table, the SELECT * FROM <table>. Take note of each of the 6 parts below as we will be discussing these further in this tutorial.

Apologies for the small image, but you can replicate it by using 3 query windows next to each other and executing the above queries.

query results

4 - Create a table with an ID column

-- Create a table with an ID column 
DROP TABLE IF EXISTS PrimKeyTable 

CREATE TABLE PrimKeyTable (ID int) 

EXEC sp_help PrimKeyTable; 

5 - Make the ID column the Primary Key

-- Make the ID column the Primary Key 
DROP TABLE IF EXISTS PrimKeyTable 

CREATE TABLE PrimKeyTable (ID int primary key) 

exec sp_help PrimKeyTable; 

6 - Specify the ID column to be an Identity to auto-increment the value

-- Specify the ID column to be Identity DROP TABLE IF EXISTS PrimKeyTable 

CREATE TABLE PrimKeyTable (ID int primary key identity) 

exec sp_help PrimKeyTable; 

7 - Give the Primary Key a meaningful name

-- Give the Primary Key a meaningful name 
DROP TABLE IF EXISTS PrimKeyTable 

CREATE TABLE PrimKeyTable (ID int constraint PK_PrimKeyTable primary key identity) 

exec sp_help PrimKeyTable 

This time I placed 4 query windows next to each other and ran the above SQL scripts in each window.

Take a closer look at the parts highlighted in yellow:

  • The identity, see the difference between the 4 result parts
  • The index_name, see the name when you do not give a name vs when you give it a name
  • The constraint_type, see the constraint_name when the primary is not name provided and when a name is provided

Again, apologies for the small image, but just put 4 query windows next to each other and execute the above four queries.

query results

Clean up by dropping the TestDB database.

USE master; 
GO 
  
-- Drop Database if it exist 
DROP DATABASE IF EXISTS TestDB; 
GO 

HR Database and Table

Now we will look at some more complete table examples. In this section, we will create a Human Resources database that we will be expanding on in future tutorials.

First, let us create the database and then a table that will hold the basic information about a Company: name, address, contact number, and email address.

-- Create the HRDatabase 
USE master 
GO 
  
DROP DATABASE IF EXISTS HRDatabase 
GO 
  
CREATE DATABASE HRDatabase 
GO 
  
USE HRDatabase 
GO 
  
-- Create a table that holds the information about a Company, Address, 
-- its Contact Number and Email Address 
DROP TABLE IF EXISTS Companies 
CREATE TABLE Companies ( 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80) 
) 

INSERT INTO Companies 
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com'              ), 
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com'              ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria', '091 523 1235' , 'charlie@someaddress.com'), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com'              ), 
('Echo Company'  , '100 Amber Street, Hatfield, Pretoria', '091 523 9685' , 'echo@someaddress.com'              ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

The result set from the select above will show the information below.

query results

Also, see the output of sp_help on the table. Take note of the Column_name and the Identity section highlighted in yellow in the image below. We can see the columns that were created and that we are not using an Identity.

query results

Add an ID Column to Companies Table

To help differentiate the records by using just one column, we will add an ID column as an integer column, so we can assign a value of 1,2,3, etc. to each company.

-- Add an ID column 
  
DROP TABLE IF EXISTS Companies; 

CREATE TABLE Companies ( 
 ID            INT, 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80) 
) 

INSERT INTO Companies (ID, CompanyName, CompAddress, CompContactNo, CompEmail) 
VALUES (1, 'Alpha Company'  , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' ), 
(2, 'Bravo Company'  , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com' ), 
(3, 'Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' ), 
(4, 'Delta Company'  , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com' ), 
(5, 'Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

When looking at the output from sp_help, the only difference is the addition of the integer column with the name ID.

query results

Make the ID column a Primary Key on Companies Table

The next set of code will show how we can make the ID column the Primary Key. This is important so the data in this column is a UNIQUE value for each row.

-- Make the ID column a Primary Key 
DROP TABLE IF EXISTS Companies; 

CREATE TABLE Companies ( 
 ID            INT PRIMARY KEY, 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80) 
) 

INSERT INTO Companies 
VALUES (1, 'Alpha Company'  , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com'  ), 
(2, 'Bravo Company'  , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com'  ), 
(3, 'Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' ), 
(4, 'Delta Company'  , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com' ), 
(5, 'Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

When you make the ID column the PRIMARY KEY, an index is created and SQL Server generates a name for the index which is PK__Companie__3214EC272796CE10 in our example.

Take note of the index_description and the index_keys in the image below.

query results

Make the ID column an identity column and the primary key on Companies Table

An identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted. You can specify the start number and increment of the identity as follows: identity(startValue, increment). So, if we use (1,5), it will start with a value of 1 and increment the value by 5 for the next record. The default is (1,1). Below we will start at 101 and increment by 100.

-- Make the ID column an identity column 
DROP TABLE IF EXISTS Companies; 
GO 
  
CREATE TABLE Companies ( 
 ID            INT PRIMARY KEY IDENTITY (101,100), 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80) 
)
 
INSERT INTO Companies 
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' ), 
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com' ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' ), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com' ), 
('Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

Below we can see the ID column values and also info about the identity column. You can see that when inserting new records, the ID column starts with 101 and increments by 100, so the next record has an ID of 201, and so on.

query results

Add a CreateDate column with default date-time stamp on Companies Table

Let's continue to build upon our examples, by adding a date-time stamp when a record is inserted. To do this, we will create a constraint and set the default to getdate().

Constraints are used to specify rules for data in a table and can limit the type of data that can go into a table. This ensures the accuracy and reliability of the data. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

-- Add a CreateDate column with default date-time stamp 
DROP TABLE IF EXISTS Companies; 
GO 
  
CREATE TABLE Companies ( 
 ID            INT PRIMARY KEY IDENTITY, 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80), 
 CreateDate    DATETIME CONSTRAINT DF_CreateDate_Companies DEFAULT (getdate()) 
) 

INSERT INTO Companies (CompanyName, CompAddress, CompContactNo, CompEmail)
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com'  ), 
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com'  ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' ), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com'  ), 
('Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

Take note of the CreateDate column and its definition above and the additional constraint in the image below.

query results

Add a true-false column to Companies Table

Another useful field to add is to determine if a record is active or not. We will create the column IsActive and if it is 1 it is active and if it is 0 it is not active.

-- Add a true-false column 
DROP TABLE IF EXISTS Companies; 
GO 
  
CREATE TABLE Companies ( 
 ID            INT PRIMARY KEY IDENTITY (101,100), 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80), 
 IsActive      BIT, 
 CreateDate    DATETIME  CONSTRAINT DF_CreateDate_Companies DEFAULT (getdate()) 
)
 
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo, CompEmail, IsActive)
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' , 1), 
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com' , 1), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' , 0), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com'  , 1), 
('Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' , 1) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

In the above insert statement, the IsActive column values were specified with either a 0 (= No) or a 1 (= Yes).

query results

Create the IsActive (true-false) column with a default constraint on Companies Table

When creating the IsActive with a default constraint, you do not have to specify the IsActive in the insert column when inserting a record, the default value will be used.

-- Create the IsActive column with a default constraint of 1 
DROP TABLE IF EXISTS Companies; 
GO 
  
CREATE TABLE Companies ( 
 ID            INT PRIMARY KEY IDENTITY, 
 CompanyName   VARCHAR(80), 
 CompAddress   VARCHAR(80), 
 CompContactNo VARCHAR(20), 
 CompEmail     VARCHAR(80), 
 IsActive      BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1), 
 CreateDate    DATETIME CONSTRAINT DF_CreateDate_Companies DEFAULT (getdate()) 
) 

INSERT INTO Companies (CompanyName, CompAddress, CompContactNo, CompEmail)
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' ), 
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , 'bravo@someaddress.com' ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com' ), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , '091 523 7414' , 'delta@someaddress.com' ), 
('Echo Company'  , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , 'echo@someaddress.com' ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

When defining the IsActive column in the table above, the default value for IsActive will be set to 1 (= Yes). When inserting records, no value was specified for the IsActive column and as you can see in the image below, all records were set to IsActive = 1 (= Yes). Take note of the new default constraint that was created in the bottom part of the image above.

query results

Columns with NULLs or NOT on Companies Table

Another thing you can do is tell the database whether NULL values can be stored or if a column requires a value. In the below code, we can see we have added "null" and "not null" next to each column. So for this table, the only column that requires data is the CompanyName.

-- Columns with NULLs or NOT 
DROP TABLE IF EXISTS Companies; 
GO 
  
CREATE TABLE Companies ( 
 ID            INT not null PRIMARY KEY IDENTITY (101,100), 
 CompanyName   VARCHAR(80) not null, 
 CompAddress   VARCHAR(80) null, 
 CompContactNo VARCHAR(20) null, 
 CompEmail     VARCHAR(80) null, 
 IsActive      BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1), 
 CreateDate    DATETIME CONSTRAINT DF_CreateDate_Companies DEFAULT (getdate()) 
) 

INSERT INTO Companies (CompanyName, CompAddress, CompContactNo, CompEmail, IsActive)
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' , 1 ), 
('Bravo Company' , null , '091 523 4789' , 'bravo@someaddress.com' , 1 ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com', null ), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , null , 'delta@someaddress.com' , 1 ), 
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , null , 0 ) 
  
SELECT * FROM Companies 
  
EXEC sp_help Companies 

Notice the records with NULL values in the image below. Also, take note that you can create a NULL constraint on a column and it can take a NULL when inserting a record like with the IsActive column.

query results

Schemas in a Database

In a database, a schema is a way to create a logical group of objects. By default, the schema is "dbo" unless otherwise specified, so all objects would be in this grouping. We can create additional schemas such as HR, Sales, etc. to help group objects.

SQL Server comes with these predefined schemas:

  • dbo
  • guest
  • sys
  • INFORMATION_SCHEMA

In SSMS, when you expand the tree on the database HRDatabase and then expand Tables, you should see the Companies table with a "dbo." prefix. The "dbo." is a schema and because the default schema is dbo, the Companies table is in the "dbo." schema when you create it without specifying the schema.

To create a table with a user-defined schema, you have to create the schema first, then you can create the table in the new schema.

The below code creates a schema named "hr" and then creates the table in that new schema, by specifying CREATE TABLE hr.Companies.

DROP TABLE IF EXISTS hr.Companies; GO 
  
CREATE SCHEMA hr; 
GO 
  
SELECT * FROM sys.schemas; 
  
CREATE TABLE hr.Companies ( 
 ID             INT not null PRIMARY KEY IDENTITY (101,100), 
 CompanyName    VARCHAR(80) not null, 
 CompAddress    VARCHAR(80) null, 
 CompContactNo  VARCHAR(20) null, 
 CompEmail      VARCHAR(80) null, 
 IsActive       BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1), 
 CreateDate     DATETIME CONSTRAINT DF_CreateDate_Companies DEFAULT (getdate()) 
) 

INSERT INTO hr.Companies (CompanyName, CompAddress, CompContactNo, CompEmail, IsActive)
VALUES ('Alpha Company' , '123 North Street, Garsfontein, Pretoria' , '091 523 6987' , 'alpha@someaddress.com' , 1 ), 
('Bravo Company' , null , '091 523 4789' , 'bravo@someaddress.com' , 1 ), 
('Charlie Company' , '987 West Street,  Lynnwood, Pretoria' , '091 523 1235' , 'charlie@someaddress.com', null ), 
('Delta Company' , '258 East Street,  The Meadows, Pretoria' , null , 'delta@someaddress.com' , 1 ), 
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , null , 0 ) 
  
SELECT * FROM hr.Companies 
  
DROP TABLE IF EXISTS hr.Companies; 
GO 
  
DROP SCHEMA hr; 
GO

Below is the output from querying sys.schemas.

query results

Clean Up

Clean up by dropping the current database - HRDatabase.

USE master; 
GO 
  
-- Drop Database if it exists 
DROP DATABASE IF EXISTS HRDatabase; 
GO 
Next Steps

In the next article, we will take a look at how to create more tables and how to link tables together.




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Jan Potgieter Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.

View all my tips


Article Last Updated: 2022-07-28

Comments For This Article




Friday, July 29, 2022 - 12:44:05 AM - Phurba Back To Top (90317)
Great Article.
This is just an awesome blog that people can learn. Very good information. It's very informative and explained in clear, and simple words.














get free sql tips
agree to terms