Some times we need to modify/broaden the data type of a data column which at the time of creation we thought of having small range/values. But later on, there is demand for that column to accomodate the larger range of values.
There can be some points which need to be looked into before modifying any data type:
1. Is there any constraint defined on that column, like Primary key constraint etc?
2. is that column being referenced by any other table(column) ?
3. are we going to boraden or narrowing the data type?
Steps for modifying the data type:
a . If there are no constraint then we can just modify the data type, using ALTER T-SQL statement:
ALTER Table TableName
ALTER COLUMN ColName NewDataType
If there is any contraint existing on this column, then first that constraint must be dropped off. Then using above T-SQL statement, data type can be modified. And at last, constraint can be re-created,
ALTER TABLE TableName DROP CONSTRAINT ConstraintName
b. If the column is being referenced by any other column, first that referencial constraint should be dropped, then modify the data type of the parent column, then modify the data type of the column being referenced to the same data type and then create the required reference again.
Keep in mind that both the columns must have the same data type.
c. we can broaden the data type but we cann’t narrow it if the existing data is out of the range of new data type.
Note: If we do modify the data type using the SSMS wizard, then it will automatically handle all these operations like dropping constraints, rebuilding them & modifying the data type of the referenced tables etc. It will display the warrning messages doing so.