Does increasing the size of an NVARCHAR column in SQL Server cause an index rebuild?

I have a database table that stores customer data with several million rows and need to change the lastname field from NVARCHAR (32) to NVARCHAR (50). The field has an index on it (non-clustered). I need to know if doing this change with ALTER TABLE ALTER COLUMN will automatically rebuild the index.

If so, I think it will take very little time. Our in-production upgrade process is through a released installer that runs scripts, and we need to give you an idea of ​​how long the upgrade will take. We have large DBs in production on SQL 2005 and 2008, so I need to know the answer for both if different.

I know that increasing the size of the columns should not change the data in a variable length field like NVARCHAR, and should be very fast even with large datasets in a situation where there is no index. I don't understand if the 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 changes with an existing index and not indicate what impact it might have on the execution time of the ALTER TABLE statement, or the index should be rebuilt or executed automatically after that etc.

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

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

SQL Server 2005 Index rebuild when varchar field size increases

Please let me know if you've had any experience with this. If I can get a test system built with a large DB where I can experiment, I will post the results.

Thank!

+3


source to share


1 answer


Not. Indexes will never be automatically rebuilt in SQL Server, even if you resize a column, such as the nvarchar column mentioned. Indexes are maintained automatically, but not rebuilt. Statistics / internal data histogram can be automatically updated if the DB automatic update option is set.



+3


source







All Articles