SQL 2005+

Creating SQL Server Tables Part 2 (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.

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.