How to change column and calculated column

Question

In SQL SERVER DB, I need to change the column baseColumnand the computed column upperBaseColumn. upperBaseColumnhas an index on it.

This is what the table looks like:

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

create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Now I need to increase the length of the column as baseColumnwell as upperBaseColumn.

What is the best way to do this?

Answer 1

I suggest you drop the index and then discard the computed column. Resize, then re-add the computed column and 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)