CREATE TABLE with SQL Server

Add new sample SQL Server tables using the create table statement and SSMS.

Here is an example of a SQL Server Create Table script to create a customer or sales table. This T-SQL code is a Data Definition Language (DDL) or Data Definition Language (LDD) script. These tables are used as examples in many of the Transact-SQL articles on the Expert-Only site. These simple scripts first test if the table exists, if it does then it is deleted. Next, create the SQL Server Sales table. It has two columns, one for the months and one for the monthly sales amounts.

1. CREATE TABLE with SQL Server using a script

Simply COPY / PASTE the script into SQL Server Management Studio (SSMS) for example. This table contains two columns:

• A MONTH column of type NVARCHAR(20)
• An AMOUNT column of type NUMERIC(5)

To begin with, test if the table exists in our database with the exists() function. If it does, then the table is deleted with the T-SQL drop table command. Next, create the SALES table, which simply contains two columns:

-- Test if the table already exists
-- If it doaes then delete the table
IF EXISTS(
	SELECT 1 FROM sys.objects
	WHERE object_id = object_id(N'[dbo].[SALES]')
		AND type in (N'U')
)
DROP TABLE [dbo].[Sales]
GO

-- Create the example table to store SALES
CREATE TABLE [dbo].[Sales] (
	[MonthName] nvarchar(20),
	[Amount] numeric(8)
)
GO

The screenshot below illustrates the execution of the T-SQL in SSMS.

After the table creation, insert a few rows in the table to handle data for the first three months of the calendar year, i.e., January, February and March, in all letters:

-- Insert sales data for our example
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'January', 1000);
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'February', 2000);
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'March', 3000);
GO

-- Select results to check table contents
SELECT 	*
FROM 	dbo.Sales;

This image shows the result with the content of the sales tables, it contains 3 lines.

This article describes an example in Transact-sql to create a table that contains monthly sales data. The other SQL scripts on the blog use this example, which is deliberately basic.

2. Create Table with different numeric data types

This example of creating a Sales table uses different data types. The columns are for example of binary, numeric, integer or string types. One possible solution is to use a T-SQL CREATE TABLE command. For example, here is a code snippet that can be easily adapted to different needs to create a new table.

CREATE TABLE [dbo].[SALES] 
(
	[YearID] INT, 
	[MonthName] NVARCHAR(50), -- alphanumeric
	[MonthCurrent] BIT, -- Boolean, 0 or 1 , false / true
	[NumberMonth] TINYINT, -- very small integer, from 0 to 255
	[EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15
	[NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31
	[NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63
	[Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma 
	[Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma
);
GO

3. MS SQL statement to add a customers table and insert data

This third example concerns the CLIENTS table which is used in many tutorials. Then the SQL Server table called dbo.CUSTOMERS is created. In the same way, you just have to COPY / PASTE the script into your SQL Server database management tool, like SSMS for example. This CUSTOMERS table contains three columns:

1. A first column CLIENTID of type int and auto-incremented.
2. A column NAME of type nvarchar(20) and UNIQUE.
3. A last column CITY of type nvarchar(20).

-- Test if the table CUSTOMERS already exists
-- And delete it if necessary
IF EXISTS(
	SELECT 1 FROM sys.objects
	WHERE  object_id = object_id(N'[dbo].[CUSTOMERS]')
		AND type in (N'U')
)
DROP TABLE [dbo].[CUSTOMERS]
GO

-- Create the CUSTOMERS table with the column NAME declared as UNIQUE
-- The UNIQUE keyword defines the column with a unique value
-- Inserting two customers with the same name is therefore impossible
CREATE TABLE [dbo].[CUSTOMERS] (
	[CLIENTID] int IDENTITY(1,1),
	[NAME] nvarchar(20) UNIQUE,
	[CITY] nvarchar(20)
)
GO

-- Insert data for manipulation examples
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('SERGEI', 'Lyon');
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');

-- Check inserted rows
SELECT 	*
FROM 	dbo.CUSTOMERS;

Let’s continue in the direction of learning how to manipulate and store data in a database, in a structured manner. Here is how to create a view from an existing table. To take it a step further, here is a third sales table that stores all the months in columns. This article explains how to convert rows into columns with a PIVOT SQL query.

In conclusion, SQL Server offers many data formats, alphanumeric or numeric. To go further, this article explains how to modify a SQL Server table to add or remove columns, or change column length.

SQL Server ALTER TABLE