Based on MSDN I see that numeric 10-19 using the same storage byte.
基于MSDN,我看到数字10-19使用相同的存储字节。
Is it mean that converting column from numeric (11,3) to numeric (18,3) will not use more storage space?
是否意味着将列从数字(11,3)转换为数字(18,3)不会使用更多的存储空间?
So, if it really like that, what are the risks? (Assuming that I need to do it for a 1 billion row table that may get numeric values that not fit 11,3).
那么,如果真的那样,那有什么风险呢? (假设我需要为10亿行表执行此操作,可能会得到不适合11,3的数值)。
1 个解决方案
#1
1
It appears that SQL Server will use the lower storage option first (5 bytes), before reaching the max for that precision (9 bytes). But both (11,3) and (18,3) max out at 9 bytes. See script below.
在达到该精度的最大值(9个字节)之前,SQL Server似乎将首先使用较低的存储选项(5个字节)。但是(11,3)和(18,3)最大值均为9个字节。见下面的脚本。
As for your conversion of 1 billion rows, since it will be a widening conversion I don't think you will have any problems. You should test this with as close to to he full data set as possible. I'm not sure how long this alter would actually take.
至于你转换10亿行,因为它将是一个扩大的转换,我不认为你会有任何问题。您应该尽可能接近完整数据集进行测试。我不确定这个改变实际需要多长时间。
USE tempdb
GO
CREATE TABLE MyTest (TestCol NUMERIC(11,3))
INSERT INTO dbo.MyTest ( TestCol ) VALUES( 99999999.999 )
INSERT INTO dbo.MyTest ( TestCol ) VALUES( 9.999 )
SELECT Testcol, DATALENGTH(Testcol) AS Size
FROM dbo.MyTest
CREATE TABLE MyTest2 (TestCol NUMERIC(18,3))
INSERT INTO dbo.MyTest2 ( TestCol ) VALUES( 999999999999999.999 )
INSERT INTO dbo.MyTest2 ( TestCol ) VALUES( 9.999 )
SELECT Testcol, DATALENGTH(Testcol) AS Size
FROM dbo.MyTest2
DROP TABLE dbo.MyTest
DROP TABLE dbo.MyTest2
#1
1
It appears that SQL Server will use the lower storage option first (5 bytes), before reaching the max for that precision (9 bytes). But both (11,3) and (18,3) max out at 9 bytes. See script below.
在达到该精度的最大值(9个字节)之前,SQL Server似乎将首先使用较低的存储选项(5个字节)。但是(11,3)和(18,3)最大值均为9个字节。见下面的脚本。
As for your conversion of 1 billion rows, since it will be a widening conversion I don't think you will have any problems. You should test this with as close to to he full data set as possible. I'm not sure how long this alter would actually take.
至于你转换10亿行,因为它将是一个扩大的转换,我不认为你会有任何问题。您应该尽可能接近完整数据集进行测试。我不确定这个改变实际需要多长时间。
USE tempdb
GO
CREATE TABLE MyTest (TestCol NUMERIC(11,3))
INSERT INTO dbo.MyTest ( TestCol ) VALUES( 99999999.999 )
INSERT INTO dbo.MyTest ( TestCol ) VALUES( 9.999 )
SELECT Testcol, DATALENGTH(Testcol) AS Size
FROM dbo.MyTest
CREATE TABLE MyTest2 (TestCol NUMERIC(18,3))
INSERT INTO dbo.MyTest2 ( TestCol ) VALUES( 999999999999999.999 )
INSERT INTO dbo.MyTest2 ( TestCol ) VALUES( 9.999 )
SELECT Testcol, DATALENGTH(Testcol) AS Size
FROM dbo.MyTest2
DROP TABLE dbo.MyTest
DROP TABLE dbo.MyTest2