SQL: Common Data Types in SQL Server

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

Data TypeDescription
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
TextUp 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
INTinteger (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 BooleanBinary choice, 0 = False and 1 = True
DateDate data type “YYYY-MM-DD” (if set to US settings)
DATETIMEdatetime data type “YYYY-MM-DD HH:MM:SS” (if set to US settings)
YEARyear in for digit representation (ex 1908,1965,2011)

SQL: Intro to SQL Server – Create Database and Tables

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

Database Design: Lab 4 Walkthrough

I made these video walkthroughs as an alternative to following the lab in the text book. I know some people (myself included) learn better from watching videos.

This is a walkthrough for Lab 1 for my course on Database Development and Design. Feel free to watch video, but I will not be sharing any files as they were not created by me and I do not have permission to share them.

Database Design: Generalization and Specialization

•How do we handle the situation when we have multiple classes but realize the classes have a significant amount of information in common?

•How do we handle the situation when we have a single class but realize that there are differences among the different objects in that class that may drive us to break up the class into two classes?


When the majority of the information about two types of objects are the same, but there exists some different specialized data

•Let’s say this class already exists for a small startup company

•All employees have an ID, firstName, lastName, and salary

•The startup has grown enough that they now want to hire consultants

•Instead of salary, consultants have an hourly rate

•Subclasses (or inherited classes) contain the specialized information

•Permanent and Consultant are both subclasses of Employee

•Superclasses (top classes) should be as general as possible

•Future changes to the superclass would affect all subclasses

•Easy to add additional subclasses


When you have 2 or more existing classes and realize they have some information in common

Adding a superclass and pulling out the common information from the 2 subclasses into the superclass (the same as specialization only in the opposite direction)


•Generalization and Specialization are examples of inheritance

•SubClassA and SubClassB are both specialized types of SuperClass

•SubClassA and SubCLassB will have all the attributes of SuperClass in addition to their own attributes

Database Design: Developing a Data Model

An important first step for developing a data model is taking the time to learn about the data and how the data relates.

Remember not all data is useful. While you may wish to include data that does not directly relate to the problem, this can quickly become problematic as your data model can grow into an unmanageable mess.

Keep in mind, a data model should work for:

  • A set of assumptions
  • Function within the limitations of the problem scope

Lets start with an example of a soccer (football for all my non-US friends) club data model. Here is an example of a class you could build for holding team data:

The attributes are used to capture data about each team.

But let’s look at a new problem we haven’t really discussed yet:

Look at the AgeGroup above. If I asked you what teams are in the U-12 age group, as a human you could look at the table above and tell me that Rage and Hurricanes are. However, if you tried running a query for U-12, it would only return Rage, as U – 12 and U-12 are viewed as completely different terms by a computer.

To prevent this, one approach could be to create a new class

Now, U-12 will only appear once in the Agegroup table, this removes the risk of someone typing it in differently like in the table before. Integrity of data accuracy is something to consider when deciding how many tables to create.

Now lets look at the issue of team captains, considering a team captain is also a player. The diagram below shows there are 2 relationships between Player and Team classes. This is perfectly okay.

•Do you want to select objects based on the value of an attribute?  Then you may want to introduce a class for that information. 

  (Ex: you want to see all teams that are in age group “U-12”)

•Do you need to store other data about this information?  Then you may want to introduce a class for that information.  (Ex: We are storing the team captain’s name but also want his/her email and phone number)

•Are you already storing similar information?  Then you may want to use a relationship among existing classes.  (Ex: the information about team captain is the same as the information about the players, so use that class with a new relationship)

Multiple Companies in One Building

Now consider the example of a building housing multiple companies. While the diagram below is not completely incorrect, I will argue against the relationship between Employee and Room. In this example, it appears that you can infer the Employee location through the Company-Room relationship. While having multiple route for data isn’t wrong, make sure they convey different information.

Fan Trap:

•Each employee belongs to one division

•Divisions are made up of many Groups

•The problem here is if you try to infer something that was not intended

•You know it’s a fan trap when you have 2 relationships with many cardinality on the outside ends

They way the data model is written, a division can have many different employees and a division can also belong to many different groups. So trying to determine what group an employee belongs to via their division is impossible in this data model.

Chasm Trap

•Each employee can belong to at most 1 group

•Each group belongs to 1 and only 1 division

•Divisions are made up of many Groups

•Can you answer the question, “What division does each employee belong to?”

•You know it’s a chasm trap when the connection is not always there or there is a gap in a route between classes

•Ann doesn’t belong to a group since the optionality is 0

•We can only determine division based on group assigned

•So we have no idea what division Ann is in

Multiple Routes Between Classes:

•Whenever there is a closed loop, check to see if the same information is being stored more than once (don’t be redundant).

•Make sure you are not inferring more than you should from a route.  Always look out for the case when a class is related to two other classes with a cardinality of many at both outer ends.

•Ensure that a path is available for all objects.  Are there optional relationships along the route?

You an even have a Self Relationship

•Say that a club requires an existing member to sponsor any new members

•You wouldn’t have a class for member and a class for sponsor because they have the same data

•You can represent this type of situation with a self relationship because objects of a class can be related to each other

MS Access: Use comparison operators and dates in queries

This tutorial was created as supplemental material for my undergrad course in database design. You can find the full course here: Course

For this example, I want to create a new table. I have attached an Excel file below that you can download.

From access: External Data> New Data Source > From File > Excel

Check First Row Contains Column Headings and click Next

You can change the data types of the column, but I am just leaving them as is.. click next

Let Access add primary key > click next

Name your table and hit finish

Now if you click on the Employee table in the table list on the left you will see the results

Comparison Operators

Comparison operators are the symbols that let us check if something is equal to, greater than, less than, etc

Lets create a query using comparison operators

Click on Create > Query Design

Drag the Employee table into the query workspace

Add all the fields below and in the Criteria spot for Age, put >40

Right click the Query Tab and click Datasheet View

You can now see the results with employees only over the age of 40

Play around with it, try less than 40, >= or <=, just try some different queries

Also remember, you can right click on Query1 tab and select SQL View to see the SQL code that runs the query

You can also use Between to select Criteria- Below will return everyone aged between 30 and 45


Now lets try querying dates

When working with dates, you need to put #’s before and after the date. If your Access is set to USA settings, we go MM/DD/YYYY, European (and most of the rest of the world) goes DD/MM/YYYY

The below query will return employees hired after Jan 1 2010

And here are the results

MS Access: Query two related tables

The entire purpose of related tables is that they allow you to query information from multiple tables at once. In this example we will be creating a query that looks at the Class and Teacher tables we built in the intro to MS Access Lesson: link to lesson

To start, we will select Create from the Menu bar and Query Design from the Ribbon

Next drag the Teacher and Class table over to the blank space for Query1

down below you can select the columns (fields) you want to bring in from the tables.

Next, put all the all the available columns in the Fields below

You’ll notice the Fields all have their table names in front of them in the drop down. This is common SQL notation for <table>.<field>

Right click on the query tab and select datasheet view.

Note the two tables are matched up by the teacherID in two different tables. That is the relationship. That is how relational database allow tables to interact

Lets remove the teacherID from the query

Go back to design view and uncheck the columns to hide them from the query

To see the results, click on the Query1 tab and select Datasheet view

So you can see, our query returned information found in 2 different tables

Now, let’s right click on Query1 tab and select SQL View

This is how you would write this query using SQL

Select Class.ClassID, Class.Class_NM, teacher.teacher_name — This means that we want to see these three columns. Note the table name is in front, followed by the column or field name, separated by a ‘.’ This is common practice in SQL. It tells the database which table the field is in. And in situations like the teacherID column that is found in both tables, it clarifies which one you want.

from teacher INNER JOIN Class ON teacher.teacherID= Class.TeacherID; — this is a typical join statement. It says use both teacher and Class tables, and match the records up using the TeacherID field.

For more information in Joins, check my SQL Intro to Joins and SQL 4 Types of Joins

Back to Main Course Page: Course

Database Development and Design

Week 1

  1. Databases: What are they, and why do we need them?

Week 2

  1. Database Design: First Things to Consider
  2. Database Design: The Development Process
  3. Intro to MS Access
  4. MS Access: Import Excel, sort and filter table
  5. Database Design: Lab 1 Walkthrough

Week 3

  1. Database Design: Initial Requirements and Use Cases
  2. MS Access: Intro to Queries
  3. MS Access: Calculated Fields
  4. MS Access: Query two related tables
  5. MS Access: Use Comparison Operators and Dates in Queries
  6. Database Design: Lab 2 Walkthrough

Week 4

  1. Database Design: The Model
  2. Database Design: Lab 3 Walkthrough

Week 5

  1. Database Design: Developing a Data Model
  2. Database Design: Lab 4 Walkthrough

Week 6

  1. Database Design: Generalization and Specialization

MS Access: Intro to Access

MS Access is a all in one database solution provided as part of MS Office. Access was much more popular years ago when more powerful solutions such as Oracle an SQL Server couldn’t be effectively run on desktops. While Access’s time in the sun is definitely in decline, for someone new to concepts of databases, Access can be a great introduction.

Let’s start by creating a simple database. Open up Access and click on Blank Database

Give your blank database a name in the pop up window

By default, you’re new database will start with a Table1 with an ID column. Click the drop-down on the next column space to add a new column. For this example, let us select Short Text as our data type. Short text can handle any text up to 255 characters

After you set the datatype, you can click on the column name and rename it

Let’s add another column, set the datatype to number

Now click the X next to table1 and you will be prompted to name your table

Another way to build a table is through the table design feature

You can name your columns and select data types: Note AutoNumber is an auto incrementing datatype that works to provide you with an auto populating ID number

Access requires primary keys for all tables (something not required, but recommended in other systems like MySQL or SQL Server). To create a Primary Key, right click on ClassID and select Primary Key

Finally, let’s just use SQL to create a table. Select Create an then Query Design from the ribbon menu

Select SQL View

Put in the SQL below

create table teacher (
teacherID int primary key,
teacher_name varchar(255))

Click on the new teacher table in the left and you can fill in some data

Now let’s create a relationship between the Class and Teacher tables. Click on Database Tools > Relationships

Drag the Class and Teacher table into the blank sheet and click Edit Relationships

Click create new, select your tables and columns from the drop down, and select Enforce Referential Integrity and select the Create button

A Relationship line will appear, showing you your connection

Back to Main Course Page: Course

Database Design: The Development Process

When start on the design of a database there are few questions you need to ask right away:

  • Not to be dismissive, but do not simply build a database to the specs of a request
  • Take stock of the data at hand, ask around if there is related data that also might be included
  • Think about what users will be doing with the data
  • As best you can, try to anticipate other uses for the data that come up
  • Focus on the type of data you are dealing with, and how it can be best used/store
  • Don’t only focus on the current use of the data, consider the data may find other uses as well

Now, let’s look at database development through the lens of a software development cycle

Keep in mind, this process is not simply linear. This process, in the real world, goes through many iterations. Change management is good idea to put in place. It helps you to deal with issues like:

  1. New data sources
  2. New requirements from business users
  3. Technology platform changes in your organization

To get started, we need to come up with a problem statement, something that can describe the problem we are trying to solve. Use cases are commonly used when developing problem statements. Use cases help to demonstrate how a user will be interacting with your database:

  • Transactional – think like a cash register, adding new transactions to database, updating inventor
  • Reporting base – data doesn’t change often, people want to look more at aggregate numbers over the details of each transaction. (End of the day reporting – what is the final sales total from the cash register)

You can use Unified Modeling Language (UML) to create your use case. UML uses a series of diagramming techniques to help visualize system design. Below, the stick figure represents users and each oval will show one of the tasks the user hopes to be able to use the database for. UML is great for mock-ups, but you should also include a detailed document that provides deeper insight into the tasks below.

Now imagine we want to create a database to store plant data:

Using this data, we can come up with a few basic use cases

Use case 1: Enter (or edit) all the data we have about each plant; that is, plant ID, genus, species, common name, and uses.

Use case 2: Find or report information about a plant (or every plant) and see what it is useful for.

Use case 3: Specify a use and find the appropriate plants (or report for all uses). 

How does our existing table design handle our use cases:

1.Can we maintain data?  Yes

2.Given a plant can we return the uses?  Yes

3.Given a use can we return the plant?  Not really

We need to re-examine this data from a class point of view

We are dealing with 2 classes in the Plant data:



•One plant can have many uses

•This is an example of a relationship between classes

•Relationships between classes are represented with a line in UML

•We need more details besides just a line to know how classes relate

•The pair of numbers at each end of the line indicates how many objects of one class can be associated with a particular object of the other class 

•The first number is the minimum number (0 or 1 to indicate whether there must be a related object)

•The second number is the greatest number of related objects (1 or n to represent many)

Design Phase

•You spend a lot of time in the top 2 quadrants

•It is an iterative process, not simply linear

•Keep reviewing the model to ensure it satisfies the problem

•Once you have a model that works, move on to design

•For the database world, design involves:

Converting our Class Diagram into Database Objects: Tables, Keys, and Relationships

Application Phase

•Now that you have a database foundation in place, the Application phase builds upon it by adding additional functionality for the users

•The Application phase satisfies the Use Cases:

Input Use Cases will most likely be satisfied with Forms

Output Use Cases will most likely be satisfied with Reports

Back to Main Course Page: Course