By: Jan Potgieter | Updated: 2022-07-28 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | > Database Design
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:
- Create a single integer column table
- Create a single varchar column table
- Create a single table that holds only names
- Create a table with an ID column
- Create a table and make the ID column the primary key constraint
- Create a table, make the ID column the primary key constraint and specify the ID column as an identity
- 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.
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.
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' , '[email protected]' ),
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria', '091 523 1235' , '[email protected]'),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ),
('Echo Company' , '100 Amber Street, Hatfield, Pretoria', '091 523 9685' , '[email protected]' )
SELECT * FROM Companies
EXEC sp_help Companies
The result set from the select above will show the information below.
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.
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' , '[email protected]' ), (2, 'Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ), (3, 'Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' ), (4, 'Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ), (5, 'Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' ) 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.
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' , '[email protected]' ), (2, 'Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ), (3, 'Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' ), (4, 'Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ), (5, 'Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' ) 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.
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' , '[email protected]' ),
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' ),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ),
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' )
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.
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' , '[email protected]' ),
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' ),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ),
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' )
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.
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' , '[email protected]' , 1),
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' , 1),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' , 0),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' , 1),
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' , 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).
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' , '[email protected]' ),
('Bravo Company' , '456 South Street, Brooklyn, Pretoria' , '091 523 4789' , '[email protected]' ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]' ),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , '091 523 7414' , '[email protected]' ),
('Echo Company' , '100 Amber Street, Hatfield, Pretoria' , '091 523 9685' , '[email protected]' )
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.
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' , '[email protected]' , 1 ),
('Bravo Company' , null , '091 523 4789' , '[email protected]' , 1 ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]', null ),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , null , '[email protected]' , 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.
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' , '[email protected]' , 1 ),
('Bravo Company' , null , '091 523 4789' , '[email protected]' , 1 ),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria' , '091 523 1235' , '[email protected]', null ),
('Delta Company' , '258 East Street, The Meadows, Pretoria' , null , '[email protected]' , 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.
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.
- Check out this previous article Create a Microsoft SQL Server Database
- Check out these additional resources:
About the author
Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-07-28
