DATALENGTH and LEN difference between NUMERIC(16,6) and NUMERIC(18,2)
-
Due to one of our customers request in the past, we used to keep financial information across our product in a format of 16,6 (10 digits before and 6 after the decimal). Now I develop a new project within our product, which will not be used by that customer and for other requests, assigned a picture of 18,2 (16 digits before and 2 after the decimal). To my surprise I have found that LEN and DATALENGTH of 16,6 were 13 and 9 respectively, where
LEN and DATALENGTH of 18,2 - only 9 and 5.DECLARE @v_numeric NUMERIC(18,2)
DECLARE @v_numeric16 NUMERIC(16,6)SET @v_numeric = 123456.6557
SET @v_numeric16 = 123456.6557SELECT
LEN(@v_numeric) AS numeric18_length
,DATALENGTH(@v_numeric) AS numeric18_datalength
,LEN(@v_numeric16) AS numeric16_length
,DATALENGTH(@v_numeric16) AS numeric16_datalengthCan one help me understand why, please? I thought the storage will be 9 in both of the cases, no?
-
You are using the wrong measure to determine the number of bytes of storage. LEN and DATALENGTH are string functions. LEN does not include trailing spaces, which is why DATALENGTH exists, so that they can be counted. They do NOT measure the amount of data storage required. I would look up storage sizes in SQL Server for numeric data type.
EDIT: See the following link regarding data storage for numeric or decimal data types:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - BOR15K - Wednesday, September 5, 2018 6:50 AM
As sgmunson has said, LEN depends on the string length of the value. DATALENGTH is the amount of space used by the value in bytes.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics
log(4294967296)/log(2)=32
That should give you a clue.
It looks to me like the storage size will increase when the value requires it to be bigger and that it is stored as a positive binary integer with an extra byte to store the sign and maybe the precision. - Jonathan AC Roberts - Wednesday, September 5, 2018 9:56 AMBOR15K - Wednesday, September 5, 2018 6:50 AM
As sgmunson has said, LEN depends on the string length of the value. DATALENGTH is the amount of space used by the value in bytes.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumericslog(4294967296)/log(2)=32
That should give you a clue.
It looks to me like the storage size will increase when the value requires it to be bigger and that it is stored as a positive binary integer with an extra byte to store the sign and maybe the precision.I have took your sample step forward:
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16you will see 16,6 still bigger than 18,2
- BOR15K - Wednesday, September 5, 2018 10:40 AMJonathan AC Roberts - Wednesday, September 5, 2018 9:56 AMBOR15K - Wednesday, September 5, 2018 6:50 AM
As sgmunson has said, LEN depends on the string length of the value. DATALENGTH is the amount of space used by the value in bytes.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumericslog(4294967296)/log(2)=32
That should give you a clue.
It looks to me like the storage size will increase when the value requires it to be bigger and that it is stored as a positive binary integer with an extra byte to store the sign and maybe the precision.I have took your sample step forward:
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16you will see 16,6 still bigger than 18,2
That's because it's stored as an integer. So it will be something like:
If you put 12.34 as a (16,6) variable it will store it as 12340000 with something in the additional byte to say shift right by 6 places.
If you put 12.34 as a (18,2) variable it will store it as 1234 with something in the additional byte to say shift right by 2 places.
So the numbers to the right of the decimal place adds to the size of the variable. -
You do realize that both 16,6 and 18,2 will use 9 bytes of storage in the database, right?
- Lynn Pettis - Wednesday, September 5, 2018 12:40 PM
18,2 will only use 9 bytes if the number being stored is bigger than 42949672.95 or smaller than -42949672.95 as I have shown above, otherwise it uses 5.
- BOR15K - Wednesday, September 5, 2018 10:40 AMI have took your sample step forward:
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16you will see 16,6 still bigger than 18,2
You are still using the wrong measurement. Did you look at the link I provided? DATALENGTH and LEN are NOT data storage measuring functions. The amount of storage (in bytes) needed to store these values is shown on that page I posted in my EDIT to my post. LEN and DATALENGTH will only provide the number of characters in a string (and it's messy where nvarchar strings are concerned). When you use those functions on a string, I would not count on those results being accurate. The data type storage requirements for decimal and numeric are given on the page I linked to previously. Those numbers are what matter. In any case, it would appear that DATALENGTH will look at the storage bytes for decimal or numeric, and note that the page I linked to talks about the maximum value for the storage size, which means it CAN BE LESS. When you decrease the size of the fractional part of a number, you significantly reduce the number of bits required.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Jonathan AC Roberts - Wednesday, September 5, 2018 11:24 AM
It makes much more sense now, if this is the way the data stored.
- sgmunson - Wednesday, September 5, 2018 1:59 PMBOR15K - Wednesday, September 5, 2018 10:40 AMI have took your sample step forward:
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16you will see 16,6 still bigger than 18,2
You are still using the wrong measurement. Did you look at the link I provided? DATALENGTH and LEN are NOT data storage measuring functions. The amount of storage (in bytes) needed to store these values is shown on that page I posted in my EDIT to my post. LEN and DATALENGTH will only provide the number of characters in a string (and it's messy where nvarchar strings are concerned). When you use those functions on a string, I would not count on those results being accurate. The data type storage requirements for decimal and numeric are given on the page I linked to previously. Those numbers are what matter. In any case, it would appear that DATALENGTH will look at the storage bytes for decimal or numeric, and note that the page I linked to talks about the maximum value for the storage size, which means it CAN BE LESS. When you decrease the size of the fractional part of a number, you significantly reduce the number of bits required.
LEN provides the length data if it's converted to string. DATALENGTH provides the length in bytes of needed to store the value of the variable not the number of characters in a string. Of course there are other things needed when storing any column on a database. But the link you provided is nothing to do with DATALENGTH or LEN. It details the amount of storage to store the value in a numeric, which is exactly what I have shown in my answer. When you decrease the value on the left hand side of the decimal point you are storing in the column (keeping the column exactly the same type e.g. numeric (18,2) you decrease the amount of space needed to store it. That's why 42949672.96 needs more space to store it than 42949672.95 when stored in a NUMERIC(18,2) colunm.
-
If you actually take the time dig a little you will find the 9 bytes of space is used to store both 16,6 and 18,2 numeric data types. I would suggesting learning about the DBCC IND and DBCC PAGE undocumented commands. I am just learning about them myself even though I have worked with SQL Server for over 20 years. Interesting information you can find using them.
- Lynn Pettis - Wednesday, September 5, 2018 2:22 PM
Where do you learn about it if it's undocumented?
- Lynn Pettis - Wednesday, September 5, 2018 2:22 PM
I think if you would take a time to read my original question through, you would find a link, where it shows that the precision up to 19 consumes 9 bytes, so my starting point was that both 16.6 AND 18.2 should consume 9 bytes.
yet the test has shown me different, hence was my post. So no need to go personal - we all normally do our homework before publishing here anything. - Jonathan AC Roberts - Wednesday, September 5, 2018 2:26 PM
Google is your friend. SQLSkills.com and MSSQLTips.com have blogs that talk about them.
- BOR15K - Wednesday, September 5, 2018 2:30 PM
I didn't go personal. I simply stated that learning about the DBCC IND and DBCC PAGE commands is something worth learning. If that is getting personal, not my problem.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply