主页 / dba / 问题 / 90439
Accepted
nateirvin
Asked: 2015-01-29 10:01:11 +0800 CST

减小 varchar 列的大小会对数据库文件产生什么影响？

  • 772

我们的数据库中有许多表，VARCHAR(MAX)其中的列 a VARCHAR(500)（或比 max 小得多的东西）就足够了。自然地，我想清理这些，并将尺寸降低到更合理的水平。我理解的“如何”做到这一点：我的问题是改变这些列会对磁盘上​​的页面和现存文件有什么影响？（有很多关于当你增加一列时会发生什么的信息，但是很难找到关于当你缩小一个列时会发生什么的信息。）

有些表的行数非常少，所以我不担心更改的成本，但有些表很大，我担心它们可能会被重组并导致大量阻塞/停机时间。实际上，我只是想要一种估计维护窗口的方法。一般来说，我想更好地了解数据库引擎在这种情况下的行为方式。

提前致谢！

编辑：

我正在查看 20 个表，但其中只有一半的行数大于 1,000。最大的有近一百万行。VARCHAR(MAX)最严重的违规者是可以缩小到水平的具有 350,000 行和 4 列的表VARCHAR(500)。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    首先要做的事情是：表中有多少数据？表格的行数和大小？

    第二：你能把这张表备份并恢复到一个测试服务器，然后运行alter语句看看影响吗（假设它不是不可行的，因为表太大而不能放在非生产系统上）？我总是发现在我的环境中进行测试比来自互联网的建议更准确，因为有几个因素可能会影响结果，而这些因素可能不会仅仅因为不知道这些因素会影响结果而在问题中提供。

    第三：增加可变长度字段的大小是（假设您没有超过 8060 字节限制）一个简单的元数据操作，因为这样的操作不会改变实际数据。但是，另一方面，减小可变长度字段的大小，即使是非常明显的工作，也不是简单的元数据更改，因为 SQL Server 在扫描所有行之前不知道，新请求的大小是有效的。

    因此：是的，这将锁定表一段时间。多少时间？好吧，这是我刚刚做的测试：

    通过其他一些测试，我有一个包含单个INT NOT NULL字段和 100 万行的表。我将它复制到一个新表中，以便通过以下方式进行此测试：

    SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

    这样，我从一个类似的场景开始MAX（我刚刚意识到你有VARCHAR并且我正在使用NVARCHAR，但这不应该改变我看到的行为），然后我可以更改为500. 它的数据可以轻松容纳在 500 个字符以内。那花了几分钟。

    然后我跑了：

    ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

    这只用了 11 分钟多一点。

    我只是再次重新运行测试，这次放下[ResizeTest]桌子并将两个NVARCHARs 更改为 just VARCHAR，只是为了非常确定我正在将苹果与至少看起来像苹果的东西进行比较;-)。

    初始表创建需要 20 秒，而创建ALTER TABLE需要 2 分钟。

    因此，就估计停机时间而言，这真的很难做到，因为它基于磁盘 I/O 速度，是否需要对数据文件和/或事务日志等进行任何自动增长操作。可能是为什么我的第一个测试需要 11 分钟来更改而第二个测试，即使VARCHAR是数据大小的一半NVARCHAR，也只需要 2 分钟（即文件在那时预先增长）。但是，您仍然应该记住，我的测试是在我的笔记本电脑上运行的，这不是最快的磁盘，但它也只有 100 万行 2 个小列（每行 22 个左右字节）。

    既然你问它将对数据页做什么，这就是你的答案。我sp_spaceused在创建表格之后，在做之后，在做ALTER COLUMN之后做了ALTER TABLE dbo.ResizeTest REBUILD;。结果（以下数字基于使用 的第二次测试VARCHAR，而不是使用 的第一次测试NVARCHAR）：

    After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

    如果您担心需要将操作保持在尽可能短的时间内，请查看我写的一篇关于这样做的文章：在几秒钟内重组 1 亿行（或更多）表。SRSLY！（需要免费注册）。

    • 12

  2. 从我收集到的信息来看，运行 alter 语句应该不会花费很长时间，只要表上没有被另一个进程锁定。根据 gbn，这只是元数据更改：https ://stackoverflow.com/questions/7261909/is-it-bad-to-use-alter-table-to-resize-a-varchar-column-to-a-larger -尺寸

    此外，关于它的存储方式，SQL Server 似乎将 varchar 数据存储在一个 8k 页面中，直到它填满整个页面，此时它用指针替换它并将其存储为 BLOB。

    我假设当您更改长度时，您不会截断任何记录。如果是这样，那么您要转换为 varchar(500) 的数据最多应该是 502 字节长并且不应该有指针。

    因此，长话短说，只要您不截断任何数据，就不会发生太大变化。

    • 0

相关问题