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 ?
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.