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