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: 101098 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110208 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: 101098 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 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.