Using the ALTER TABLE Statement to Change to Width or Data Type of a Column

Unlike many DBMS products, MS-SQL Server allows you to change not only the width of a column but also its data type. There are, however, a few restrictions on data type changes. You cannot change the data type of a column if the column:

Is of type TEXT, IMAGE, NTEXT, or TIMESTAMP

Is part of an index, unless the original data type is VARCHAR or VARBINARY and you are not changing the original data type or making the column width shorter

Is part of a PRIMARY KEY or FOREIGN KEY

Is used in a CHECK constraint

Is used in a UNIQUE constraint

Has a default associate with it

Is replicated

Is computed or used in a computed column

When changing a column's data type, all of the existing data in the column must be compatible with the new data type. As such, you can always convert from INTEGER to character since a CHARACTER column can hold numeric digits, letters, and special symbols. However, when converting a CHARACTER column to INTEGER, you must ensure that every row of the table has numeric digits or NULL in the CHARACTER field you are converting.

Once you've identified the column as one whose type you can change, use the ALTER TABLE statement in the form

ALTER TABLE <table name>

ALTER COLUMN <column name> <new data type>

to change the width or data type of a column. For example, if you have an EMPLOYEE table defined by

CREATE TABLE employee

(employee_id INTEGER,

first_name VARCHAR(20),

last_name VARCHAR(30),

social_security_number CHAR(11),

street_address CHAR(30),

health_card_number CHAR(15),

sheriff_card_number CHAR(15),

PRIMARY KEY (employee_id))

you can use the ALTER TABLE statement

ALTER TABLE employee

ALTER COLUMN street_address CHAR(35)

to change the width of the STREET_ADDRESS column from CHAR(30) to CHAR(35).

You can also use the ALTER TABLE statement to change a column's data type. For example, to change the HEALTH_CARD_NUMBER from CHAR(15) to INTEGER, use the ALTER TABLE statement:

ALTER TABLE employee

ALTER COLUMN health_card_number INTEGER

When converting a column from one data type to another, remember that all of the existing data in the column must be compatible with the new data type. Thus, the conversion of HEALTH_CARD_NUMBER from character to integer will work only if all of the current health card numbers are NULL or if they are all composed of only digits. As such, if any health card number includes a non-numeric character, the ALTER TABLE statement converting the column's data type from CHARACTER to INTEGER will fail.