Here is a table of the most commonly used data types in SQL Server

Data Type	Description
Char()	Fixed length string, unused spaces get padded and eat up memory: size 0-255
Varchar()	Variable length string, unused spaces don’t use memory: 8000 chars
Nvarchar()	Designed to handle Uni Code data (UFT-8): 4000 chars
nvarchar(max)	536-870-912 characters
Text	Up to 2GB of text data
Identity(x,y)	Auto incrementing number with x being starting point and y = steps, so Identity(1,1) starts and1 and counts by 1
INT	integer (whole number, no decimals)
Decimal(x,d)	floating point decimal number, x is size, d is number of places after the decimal
float(n)	floating precision number, Float(24) = 4-bytes, Float(53) = 8-bytes — float(53) is default
Bool or Boolean	Binary choice, 0 = False and 1 = True
Date	Date data type “YYYY-MM-DD” (if set to US settings)
DATETIME	datetime data type “YYYY-MM-DD HH:MM:SS” (if set to US settings)
TIME	Time “HH:MM:SS”
YEAR	year in for digit representation (ex 1908,1965,2011)

We will be using SQL Server Management Studio in the following lessons. If you have SQL Server installed on your machine, search for MS SQL Server Management Studio in programs or search for SSMS. If you need to install MS Sql Server: click here

Once it opens, enter the server you are looking to connect to and pick your authentication method (I’m using Windows Authentication, but you could set up a SA account and use Server Authentication)

If you properly connect to the server, you should get an object explore like the one seen below

Create a database

If you are working on work or school SQL Server, you may not have rights to create a database, you will most likely have a database assigned to you that you can build tables in. You can skip to the table creation part of the lesson.

Method 1: Using the Gui

Right click on database in the object explore, click New Database

Next name your new database, leave all other settings as is. Click Ok

Your new database’s name will appear in the list of databases now

Method 2: Use SQL

This is my preferred method. And again, we will just be using the default settings here to make this lesson easier.

Click the New Query Button to open a new query window

In the new window, type the following (note the semicolon at the end of the line, this is standard SQL and used by most system. SQL Server allows you to replace ; with the word GO. It is completely legit, I just don’t use it because no other system does either)

Create Database Test2;

Then click Execute

If you don’t see your new database appear in the Object Explorer, right click Database ,and select Refresh

Select Database to Work With

Method 1: Gui

From your query workspace, select your database from the drop down menu

Method 2: SQL Code

Go to a query workspace and type in the following code

use Test;

I tend to like this method because you can put it on the top of code you might share and it will guide people to the right database

Create Table

Method 1: Gui

Hit the + next to your database to expand

Right Click Tables > New > Table…

Now manually enter column names and datatypes for your new table

Once you are done. Click the X to close this tab. You will be first asked to save changes (yes) then you will be asked to Name you new table

Method 2: Sql Code

From your query window, use the following code to create a table:

Create Table tableName (
    Column 1  datatype,
    Column 2  datatype));

The syntax is pretty straightforward. The code below with create a table names Contractor with 6 columns

create table Contractor (
ContractorID int primary key,
CompanyNM nvarchar(255),
LastNM	nvarchar(255),
FirstNM nvarchar(255),
Phone nvarchar(50),
email nvarchar(255));

Note I am able to assign the primary key to the first column by putting primary key after the datatype

Copy this into your SQL Server — Note you can run segments of code by highlighting them first and then hitting execute. Only the highlighted code is run.

To see if it runs successfully, expand your tables segment out on your object explorer

Let’s add another table. Copy the following code over to SQL Server and execute just like before

create table Permit (
PermitID nvarchar(255) primary key,
StartDate date,
ProjectTitle nvarchar(255),
[Location] nvarchar(255),
Fee money,
ContractorID int);

Now lets connect the two tables with a foreign key/primary key relationship. To create this relationship, use the following code

alter table Permit
add foreign key(ContractorID) References Contractor(ContractorID);

Note I am working with the table Permit, I am saying the Column ContractorID is the foreign key in the Permit table related to (References) the ContractorID column in the Contractor table

Add data to tables

Use the following code to add data to the two tables

insert into Contractor
values (1, 'Front Poarch Construction', 'Poarch','Ken', '555-1234', 'poarch@fpc.com'),
       (2, 'Mikrot Construction', 'Mikrot', 'Kim', '555-5678', 'MK@mikrot.com'),
	   (3, 'Sobaba Construction','Sobata', 'Jeri', '555-9012', 'SJ@sobaba.com');

Insert into Permit 
values ('B12345','2022-01-01','My Deck','Branchburg',550.00,3);

The syntax is basically

Insert into <tableName>

Values (data separated by commas, rows wrapped in parathesis, again separated by columns)

Add Data from Excel File

You can download the following file if you want to play along

Right click on your database, Tasks> Import Data

Click Next on the first Window to pop up

Choose Microsoft Excel as Data source, browse for your file, make sure First row has column names is selected, click Next

Select SQL Server Native client as destination. If you have more than one to pick from, choose the higher number. Click Next

Leave default options – Click Next

Select the top option, You can change the destination table name if you choose.

I choose to change it and then click Next

Leave default selections, click Next

Click Finish

Make sure you got 75 rows Transferred and click close

Move data to production tables

Permit_Landing is a Landing Table. That means a table you load data into initially before it is verified and moved to production tables

Refresh your database to see the new added tables. Right click on Permits_Landing and Select Top 1000 Rows

A query window should pop up and give you the following results

The goal is to move this data to the Permit table. But note, the Permit Table has a column ContractorID that is not present in Permits_Landing. So we have to use code like seen below.

  insert into permit (PermitID, StartDate, ProjectTitle, [Location], Fee)
  select * from Permit_Landing;

Note, we have Insert Into Permit (like before) — but we now include a list columns. We only list the columns we want to load data into. Since we don’t have ContractorID column in the landing table, we will not include it here.

Also, notice the [] around Location. This is because location is a SQL key word. To let SQL Server know we are talking about a column and not a keyword, we put square brackets around it

Finally, we choose the data to load into the table using a simple select statement: Select * from Permit_Landing