增加varchar字段的大小时，SQL Server 2005索引会重建 [英] SQL Server 2005 Index rebuild when increasing the size of a varchar field

问题描述

我在一个大约9000万行的表中有12个varchar（50）字段，我需要将其增加到varchar（100）的长度。每个字段都有一个索引（只有一个字段作为成员）。如果我增加varchar字段的长度，是否需要重建索引（或者是否会自动重建索引），或者统计信息是否会过期？

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

我没有一个合理大小的测试系统测试（或者可能不知道如何查看索引是否重建或统计数据是否需要重新计算）。

I don't have a reasonably sized test system test (or perhaps dont know how to see if the indexes were rebuilt or statistics need to be recomputed).

谢谢

推荐答案

统计数据基于列中的内容，而不是其大小，因此您的统计信息不会过时。

Statistics are based on what is in columns, not their size, so your statistics won't become out of date.

请记住，有时当您更改列时，SQL服务器会向表中添加一个新列并删除现有列，因此在这种情况下它必须重建索引无论如何。

Bear in mind that sometimes when you alter 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 in that case anyway.

尽管搜索我不能断然说你是否必须重建索引，但我会重建（因为这应该是正常维护的一部分，假设此表不是只读或写入非常低）

Despite searching I can't categorically say whether you have to rebuild indexes, but I would rebuild regardless (as this should be occuring as part of your normal maintenance, assuming this table is not read-only or very low writes)

BTW ，在每个列上都有一个单独的索引可能不是最佳的。您是否已分析过查询工作量？

BTW, having a seperate index on each of those columns might not be optimal. Have you profiled your query workload?

这篇关于增加varchar字段的大小时，SQL Server 2005索引会重建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！