BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Creating a Database and Tables in SQL Server
- Creating Tables within a Database (this blog)
- Creating Relationships or Joins between Tables
- Indexing Columns in a SQL Server Table
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 Tables within a Database
If you expand the database that you have (I hope) just created, you can see that it already contains a Tables category:
Any tables that you create will appear in this list.
To create a new table, just right-click:
Right-click on Tables to create a new one.
SSMS will produce a 3-column grid for you to fill in:
The start of a new table.
Specifying the Table Columns
Every column that you create in a table has 3 pieces of information that you can fill in:
|Information||What it contains|
|Column Name||The name of the column or field (don't use spaces).|
|Data Type||The type of data for the column.|
|Allow Nulls||Whether it is possible not to enter a value for this column for a record (so, for example, you would untick this box to make sure that someone fills in a person's name, but leave it unticked, perhaps, for the date of birth column, which can be left blank).|
Here is what you would probably create for our example tblCompany table:
The columns would give the unique number of each company, its name and a description of what it is and does.
You should refer to this separate blog for the possible data types that you can use.
You now want to make sure that every time someone enters a new company, it is automatically assigned a number one bigger than the previous one (that way, you can be confident no two companies can ever share the same number). Here's how to do this. First, make sure you have the id column selected:
First select the CompanyId column.
You can now tell SQL Server to increment its value automatically:
Expand the Identity Specification category, and tell SQL Server that this column is an identity one. You can also specify the increment and seed (although usually you'll set these both to 1 - see hint below for what they mean).
Suffer from triskaidekaphobia? Set the identity seed to 14 and the increment to 2, and your numbers will go 14, 16, 18 ... instead of 1, 2, 3 ... !
Creating a Primary Key
Every table in SQL Server should have a primary key (it's virtually a requirement). This is usually - although not always - a single column. The primary key is the field whose value uniquely identifies a record. Here are some examples:
|National Insurance database||National Insurance number|
|NHS database||NHS number|
|Your company's personnel system||Your personnel number|
In our example, the primary key is going to be the CompanyId column, so you can right-click on this and set it:
|Right-click to set the key ...||... to get a key symbol next to it!|
Saving the Table
Having created all of the columns, set the identity column and set the primary key, you can just close down your table, at which point you'll be asked to name it:
Click on this icon to close a table.
You can now choose that you want to save the table:
You'll have a chance to choose a different name if you choose Yes!
It's a convention in SQL Server to prefix table names with tbl, although you don't have to follow this convention:
Type in a name for your new table, and select OK.
Your new table should appear in the list of tables for this database:
If you can't see the table you've created, right-click on Tables and choose to refresh the list.
The Table of Persons
You could now repeat the steps shown above to create the table of people for our database:
The table of people, called tblPerson. The columns are listed below.
The columns in this table are:
|Heading 1||Heading 2|
|PersonId||The unique number for each person (the primary key, which should be set to an Identity column).|
|FirstName||Each person's first name (we allow this to be empty, or null).|
|LastName||Each person's last name (must be filled in).|
|DateOfBirth||The optional date of birth for each person.|
|CompanyId||The number of the company each person belongs to.|
Again, the possible data types you could have used are explained in this separate blog.
Entering Records for a Table
Having created a table, you can right-click on it to enter data for it:
Right-click on a table to edit its data. The menu option shown is for SQL Server 2008 R2; the menu is similar for other versions of SQL Server.
Here's what you could type in for our example for the companies table:
Here we've created 4 companies. SQL Server always shows a blank row for a new record.
Note that you can't type in a value for the CompanyId, as this will be generated automatically by SQL Server. If you don't want this to happen, don't make the column an identity column!
You could now close this table down, although you don't need to - each record is saved as soon as you move off it.
It's now time to repeat these steps to type in some people into the tblPerson table:
Our 7 people: 3 work for Shell, 2 for IBM, 1 for Microsoft and 1 for Wise Owl.
Note that you have to know the company ids to enter them in the table (for example, if David Richards works for Shell, you need to know that the CompanyId for Shell is 1, and type this into the tblPerson table against his name). Most applications will contain a front-end form featuring a drop list to make this easier, so that users don't have to memorise the id numbers for each company!
The Next Stage
Now that you've created two tables, it's time to tell SQL Server that there is a relationship between them (and prevent someone from assigning a company id to a person when no company with that id exists in the parent tblCompany table). This is the subject of the next part of this blog!