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).
thank
source to share
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?
source to share