SQL 2005+

Modifying SQL Server Tables

by Richard Carr, published at http://www.blackwasp.co.uk/SQLModifyTables.aspx

The ninth part of the SQL Server Programming Fundamentals tutorial describes how database tables can be changed using T-SQL. In this article we will add, remove and modify columns, whilst considering the impact these operations can have on existing data.

Modifying Tables Using T-SQL

In previous articles in the SQL Server Programming Fundamentals tutorial we have seen how to create tables, and their constituent columns, and make modifications to their structure using the SQL Server Management Studio (SSMS) graphical user interface. During development, and for support purposes, SSMS is an ideal tool for such operations.

In this article we will consider the modification of existing tables using Transact-SQL statements, such as may be executed from a software application. This article describes simple changes, such as renaming tables and columns and changing column data types. The commands and techniques investigated here will also form the basis for future articles that explain how to make more detailed changes to tables, including the addition of primary keys, default values and constraints.

This article uses the JoBS database that has been created in earlier tutorial articles. If you have not followed the tutorial so far, download the script using the link at the top of this page and use it to create the database and sample data. You may also want to use this script to recreate the database if you have made modifications to your own installation. In this article we will make further changes to the JoBS schema, including some that will be incorporated into a new script to be used in the next article in the series.

Adding Columns to a Table

Many of the schema modifications that can be made to tables use the ALTER TABLE command. This command allows you to specify the name of the table to be changed and the action that you wish to perform. The first type of change that will be demonstrated is adding a new column to a table. In this case, the ALTER TABLE statement includes an ADD clause, followed by the new column specification.

NB: Column specifications are described in the earlier article, "Creating SQL Server Tables Part 2".

A new column can be added to an existing table that is empty or that contains information. When a table already holds data, the information will be updated so that the new column has a null value in each row. This means that the column specification must not specify that the column is not nullable. You can change the nullable status later, once the rows have all been updated to ensure that there are no nulls in the new column.

NB: Later in the tutorial we will create default values for columns. If a default value is specified for a new column, this value is added to every existing row and the NOT NULL clause for the new column is then valid.

Let's try the new statement by adding a new column to the StandardJobs table. This column will hold a description of the standard work that may be very long, so a VarChar(Max) column has been chosen. Execute the following statement, then review the table and its contents to see the changes.

ALTER TABLE StandardJobs ADD JobDescription VARCHAR(MAX)

Another missing piece of information in the JoBS database is the time that was required to complete a job. We can add a column to the Jobs table to hold this.

ALTER TABLE Jobs ADD Duration DECIMAL(4,2)

Modifying Existing Columns

Columns can be modified using a similar syntax to that used when adding new ones. Using the ALTER COLUMN clause allows the size or data type for a column to be modified and the status of the column to be changed between nullable and required. To alter the size of a column, the new column specification is provided, including the name and nullable status. For example, the following statement reduces the maximum size of the EngineerName column in the Engineers table from fifty to thirty characters.

ALTER TABLE Engineers ALTER COLUMN EngineerName VARCHAR(30) NOT NULL

If you attempt to make a column too small for the data that is already held in the table, the change is refused and an error is displayed. This prevents information from being truncated or accuracy being lost. For example, the following statement attempts to shorten the EngineerName column to a size that is too small:

ALTER TABLE Engineers ALTER COLUMN EngineerName VARCHAR(5) NOT NULL

The same syntax can be used to change the data type for a column. In the downloaded script, the Engineers table contains a column that holds the engineer's hourly charging rate. This column is defined as a Money type. To change this to a numeric column, the following statement can be used:

ALTER TABLE Engineers ALTER COLUMN HourlyRate NUMERIC(5,2) NOT NULL

When changing a column's data type, the new type must be compatible with the data within the table so that information is not lost. The next sample statement breaks this rule by trying to change a numeric value to a globally unique identifier.

ALTER TABLE Engineers ALTER COLUMN EngineerName UNIQUEIDENTIFIER NOT NULL

Finally, the nullable option for a column may be changed. To make a column nullable, the column definition should not include the NOT NULL clause. Optionally, you may include the NULL keyword, as in the next example, to improve the readability of the statement and ensure that the purpose is obvious.

ALTER TABLE Engineers ALTER COLUMN HourlyRate MONEY NULL

To change a nullable column so that it requires a value, add the NOT NULL clause. NB: This command will fail if the table contains rows where the column value is null.

ALTER TABLE Engineers ALTER COLUMN HourlyRate MONEY NOT NULL