SQL Server 2005 index recovers when varchar field increases

I have 12 varchar (50) fields in a table of approximately 90 million rows that I need to grow to be varchar (100) long. Each of these fields has an index (only one field as a member). If I increase the length of the varchar fields, do the indexes either have to be rebuilt (or will be automatically rebuilt) or the statistics become outdated?

I do not have a reasonably sized test system benchmark (or perhaps I don’t know how to check if the indexes have been rebuilt or the statistics need to be recalculated).



source to share

1 answer

Statistics are based on what is in the columns, not their size, so your statistics will not be outdated.

Remember that sometimes when you change a column, SQL Server will add a new column to the table and drop the existing one, so it will have to rebuild the index anyway.

Despite searching, I cannot categorically say if the indexes need to be rebuilt, but I would rebuild independently (as this should be happening as part of your normal maintenance if this table is not read-only or very low write)

By the way, having a separate index on each of these columns may not be optimal. Have you profiled your workload?



All Articles