SQL 2005+

Modifying SQL Server Tables (2)

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 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

Limitations

There are some limitations on the changes that may be made to columns. These are generally related to more advanced features than have been used so far in the tutorial, so do not affect the JoBS database in its current form. However, some of these limitations are worthy of consideration here.

• A column may not be modified if it uses the Timestamp data type. Text and NText columns may only be changed to VarChar(Max), NVarChar(Max) or XML types. Image columns may only be altered to VarBinary(Max).
• It is possible to define a UniqueIdentifier column as the ROWGUIDCOL for a table. This marks the column as being a special unique value for each row within the table. Such a column may not be modified.
• If a column is used within an index, the data type may not be changed. The size of such a column may be increased if the data type is VarChar, NVarChar or VarBinary.
• A column that is included within a primary key, foreign key relationship or in a constraint that enforces uniqueness or other conditions, may not be altered.
• If a column's data type is changed from NChar or NVarChar to Char or VarChar, any extended characters in its data may be converted to standard English characters.
• If a column's value is calculated or is used in the calculation of another column, the column type may not be changed.