SQL Server numeric data type precision size

Question

Based on MSDN I see that numeric 10-19 using the same storage byte.

Is it mean that converting column from numeric (11,3) to numeric (18,3) will not use more storage space?

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).

One risk is that you throw off some calculations in code because of the various casts involved. And the storage issue could ripple to persisted computed columns and indexes. — shawnt00
– shawnt00, Commented Jul 22, 2015 at 20:41
Hi Rich, first thanks for your answer, the example you added is showing that numeric 11,3 and numeric 18,3 using the same data size (while actually using this precision ). so what are the cons of using 18,3 vs 11,3 ? why not use ,be default, that maximum precision per storage group ? the only answer that I can think of is to enforce limitation of the data . — Yossi M
– Yossi M, Commented Jul 31, 2015 at 14:09
Limitation of the data is the only reason I can think of as well. — Rich Rousseau
– Rich Rousseau, Commented Aug 10, 2015 at 17:12

Rich Rousseau · Accepted Answer · 2015-07-22 20:35:03Z

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.

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.

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

Collectives™ on Stack Overflow

SQL Server numeric data type precision size

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related