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.

What's the best way to do it?

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)