SQL 2005

Creating SQL Server Tables Part 2

The sixth part of the SQL Server Programming Fundamentals tutorial returns to the subject of creating SQL Server database tables. In this article we will use Transact-SQL commands to create the remainder of the tables for the JoBS tutorial database.

The CREATE TABLE Command

In the previous article we created database tables using the graphical user interface tools of SQL Server Management Studio (SSMS). This method is simple and ideal for development work. However, when deploying an application it is often necessary to script the table creation as Transact-SQL (T-SQL) commands. In this article we will describe the use of the CREATE TABLE command, which allows the generation of new database tables and their constituent columns.

In this instalment of the tutorial, we will create all of the remaining tables required by the JoBS database.

Command Syntax

The simplest way to use the CREATE TABLE command is to provide a table name and a comma-separated list of column specifications. This is shown in the syntax below:

CREATE TABLE table-name
(
    column-specification1,
    column-specification2,
    ...
    column-specificationN
)

Each column specification describes a column that will be created within the table. Here we can provide a name for the column and specify the column type and size to use. We can also determine if the column should permit the storage of null values by appending either "NULL" or "NOT NULL" to the specification. These three items are separated by space characters, making the syntax for a column specification as follows:

NB: The use of "NULL" is optional. If omitted, a column will be nullable by default.

Creating a Simple Table

Creating a table using T-SQL is a relatively simple operation, though the statements can quickly become rather long if the number of columns is high. We will start by creating a simple table with just two columns in the JoBS database. If you have not already done so, start SQL Server Management Studio and open a new query window.

We need to ensure that we are working against the correct database before executing any of the commands below. In an earlier article I explained how to change the active database using the drop-down menu in SSMS. You can also change databases using T-SQL with the USE command. If your query window is not showing JoBS as its active database, run the following script. You should see the title of the query window and the database selection drop-down list update accordingly. NB: If your tutorial database is not named "JoBS", substitute the correct name in the USE command.

We can now create our new table. This table will hold a list of all of the geographical areas that are serviced by DisasterFix engineers. Each row will have a two-letter area code and name of the geographical area, with both columns being mandatory. Later in the tutorial we will use this information to link a customer's address to a specific area so that customers can be matched to appropriate engineers. Let's create the table using the script below:

CREATE TABLE GeographicalAreas
(
    AreaCode CHAR(3) NOT NULL,
    AreaName VARCHAR(25) NOT NULL
)

After executing the statement, right-click the "Tables" branch in the Object Explorer and choose "Refresh". The new table should now be visible. If you right-click the table name, you can select "Design" to see the table's structure in the designer, as you did when creating the Customers table in the previous article in this tutorial.

Setting the Main Filegroup

The previous example table makes no mention of a filegroup to use for storing the table's data. If you are building a database that includes multiple filegroups you may want to specify the location of each table individually. You can do this by adding the suffix "ON" to the CREATE TABLE statement, following the keyword with the name of the filegroup that you wish to use. If you do not specify a filegroup, the primary filegroup is used by default.

The JoBS database includes only a single filegroup. However, if it had a second filegroup named "METADATA", we could target the GeographicalAreas table at this location by modifying the T-SQL command as follows:

CREATE TABLE GeographicalAreas
(
    AreaCode CHAR(3) NOT NULL,
    AreaName VARCHAR(25) NOT NULL
)
ON METADATA

Setting a Text / Image Filegroup

Some tables contain one or more columns that use the large object data types, such as VarChar(MAX), XML, etc. If desired, the large object data can be held in a separate filegroup to the information in the other columns of the table. This is useful when using multiple files and filegroups in order to give the best performance.

In the JoBS database we require a table that will hold complaints. Each complaint will include a column that holds details of the complaint made with the text potentially being very long. This will therefore be held in a VarChar(MAX) data type. The complaint can be linked to a customer by holding the customer's unique reference number and, potentially, to an engineer who will also have a reference number.

We can create this table using the following statement:

CREATE TABLE Complaints
(
    ComplaintReference INT NOT NULL,
    CustomerNumber INT NOT NULL,
    EngineerId INT,
    ComplaintTime DATETIME,
    Complaint VARCHAR(MAX) NOT NULL
)

If the JoBS database contained multiple filegroups, we may decide that the information would generally be stored in the filegroup named CORE whilst the large object data would be held in a separate filegroup named LARGE. You have already seen how to specify the location of the regular data. To set the large object filegroup, you use the TEXTIMAGE_ON command as shown below:

CREATE TABLE Complaints
(
    ComplaintReference INT NOT NULL,
    CustomerNumber INT NOT NULL,
    EngineerId INT,
    ComplaintTime DATETIME,
    Complaint VARCHAR(MAX) NOT NULL
)
ON CORE
TEXTIMAGE_ON LARGE

