One of my friends had a problem altering a column data type and asked me for a solution. The problem statement is given below.
He had a table test defined as below
CREATE table test ( Col1 varchar(100), -- computed column col2 AS (col1) )
And he needed to alter the data type of col1. He tried below query but it failed.
ALTER table test alter COLUMN col1 nvarchar(100) Msg 5074, Level 16, State 1, Line 1 The column 'col2' is dependent on column 'col1'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.
It’s very much clear from the error and the table schema above, that col2 depends on col1 and thus col1 can’t be changed.
So the answer here is very clear too, drop the computed column first and then change the data type of col1 and then recreate the computed column. The script for the same is give below
-- Drop computed column ALTER table test drop COLUMN col2 GO -- Change datatype of col1 ALTER table test alter COLUMN col1 nvarchar(100) GO -- Add the Computed column ALTER table test add Col2 AS (Col1)
One thing to keep in mind is that the table won’t be accessible for DML queries when above statements are executed. So this complete activity should be planned carefully if the changes are to be done on production server.