SQL Server——提高十进制精度/刻度的性能

I would like to know what is the impact of modifying a SQL Server decimal column.

我想知道修改SQL Server decimal列的影响是什么。

By example I have Decimal(12,2) column and I want to change it to Decimal(13,3)

比如我有Decimal(12,2)列我想把它改成Decimal(13,3)

Facts:

事实:

I won't loose any data (increasing the precision + scale won't throw any warning).
我不会丢失任何数据(增加精度+比例不会抛出任何警告)。
I'll still have a 9 bytes field. (See MSDN references: http://msdn.microsoft.com/en-us/library/ms187746.aspx)
我仍然有一个9字节的字段。(见MSDN引用:http://msdn.microsoft.com/en-us/library/ms187746.aspx)

Question 1: If I have over 10 millions or more row, how it will react?

问题1:如果我有超过1000万或更多的行，它会如何反应?

Question 2: Is it going to take a lot of time to upgrade the precision+scale or will it be instantaneous (I am talking about performance of the change being applied)?

问题2:升级精度+规模是否需要花费大量的时间，或者是即时的(我说的是正在应用的变更的性能)?

I actually tried on small table and it seems to be instantaneous, but I want to be sure before doing it on a huge table.

我在小桌子上试过，看起来是瞬间完成的，但我想在在大桌子上做之前先确认一下。

2 个解决方案

#1

The binary representation is different so it needs to perform a logged update to each row.

二进制表示形式不同，因此需要对每一行执行日志更新。

SELECT CAST(CAST(1 AS Decimal(12,2)) AS VARBINARY), 
       CAST(CAST(1 AS Decimal(13,3)) AS VARBINARY)

Returns

0x0C02000164000000
0x0D030001E8030000

#2

It certainly is not going to be instantaneous. I just tried on a table with 175 million rows of data in my test DB. The query has been running for the past 10 minutes and is still in progress.

它肯定不会是瞬间的。我刚刚在我的测试数据库中尝试了一个有1.75亿行数据的表。查询已运行了10分钟，目前仍在进行中。

Also, if there are any indexes on this column, you will have to drop and recreate those indexes, which will add to the total time required.

此外，如果本列上有任何索引，则必须删除并重新创建这些索引，这将增加所需的总时间。

Raj

拉吉

#1