Modifying the Data Type of a column having data

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,

 Dropping constraint:

           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.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s