Sql-server – Do i need to rebuild the indexes on the table after changing column length on SQL table

sql server

I have increased the length of a column using alter table statement, there are 3 non clustered and 1 clustered index on the table. Now do i need to rebuild the indexes on the table ?

Best Answer

As far as I know you won't run into issues with expanding the length of a VARCHAR column. However, there are certain types of datatype changes that do you require you to drop and recreate indexes. Perhaps you are thinking of those?

This is something that's fairly straightforward to test. You don't even need data in your test table. For example:

CREATE TABLE #X_TEST_IX (COLUMN1 VARCHAR(100) NOT NULL, PRIMARY KEY (COLUMN1));

-- runs without an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 VARCHAR(200) NOT NULL;

-- throws an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 INTEGER NOT NULL;

The error thrown by the last statement is:

Msg 5074, Level 16, State 1, Line 19 The object 'PK__#X_TEST___7116E265BDC9D4B5' is dependent on column 'COLUMN1'

Msg 4922, Level 16, State 9, Line 19 ALTER TABLE ALTER COLUMN COLUMN1 failed because one or more objects access this column.