Certain errors are generated only by the alter table modify command. Although alter table modify converts columns to compatible datatypes, alter table may issue errors if the columns you are converting have certain restrictions.
Make sure you understand the implications of modifying
a datatype before you issue the command. Generally, use alter
table modify only to implicitly convert between convertible
datatypes. This ensures that any hidden conversions required during
processing of insert and update statements
do not fail because of datatype incompatibility.
For example, if you add a second_advance column to the titles table with a datatype of int, and create a clustered index on second_advance, you cannot then modify this column to a char datatype. This would cause the int values to be converted from integers (1, 2, 3) to strings (‘1’, ‘2’, ‘3’). When the index is rebuilt with sorted data, the data values are expected to be in sorted order. But in this example, the datatype has changed from int to char and is no longer in sorted order for the char datatype’s ordering sequence. So, the alter table command fails during the index rebuild phase.
Be very cautious when choosing a new datatype for columns that are part of index-key columns of clustered indexes. alter table modify must specify a target datatype that will not violate the ordering sequence of the modified data values after its data copy phase.
alter table modify also issues a warning message if you modify the datatype to an incompatible datatype in a column that contains a constraint. For example, if you try to modify from datatype char to datatype int, and the column includes a constraint, alter table modify issues this warning:
Warning: a rule or constraint is defined on column ‘new_col’ being modified. Verify the validity of rules and constraints after this ALTER TABLE operation.
The modify operation is flexible, but must be used with caution. In general, modifying to an implicitly convertible datatype works without errors. Modifying to an explicitly convertible datatype may lead to inconsistencies in the table schema. Use sp_depends to identify all column-level dependencies before modifying a column’s datatype.
