1

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 ?

  • 1
    What do you mean "do you need to"? The database should maintain the consistency of the indexes for operations performed in the database. – Gordon Linoff Nov 10 '16 at 15:35
  • 3
    No, you don't need to rebuild, and Gordon didn't say that at all. If a rebuild is required (which is only if data actually needs to be rewritten), the server will do it regardless as part of the ALTER. – Jeroen Mostert Nov 10 '16 at 15:41
  • i have change the length of a column form 10 to 30 varchar. if i don not rebuild index, what will happen to the index if i do updates or inserts on the altered column with 30 character length data. – Yogi Nov 10 '16 at 16:00
  • Nothing will happen, it will be fine. – Ben Nov 10 '16 at 16:04
  • 1
    Then those rows will be updated. If the page they're in is too small to hold them, the page will be split. This is what always happens if you make a row larger, regardless of how it's indexed. If your column had been a CHAR, then all rows would have changed, and the index would have been rebuilt. But changing a variable length column to have a greater size does nothing (initially) -- it was already variable. – Jeroen Mostert Nov 10 '16 at 16:04
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.