3

I have a database table that holds customer data with several million rows in it, and need do modify the lastname field from NVARCHAR(32) to NVARCHAR(50). The field has an index on it (non-clustered). I need to know whether making this change with an ALTER TABLE ALTER COLUMN statement will automatically rebuild the index.

If so, I think that would take quite a bit of time. Our upgrade process in production is done via a released installer that runs scripts, and we need to give an expectation of how long the upgrade will take. We have sizable DBs in production on both SQL 2005 and 2008, so I need to know the answer for both if it's different.

I know that increasing the size of the columns shouldn't modify the data in a variable length field like NVARCHAR, and should be very quick even on large data sets in a situation where there's no index involved. What I'm not clear on is whether an index will cause this to be much slower.

I read the documentation at the link below, but it really just indicates that you can make the change with an existing index, and doesn't specify what impact that might have to the execution time of the ALTER TABLE statement, or whether the index has to be rebuilt afterwards, or is done automatically, etc.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

The question at the link below is really almost the same thing I'm asking here, but there's no satisfactory answer on this other post, so I thought I'd try a new post.

SQL Server 2005 Index rebuild when increasing the size of a varchar field

Please let me know if you have had experience with this. If I can get a test system set up with a sizable DB where I can experiment, I'll post the results.

Thanks!

Community
  • 1
  • 1
Jim
  • 6,753
  • 12
  • 44
  • 72
  • I don't believe the index needs to be rebuilt if all you've done is increase the size. Unless you increase the size *and* have `NULL`s in the table now *and* change the definition to `NOT NULL` *and* add a default value that is larger than the old size, no row will change, and therefore no data in the index will change. But you can certainly test this *without* creating a "sizable DB"... – Aaron Bertrand Mar 29 '12 at 15:26
  • Ok, cool. None of those things you mentioned apply to my situation; no NOT NULL constraints or DEFAULT values defined on the columns I need to modify. I think what I'll do to test for sure if statement execution time is affected is create two tables, each with a VARCHAR(50) column that can be populated with a simple script. I can put an index on one table and no index on the other, and time how long it takes to alter the columns to VARCHAR(100) in for each table when they have identical data and the only difference is the index. – Jim Mar 29 '12 at 15:48
  • Querying sys.dm_db_index_physical_stats before and after the change might also show if significant changes have been made. – Philip Kelley Mar 29 '12 at 17:06

1 Answers1

3

No - Indexes are never automatically rebuilt in SQL Server, even when you change a column size such as the nvarchar column mentioned. Indexes are maintained automatically but not rebuilt. Statistics / internal histogram of the data may be automatically updated if the DB Option 'auto update statistics' is set.

Richard Ouimet
  • 476
  • 3
  • 6