Create a customer table with SQL Server

This script goal is to Create a customer table with SQL Server. This SQL Server customer table example is used in many articles and tutorials on Expert-Only. This simple script first tests if the table Customers already exists. Then, if it does the table is dropped. Then the SQL Server table called Customers is created. And the script inserts a few lines before selecting the content of the table.

Create a customer table with SQL Server

This article shows the SQL SERVER CREATE TABLE, the SQL SERVER DROP TABLE, the SQL SERVER DROP TABLE IF EXISTS, the INSERT INTO and the SELECT syntax.

This example creates a customer’s table to store the Customer ID, the first name, the last name, the city, and the country.

Just COPY and PASTE the SQL script in the SQL Server database management tool, like SSMS for example.

This CUSTOMERS table contains five distincts columns:

• The Customer ID
• Firstname
• LastName
• City
• Country

First step : two ways to check if the table exists and drop it if necessary

-- If the Customers table already exists, then we delete it
IF EXISTS(
	SELECT   1 FROM sys.objects
	WHERE   object_id = object_id(N'[dbo].[customers]') AND type in (N'U')
)
BEGIN
DROP TABLE dbo.customers
END
GO

-- in recent versions, starting at Sql Server 2016, it is possible to use directly this command: 
DROP TABLE IF EXISTS dbo.customers

Second step: create the customers table

-- Creation of the Customers table with the column FirstName declared as UNIQUE
-- The UNIQUE keyword defines the column with a unique value
-- An insertion of two clients with the same first name is therefore impossible
CREATE TABLE dbo.customers (
[CustomerID]	int IDENTITY(1,1),
[FirstName]		nvarchar(20),
[LastName]		nvarchar(20),
[City]			nvarchar(20),
[Country]		nvarchar(50)
)
GO

Third step: add the constraints on ID and names

The CustomerID column is the primary key, so it cannot have duplicate values for this column.

The fisrt name and last name have a Unique constraint, so the combination has to be unique.

ALTER TABLE dbo.customers 
	ADD CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED (
		[CustomerID] ASC
    );
GO 
ALTER TABLE dbo.customers 
	ADD CONSTRAINT [CustomersUniqueNames] UNIQUE NONCLUSTERED (
		[FirstName], 
		[LastName]	
	);
GO

Fourth and last step: insert the sample data and display the table content

-- Insertion data for the example
INSERT INTO dbo.customers ( FirstName, LastName, City, Country ) VALUES ( 'Akhihabara','Akhihabaran','Tokyo','Japan' );
INSERT INTO dbo.customers ( FirstName, LastName, City, Country ) VALUES ( 'Johnny','John','Toronto','Canada' );
INSERT INTO dbo.customers ( FirstName, LastName, City, Country ) VALUES ( 'Ali','Ahmed','Cairo','Egypt' );

-- Verification of inserted lines
SELECT * FROM dbo.customers;

Screenshot of the entire script from SSMS:

The result of the script execution:

This article shows how to create a customer table with SQL Server with simple T-SQL instructions. The table is also used in this article about a table with a primary key.