sql - 在批量数据上执行时，使用ALTER提高列的精度是否会影响数据库性能？

Question

我在一个实时数据库中有一个SQL表，该表具有超过600万行，并且我想提高特定列的精度:

ALTER TABLE sales ALTER COLUMN amount DECIMAL(8,4)

现在我的问题是，如果我执行上述查询，SQL Server将重新计算每个单元格并将新值写回到同一单元格中吗？ (如果是，那么这肯定会花很长时间才能执行，而我们在数据库上的其他事件也会受到影响。)或者，该语句是否会以其他方式执行？

Answer 1

快速纠正
decimal(8, 2)到decimal(8, 4)的转换实际上并没有提高精度；它正在扩大规模。

从decimal/numeric - TSQL:

精度可以存储的最大十进制数字总数，在小数点的左边和右边。

标度可以存储在小数点右边的最大小数位数。小数位数必须是0到p之间的值。

因此，总位数(以及因此的存储要求)没有改​​变。

答案

如果您的值超过 9999.9999 ，那么您将遇到算术溢出错误的糟糕情况。为了容纳所有可能的(8，2)值，您需要将列增加为 decimal(10, 4)。

但是，这将增加您的列的存储要求，从5个字节增加到9个字节。结果，就其对可用性和事务日志的影响而言，这等效于 update语句。

根据测试发现，至少对于SQL Server 2008R2，如果存储需求发生变化，提高的精度只会导致数据更新。本质上，如果列的新精度值与上一个相同(或较小，并且不会发生截断)，在大小类别中，则表数据保持不变。

对于600万行表，对事务日志的影响约为2.5GB。它不一定会以这个数量增长，但这就是它将消耗多少空间。我的测试使用了大约200万行，并且 alter语句使日志从1MB增长到了约850MB。

至于对性能的影响(需要多长时间)，如果不了解服务器硬件和负载的任何信息，就无法说了。如果您足够担心要避免就地修改表，则最好的方法可能是表交换:

使用所需的架构创建一个新表( sales_tmp)，然后复制数据:

insert sales_tmp
select * from sales;

如果可以确保在操作过程中不修改 sales表，则不必担心通过事务和锁来阻止它。否则， repeatable read事务就足够了，并且至少在操作期间不会阻止对 sales表的读取。

然后:

复制新表上的权限

重新创建索引和外键引用

删除旧表

重命名新的(sales_tmp到sales)

如果在受影响的表上设置了复制或其他奇特的设置，这可能会出现一些问题。遗憾的是，禁用和重新启用这些功能并非易事。

交易记录

如果您担心更新600万条记录对事务日志的影响，则需要分批更新记录。大小视您的需求而定。我建议1,000-10,000。

IMO，除非您的数据库服务器确实受到可用空间的限制，否则我真的不需要担心这一点，但是此信息可能对将来有用。

如果您的恢复模型是SIMPLE，则日志包含也是如此。如果它是FULL，那么事情就更难了。

尽管Blam在回答中提出了什么要求，但是他的循环实现绝对不能保证任何包含事务日志文件的内容。

SIMIPLE恢复

在循环主体的末尾发出 checkpoint语句，以确保刷新日志数据。无论如何，SQL Server都会定期执行此操作，在大多数情况下您不会注意到，但在某些情况下您会注意到。以下是高度简化的伪代码:

while @rows_left > 0
begin
    -- update/copy rows
    checkpoint;
end

满血复活

这很棘手，因为在您进行日志备份之前，日志会无限增长，这会将日志页面标记为非事件状态，并使SQL Server可以重新使用已为日志文件分配的空间。除了推荐一些资源外，我将不做详细介绍:

Managing the Transaction Log

BACKUP (TSQL)