SQL Server Alter Computed Columns

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.

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook