The Binary Cast

Question

The Binary Cast

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
July 27, 2020 at 12:00 am

#3769528

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

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

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

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

_____________
Code for TallyGenerator

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 2

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Stewart "Arturius" Campbell SSC Guru Points: 72485 More actions · Answer 3

Really interesting question, thanks Steve

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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