How to alter a column and a computed column

Question

In SQL SERVER DB, I need to alter a column "baseColumn" and a computed column "upperBaseColumn". The upperBaseColumn has index on it.

This is how the table looks

create table testTable (baseColumn varchar(10), upperBaseColumn AS (upper(baseColumn))

create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Now I need to increase the column length of both the baseColumn and the upperBaseColumn.

Whats the best way to do it.

Thanks for the help guys!!!

Answer 1

I suggest you drop the index, then drop the computed column. Alter the size, then re-add the computed column and the index. Using your example....

create table testTable (baseColumn varchar(10), upperBaseColumn AS (upper(baseColumn)))
create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Drop Index TestTable.idxUpperBaseColumn

Alter Table testTable Drop Column upperBaseColumn

Alter Table testTable Alter Column baseColumn VarChar(20)

Alter Table testTable Add upperBaseColumn As Upper(BaseColumn)

create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Answer 2

The answer looks good. My only concern is if one of the script fails in between it will leave the database in the inconsistent state.

I'm not sure if that can happen though

Thanks a ton anyways