DATALENGTH and LEN difference between NUMERIC(16,6) and NUMERIC(18,2)

Question

DATALENGTH and LEN difference between NUMERIC(16,6) and NUMERIC(18,2)

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

It really does seem to be some efficiency mechanism in SQL to not use more than 5 bytes unless it's actually needed to store the specific value being saved.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Sergiy SSC Guru Points: 110206 More actions · Answer 2

It really does seem to be some efficiency mechanism in SQL to not use more than 5 bytes unless it's actually needed to store the specific value being saved

Not sure if it may be named "efficiency", considering the amount of extra CPU clocks required to read data of variable sizes, and the overhead created by page splits caused by a changed numeric value.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

Sergiy - Wednesday, September 5, 2018 5:48 PM
It really does seem to be some efficiency mechanism in SQL to not use more than 5 bytes unless it's actually needed to store the specific value being saved
Not sure if it may be named "efficiency", considering the amount of extra CPU clocks required to read data of variable sizes, and the overhead created by page splits caused by a changed numeric value.

SQL wouldn't do it if it weren't more efficient. Generally CPU is plentiful and waiting, while disk I/O is a scarce resource.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

Lynn Pettis - Wednesday, September 5, 2018 12:40 PM
You do realize that both 16,6 and 18,2 will use 9 bytes of storage in the database, right?

I ran this test to check.
DROP TABLE dbo.Numeric163_Low GO DROP TABLE dbo.Numeric163_High GO CREATE TABLE dbo.Numeric163_Low ( A numeric(18,2) NOT NULL, B numeric(18,2) NOT NULL, C numeric(18,2) NOT NULL, D numeric(18,2) NOT NULL, E numeric(18,2) NOT NULL, F numeric(18,2) NOT NULL, G numeric(18,2) NOT NULL, H numeric(18,2) NOT NULL, I numeric(18,2) NOT NULL, J numeric(18,2) NOT NULL, K numeric(18,2) NOT NULL, L numeric(18,2) NOT NULL, M numeric(18,2) NOT NULL, )

CREATE TABLE dbo.Numeric163_High ( A numeric(18,2) NOT NULL, B numeric(18,2) NOT NULL, C numeric(18,2) NOT NULL, D numeric(18,2) NOT NULL, E numeric(18,2) NOT NULL, F numeric(18,2) NOT NULL, G numeric(18,2) NOT NULL, H numeric(18,2) NOT NULL, I numeric(18,2) NOT NULL, J numeric(18,2) NOT NULL, K numeric(18,2) NOT NULL, L numeric(18,2) NOT NULL, M numeric(18,2) NOT NULL, )

INSERT INTO dbo.Numeric163_Low(A,B,C,D,E,F,G,H,I,J,K,L,M) SELECT TOP (10000000) 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95, 42949672.95 FROM tally a,tally b

INSERT INTO dbo.Numeric163_High(A,B,C,D,E,F,G,H,I,J,K,L,M) SELECT TOP (10000000) 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96, 42949672.96 FROM tally a,tally b
When I go into Reports\Standard\Disk Usage by Top Tables
I get this:

Which verifies your statement.