Create a Table with SQL Server 2012

SQL Server


Tables serve as the basic unit of organization for any database, including those managed by SQL Server 2012. Designing appropriate tables to store your data is an essential responsibility of a database developer and both designers and administrators must be familiar with the process of creating new SQL Server database tables. In this article, we explore the process in detail.

Note that this article describes the process of creating tables in Microsoft SQL Server 2012. If you are using a different version of SQL Server, please read Creating Tables in Microsoft SQL Server 2008 or Creating Tables in Microsoft SQL Server 2014.

Step 1: Design Your Table

Before you even think about sitting at a keyboard, pull out the most important design tool available to any database developer - a pencil and paper. (OK, you are allowed to use a computer to do this if you like -- Microsoft Visio offers some great design templates.)
Take the time to sketch the design of your database so that it includes all of the data elements and relationships that you will need to meet your business requirements. You'll be much better off in the long run if you start the process with a solid design before you start creating tables. As you design your database, be sure to incorporate the database normalization to guide your work.

Step 2: Start SQL Server Management Studio

Once you've designed your database, it's time to begin the actual implementation. The easiest way to do this is to use SQL Server Management Studio. Go ahead and open SSMS and connect to the server that hosts the database where you would like to create a new table.

Step 3: Navigate to the Correct Folder

Within SSMS, you'll need to navigate to the Tables folder of the correct database. Notice that the folder structure on the left side of the window contains a folder called "Databases." Begin by expanding this folder. You will then see folders corresponding to each of the databases hosted on your server. Expand the folder corresponding to the database where you wish to create a new table.
Finally, expand the Tables folder underneath that database. Take a moment to examine the list of tables that already exist in the database and make sure it reflects your understanding of the existing database structure. You want to be sure not to create a duplicate table, as this will cause you fundamental problems down the road that may be difficult to correct.

Step 4: Begin Table Creation

Right click on the Tables folder and select New Table from the pop-up menu. This will open a new pane within SSMS where you may create your first database table.

Step 5: Create Table Columns

The design interface presents you with a three-column grid to specify the table properties. For each attribute you wish to store in the table, you will need to identify:

  • Column Name: This is, quite simply, the name of the attribute. Each database attribute is similar to a variable; it is a single data item that your database will store for each record. For example, if you have a table consisting of contact information, you might have columns named FirstName, LastName, HomePhone, and EmailAddress containing information about each person in the contact database.
  • Data Type: The data type specifies the type of information that you will store in each column. For example, a person's first name consists of a string of letters, so you might use the varchar(50) data type to indicate that the column will contain a variable length string that is no longer than 50 characters. Similarly, a date of birth would use the date data type. For more information, see our article on SQL Server data types.
  • Allow Nulls: This is simply a checkbox. If you check it, the database will allow you to store null (or blank) values in that column. For more information, see Understanding NULL Values.

Go ahead and complete the grid matrix, providing each of these three pieces of information for every column in your new database table.

Step 6: Identify a Primary Key

Next, highlight the column(s) that you've selected for your table's primary key. Then click the key icon in the taskbar to set the primary key. If you have a multivalued primary key, use the CTRL key to highlight multiple rows before clicking the key icon.
Once you've done this, the primary key column(s) will display a key symbol to the left of the column name, as shown in the image above. If you need assistance, you might want to read the article Selecting a Primary Key.

Step 7: Name and Save Your Table

After creating a primary key, use the disk icon in the toolbar to save your table to the server. You'll be asked to provide a name for your table when you save it for the first time. Be sure to choose something descriptive that will help others understand the purpose of the table.
That's all there is to it. Congratulations on creating your first SQL Server table!