How to alter column of a table with indexes?

Question

I would like to change the column size of the a column in a table with some indexes from varchar(200) to varchar(8000). How should I proceed?

Answer 1

Since is VARCHAR and you're increasing the size, then simply ALTER TABLE ... ALTER COLUMN ... should be enough.

The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

Otherwise you would drop the index(es), alter the column, then add back the index(es).

Be aware though that SQL Server maximum index key size is 900 (or 1700 for newer editions), so even though the ALTER will succeed, a future INSERT of data over the 900 length limit will fail with error:

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length ... bytes for the index '...' exceeds the maximum length of 900 bytes.