MS SQL Server – Create Table with SSMS

Overview

In this example we will create a table labeled Users in a database labeled PythonFlaskDemo using SSMS SQL Server Management Studios.

The table will have the following columns:

• Id (Primary Key)
• Username
• Email
• EmailVerified – Boolean (bit) true or false
• Password
• DateCreated – Record date record was create
• DateModified – Record date record was modified

Prerequisites

Download and install MS SQL Server from Microsoft’s Website here.

Download and install SSMS from Microsoft’s Website here.

Create a new Database

This is a simple Users table and would only be sufficient for a small application demo. This will be used in a series of tutorials for Flask and ASP.NET C# Web API.

Right click on “Databases” and “Create Database”.

Add new database labeled PythonFlaskDemo and click OK.

Open a new Query window in SSMS (SQL Server Management Studio) and run the following script to create the table.

CREATE TABLE [dbo].[Users](
	[Id] [int] NOT NULL,
	[Username] [varchar](50) NULL,
	[Email] [varchar](200) NULL,
	[Password] [varchar](256) NULL,
	[EmailVerified] [bit] NULL,
	[DateCreated] [date] NULL,
	[DateModified] [date] NULL
) ON [PRIMARY]

From SSMS – Alter the table by setting the primary key and Identity Column to Id to enable the auto increment feature.

Another option is the Alter Table script. This tutorial is to get users familiar with SSMS.

Add few records with the following insert statements.

USE [PythonFlaskDemo]
GO
SET IDENTITY_INSERT [dbo].[Users] ON 
GO
INSERT [dbo].[Users] ([Id], [Username], [Email], [Password], [EmailVerified], [DateCreated], [DateModified]) VALUES (0, N'Guest1', N'guest1@fakeemail.com', N'abc123', 1, CAST(N'2020-01-06' AS Date), NULL)
GO
INSERT [dbo].[Users] ([Id], [Username], [Email], [Password], [EmailVerified], [DateCreated], [DateModified]) VALUES (1, N'RoudyRhonda', N'roudyrhonda@fakeemail.com', N'abc123', 1, CAST(N'2019-12-02' AS Date), NULL)
GO
INSERT [dbo].[Users] ([Id], [Username], [Email], [Password], [EmailVerified], [DateCreated], [DateModified]) VALUES (2, N'freddycrouger', N'freddycrouger@fakeemail.com', N'abc321', 1, CAST(N'2020-03-04' AS Date), NULL)
GO
INSERT [dbo].[Users] ([Id], [Username], [Email], [Password], [EmailVerified], [DateCreated], [DateModified]) VALUES (3, N'bobbyfisher', N'bobbyfisher@fakeemail.com', N'cba321', 0, CAST(N'2020-01-02' AS Date), NULL)
GO
INSERT [dbo].[Users] ([Id], [Username], [Email], [Password], [EmailVerified], [DateCreated], [DateModified]) VALUES (4, N'marshall44', N'marshal44@fakeemail.com', N'fds345', 1, CAST(N'2020-04-03' AS Date), NULL)
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO

From a new Query window run the following:

select * from users

And you should see the following: