In the last tutorial we covered connecting to a SQL Server and creating our database, ContactManager. The next step in the process is to create a table in which we can store the names and primary details of our contact. Using the Management Studio the process of creating a new table is fairly simple, and everything is managed via the GUI.
A table can be compared to a file, it represents a single container within a database used to store data. The data is stored in the form of columns, each representing a type of data to be stored, with a descriptive name. The data itself is stored as rows inside the table, each mapped to the correct column, and effectively creating a grid.
The first step is to create and open the table designer for the new table we need. The table will be called Contact, but it will only be named once we save the table and have added all the required columns.
- Expand the ContactManager database in the Object Explorer using the + next to it.
- Click on the Tables heading to select it.
- Right Click on the Tables heading and select New Table from the drop down list.
- A new window will open up with 3 columns: Column Name, DataType and Allow Nulls
For our contacts table we will require four columns initially. Before going into the creation of the columns, let's focus on DataType's. Each column has a format, which is defined by the type of information required to be stored. The most common type of data is text, and SQL provides a few different ways to store the data. Storage of data is an important factor to define the amount of space it will take up in the database. A detailed breakdown of data types in SQL can be found here.
For this tutorial we will be focussing on 3 specific data types: UNIQUEIDENTIFIER, NVARCHAR and CHAR. The first is effectively a 16-Byte GUID and the latter two can be used to store characters. The big difference between NVARCHAR and CHAR is the way it stores characters. NVARCHAR will always store the exact characters up to the maximum column size, and CHAR will store the exact number of characters based on the column size. Therefore a CHAR(3) will always store 3 characters, even if only one character is added.
The first field to be created is the Primary Key for the table. The primary key allows each individual row to be easily and uniquely recognised. In most case auto incrementing INT fields are used as primary keys, however GUID's have become a default for Microsoft. We will explore this in more detail when looking into implementing membership for the Contact Manager application.
- Column Name: ContactID
- Data Type: UNIQUEIDENTIFIER
- Allow Nulls: Unticked
- In the Column Properties / General look for Default Value or Binding and set it to NEWID()
Step 4 will ensure that the ID is generated by the server automatically when a new record is created, eliminating the need for generating the ID in code. NEWID() is a SQL Server function that create a random GUID. This does however add an extra step to the process when adding rows into the database, which will be covered in the relevant tutorial.
Repeat steps 1 - 3 until the grid matches the sample below.
The final step in the process is to save the table.
- Click on File - Save Table_x
- Enter Contact as the name
- Click on OK.
If all went well the top tap will change to Table - dbo.Contact and the table will appear under the Table heading in the Object Explorer.
Have fun until next time!