BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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:

column-name data-type [NULL | NOT NULL]

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.

USE JoBS

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
20 July 2008