The Binary Cast

• Comments posted to this topic are about the item The Binary Cast

• Numeric values take at least 5 bytes: starting with 4 bytes  of metadata (defining the numeric type) followed by as many as necessary bytes of the integer representation of the number (with the decimal dot out of the picture).

select cast ( CAST (126 as NUMERIC(10,4)) as varbinary(10))

returns

0x0A040001E0391300

It's easy to see here that 0A defines 10, 04 defines "4" in the type definition, not sure what following "0001" stand for, must be the data type itself.

Following 4 bytes are the binary representation of the corresponding integer value in reverse order.

Reversing 0xE0391300 --> 001339E0,

select CAST (0x001339E0 as int)

returns 1260000

Placing the dot after 4 digits from the right gives the expected result.

Zero bytes at the end of the binary representation of a numeric value are optional:

SELECT CAST(0x0A040001E03913 AS NUMERIC(10,4))

Still returns the expected result 126.0000

• Just to add to Sergiy's response - the "01" indicates that the value is positive.  If the value was -126, then you'd get "00" in that octet instead.  the "00" prior to the sign bit, I am not sure what it is for.  Doing a quick bit of random testing (putting other values on for the 00 and 01) I found:

- the value of that 00 doesn't matter.  you can put in 00, 01, ff and anything in between (it seems... I only tested a small sample size) and it makes no difference

• the value of 01 only matters if it is 00 (ie negative) or not 00 (positive).  If you put in 02, 03, ff or anything in between (it seems... i only tested a small sample size) it made no difference

A good write-up on these datatypes was done by Randolph West at - https://bornsql.ca/blog/how-sql-server-stores-data-types-integers-and-decimals/

He also did one on the money datatype which is a good read.

• Really interesting question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

• Great question, Steve!

I have tested a few things and theoretically your expression should have worked as it is a combination of the following 2 expressions that work

SELECT CAST(0x7e AS Int)  -- that returns 126

SELECT CAST(126 AS NUMERIC(10,4))

Implicit conversion to numeric works too

DECLARE @var as NUMERIC(10,4)

SET @var = CAST(0x7e AS Int)

SELECT @var   --  that returns 126.0000

Regards,Yelena Varsha

Viewing 5 posts - 1 through 5 (of 5 total)