SQL Server - create databases, tables and relationships
Part one of a four-part series of blogs

A guide to how to create databases in SQL Server Management Studio, including creating tables, indexing columns and using database diagrams to create relationships between tables.

  1. Creating a Database and Tables in SQL Server (this blog)
  2. Creating Tables within a Database
  3. Creating Relationships or Joins between Tables
  4. Indexing Columns in a SQL Server Table

This blog is part of the Wise Owl online SQL tutorial.  We also run classoom-based courses in SQL.

Posted by Andy Brown on 19 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Database and Tables in SQL Server

In order to be able to write SQL, you need to have some tables to query!  This blog shows you how you can create tables, and link them together using relationships.

All of the diagrams and examples use SQL Server Management Studio 2008 R2, but you'll be able to follow this blog using SSMS 2005 and 2012 also.  Not much has changed for table design between versions!

Pre-requisites for Creating a Database

Before you can create tables in SQL Server, you need to know the following:

Topic Notes
Designing databases A SQL Server database consists of two or more tables linked together using relationships.  You can see how to design databases - and what this term means - in my earlier blog on database design.
Using SSMS The usual tool for creating tables using SQL Server is called SQL Server Management Studio (or SSMS).  You can see an introduction to Management Studio here, and see how to configure some useful options.

Our Database

This blog will show how to create a simple two-table database looking like this:

Two-table database design

Our database will contain two tables: one to hold companies, and another to hold the staff who work for them.


In this database, each table has a primary key (the field shown above with the key symbol next to it).  This is the field whose value uniquely determines which record we're looking at. 

For example, there may (will!) be lots of people in the tblPerson table called Andy Brown, but there will only be one person with a given id number of 317265.

Creating our Database

To create a new database, first connect to a server, then right-click on the server and choose to create a new database:

Creating new database menu

Right-click on Databases, and choose to create a new one.


You can now type in a name for your new database (here I've gone for BlogExample), and choose to add it:

Creating database - filling in dialog box

Type in the database name then select OK.

As the above dialog box shows there are a lot of ways in which you can control how SQL Server will create and manage your database, but this blog is meant to be part of a tutorial on writing SQL, not database administration!  Choosing all of the default options should work fine on your computer.

After creating your database, I'd recommend refreshing the list of databases on this server to ensure that the new one you've just created appears in the right position:

Refresh menu for SSMS database

Right-click on Databases again, and this time choose to refresh the list.


Your new database should now appear in the correct position in alphabetical order:

New database in list

The new database appears in the list of databases.


Now that we've got a database, you can create tables within it - read on!

This blog has 0 threads Add post