Creating the JoBS Database Tables

The JoBS database now includes three tables, permitting the storage of customers, geographical areas and complaints. To provide for all of the functionality required in the application we need to add a further twelve tables. These tables are described in the following sections. As a learning exercise, you may want to create these tables yourself using the SSMS graphical user interface or with T-SQL commands. Alternatively, you can download the script using the link at the top of this article and run it to create the database and its fifteen tables.

CustomerAddresses Table

The CustomerAddresses table holds details of addresses. By holding the addresses in a separate table to the customers we can have multiple addresses for a single customer without unnecessary repetition of data. This is known as a "one-to-many" relationship.

Each address includes a reference to the customer number so that the two tables can later be linked together. The address also includes a region code that will link to the GeographicalAreas table. Each address includes a reference number.

Contracts Table

The Contracts table holds information relating to single jobs and annual contracts. Each contract is linked to a customer address; if a customer wishes to obtain an insurance policy for multiple premises, they will actually create several contracts. Recurring contracts include a renewal date and a flag that indicates if the customer wishes to automatically renew or whether contact must be made beforehand. Single jobs will hold a null value in the renewal columns. Both single jobs and contracts include a monetary value.

NB: The CustomerAddressId column will be used to provide a link to the CustomerAddresses table. Note that the name of the two matching columns need not be identical but that the data type should be.

Skills Table

The Skills table will be used to store the various skills that may be required of an engineer. A unique code and a name for each skill will be held.

StandardJobs Table

The JoBS system will hold a list of standard jobs that may be undertaken by engineers. For simplicity, custom jobs are not permitted; if an unexpected job must be undertaken it will be added to this table first. A standard job includes a unique reference number, a name for the job and a standard price to charge.

RequiredSkills Table

Each standard job will require one or more skills to complete successfully. In order that the correct engineer is despatched to do the work, the RequiredSkills table holds links between the StandardJobs and Skills tables, determining which skills are required. In a later article, we will create links between the tables to cement this "many-to-many" relationship.

Engineers Table

We need a table to hold a list of people that may be employed to undertake work on behalf of DisasterFix. This information will be stored in the Engineers table. The table will hold an optional photograph to be used on engineer ID cards.

NB: You would probably need to hold more engineer information than this in a real-world solution. In this case the table has been simplified for the purposes of the tutorial.

EngineerWorkingAreas Table

The EngineerWorkingAreas table will provide a many-to-many link between engineers and geographical areas. This information will determine which areas the engineer is willing to work in. This data is necessary when matching customer work to engineers.

EngineerSkills Table

In a similar manner to the EngineerWorkingAreas table, this table will provide a link between the engineers and the skills they have. This will be used to ensure that the engineer sent to a job is equipped to undertake the work.

Jobs Table

The Jobs table will hold information relating to all jobs that have been performed or that are booked as future work. This table is central to the JoBS application. Each job will be given a unique identifier. There will be links to the standard job type and the contract under which the work will take place. The contract number will be used to find the customer's address and other details.

Once a job has been booked with a customer, the engineer and the date of visit will be recorded. These columns are nullable because they may not be known when the job has been requested but not yet scheduled.

On completion of a job, the number of hours taken by the engineer will be recorded along with the charge rate for the time This information will be copied from the engineer's daily rate but will be stored in this table for historical purposes, as daily rates may change.

Sometimes a job will not be completed in a single visit. This may be due to a lack of parts or because the job takes longer than anticipated. In these cases the job will be marked as requiring a follow up. Once a follow up visit is booked as a new Job row in the table, a reference to the new JobId will be created so that the history of a job may be tracked.

Parts Table

The Parts table will hold the list of standard parts that may be used by an engineer. Each part has a cost that will be used when calculating the total cost of a job and the profit made. Parts that are not included in the list are not reimbursed by DisasterFix to the engineer.

UsedParts Table

In order for DisasterFix to determine their costs, the parts used during each job must be recorded. As multiple parts may be used for a single job, the UsedParts table will be used in a one-to-many relationship. Each row in this table links a part number to a job and records the unit cost of the part number and the number of units used in each case. The unit cost is recorded at the time of the job so that the historical data is not lost if the current cost changes. The number of units used is held as a fixed-point number to allow the engineer to specify that they used a fraction of a unit, for example 1.2 metres of copper piping.

EngineerStock Table

Each engineer is expected to hold a stock of parts that they will use during their day-to-day work. The current stock level for each part held will be stored in the final table so that replenishment can be made as required. This will also be used in a report that details the value of stock that an engineer has in their possession.