Database Fundamentals #6: Create a Table with the SSMS GUI

The whole idea behind databases is to store information, data, in order to look at it later. The place where that data is going to be kept is referred to as a table. A table consists of columns and each column can be defined to store a variety of specific kinds of data. You can make small tables or large ones. The columns on the tables can be made so that they have to have data in them or they can be empty. The choices are yours to make, but you need to know how to set tables up appropriately .

In addition to tables, we’re going to start learning about columns. Columns can be very generic in nature, supporting all sorts of different kinds of data, or they can be extremely specific, storing only one type of data. I’ll introduce a number of different types of data so you can begin understanding the wealth of choices that are available to you.

As a concept, tables are very simple and straight forward. It’s the application of this concept that gets complicated. The goal here is to learn not only what can get stored, but how best to store it. I know that many of you may never create a table as you manage existing databases for purchased applications, but understanding how tables work will help you to better, and more easily, manage your systems.

Tables are Where The Data Lives

Unlike a database where you can rely on a set of defaults, a table has to be defined completely, every time. There are defaults within the column definitions but building the table requires you provide definitions all the way through. Tables can be defined through the GUI or by using TSQL. We’ll start with the GUI.

Creating a Table with the Table Designer

Connect to your server if you’re not already. In the Object Explorer window, navigate to a database you can work on. Expand the database by clicking on the plus sign and you should see a folder labeled Tables. Right click on the Tables folder and from the context menu select “New table…” This will open the Table Designer window in the tabbed area to the right of the Object Explorer and you’ll see a new toolbar across the top of the screen, the Table Designer Toolbar. You can ignore that for the moment and concentrate on the window.

The Table Designer window is broken into three areas, the column list, which is the main part of the window, the properties, off on the right by default, and the column properties which will be down at the bottom. Your screen will look something like this (click to make bigger):

Table Properties

You’ll work with all three areas to build a table. The very first area you should concentrate on is the Properties area of the window. This is where you can start to define the table being built. You need to name your tables, so click on the text box to the right of the (Name) property. Currently, your table is named, Table_1. If you continued using this name and added more tables they would each be named in order, Table_2, Table_3, etc. However, it’s very hard to know what’s stored in tables with names like these. Instead, you should provide meaningful names for your tables. These names should reflect the business need that the table is going to satisfy. For your first table use the name Person. Just type it into the box, making sure that you overwrite the existing name.

The properties that are greyed out cannot be edited from this window. The other properties can be changed. Some of the properties are text boxes, just like the database name. For example, click on the box next to Description. Here you can describe the business need for a table. It’s a good idea to get into the habit of doing that when you create tables. This table is for storing information about people in the system. Type a description for that on your own. Other properties use drop down menus to select values. Click on the box next to Schema. You’ll see a drop down button on the right. Click that and you’ll see the default schemas created with the database. Don’t change the schema. This will be covered in a later blog post. The rest of the properties are outside the scope of this discussion. For now, accept all the defaults.

Time to add some columns to the table

Columns and Column Properties

The columns defined within a table define what kinds of data will be stored with that table. To get started on the first column, click on the Column Name column in the main part of the designer window. You’ll have to type in a value for all column names. There are no defaults here. For the first column type the value PersonID. This will become a way to identify individuals without having to rely on their names or some other value that will likely be duplicated.

Next, you’ll want to define the type of data that this column will hold. You can click on the Data Type column, but since you’re already typing, with your fingers on the keyboard, it’s usually easier to continue to use the keyboard. You can hit tab and the focus will change from the Column Name to the Data Type column. The default data type will be automatically selected. Unless you’ve made changes to your system, this will be nchar(50). The different data types will be discussed in another blog post. For now, just follow along with the data types as defined in the text. This column could simply be left as the default data type, but since the need is for it to be an identifier, it’s easier to use a number. You can either type a value or select one from the drop down list. For this column, select or type the value int. That’s short for integer. After you do this, tab again to change the focus to the Allow Nulls column.

You may have noticed that the Column Properties were filled in when you first moved into the Data Type column. After changing focus to the Allow Nulls column, the values in the Column Properties will have changed. Most of the time, you won’t need to make changes to anything in the Column Properties. You can create tables just using the Table Properties and the basic column information. But, there will be cases where you need to modify Column Properties. For example, in this case, the business wants to have an identifier for people in the system. Rather than having to try to come up with that as data is entered into the system, you can generate it automatically. Scroll down through the properties for the column until you see Identity Specification. Click on the plus sign next to that. Your screen will look like this:

One column on a table may be defined as an Identity column. Select the drop down on the (Is Identity) property then choose ‘Yes.’ You’ll see the Identity Increment and Identity Seed values change from grey to black and the number one (1) will appear in both. Increment represents the steps between values. With a value of one, you’ll see the values moving up by one step at a time. Identity Seed is the starting point that the numbers will begin to count from. You can leave these on the defaults for this table. With this enabled, when data gets inserted into the table, it will automatically get a new identifier value without you doing any work at all.

Finally for this column, you need to decide if you want the data to be required or not. When you selected the Identity Specification though, that choice was taken out of your hands. An identity column will always have a value, so it can’t be left NULL. But you can decide to have other columns allow for no information to be entered by using the NULL value. NULL is a very complicated topic and will be addressed in more detail in a series of blog posts.

You  can now continue to add more columns to the table using the Table Designer window. You won’t need to make modifications to the Column Properties for the rest of the columns. This shows how the rest of your table should be defined.

After you finish setting up the rest of the table, you can save this table to the database. You can do this by hitting the key combination CTL-S or you can click on the disk icon on the toolbar. Either of these will save the table. Once completed, you’ll need to refresh the Object Explorer window to see the new table. It will be visible there under the Tables folder after the refresh.

Deleting a Table

To  delete a table through the GUI, you would navigate to the table in the Object Explorer menu and then select the table. You can then select the Edit menu and the Delete menu choice. This will open the Delete Object window that you can see here:

If you click the OK button, this table will be removed.

Conclusion

That’s how the GUI works to add and remove tables to the database. Remember, SQL Server always has more than one way to get something done. Learning how the GUI works can be enough for some people. Others are going to want more speed and more direct control. That’s what TSQL is for. We’ll  recreate the same table using TSQL so you can see exactly how that works in the next Database Fundamentals blog post.