How Binary Data is represented in SQL database

Question

I have a field named X where the fieldtype is Binary(15). Now I am updating the table with

Update Table_name 
Set X = 567845329090989
Where some_condition

Now after executing the above command in the table while viewing I am seeing that the column is updated with the following

0x0000000F000001ADB920CB73040200

This is not Hexadecimal value also. How is this working?

What are you actually trying to do? You would use 0x567845329090989 if you wanted it treated as literal binary data. — Martin Smith
– Martin Smith, Commented Mar 17, 2011 at 12:42

Community · Accepted Answer · 2017-05-23 10:33:09Z

3

567845329090989 is ~~bigint~~ decimal(15,0)
using Windows calculator, this is 0x20473CB20B9AD
the value above is 0x0000000F000001 ADB920CB730402 00

I forget exact details but it's to do with endianess (SO)

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Mar 17, 2011 at 12:36

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JavaBits Over a year ago

Thanks but What does Binary (15) exactly means? 15 Byte space of data?

Mikael Eriksson · Accepted Answer · 2011-03-17 13:00:46Z

2

From comment by @gbn. The constant 567845329090989 is read as decimal(15,0) not bigint.

declare @T table (X binary(15))

declare @X bigint = 567845329090989
declare @Y decimal(15,0) = 567845329090989

insert into @T values (@X)
insert into @T values (567845329090989)
insert into @T values (@Y)

select *
from @T

Result

X
--------------------------------
0x0000000000000000020473CB20B9AD
0x0000000F000001ADB920CB73040200
0x0000000F000001ADB920CB73040200

edited Mar 17, 2011 at 13:00

answered Mar 17, 2011 at 12:41

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

2 Comments

Martin Smith Over a year ago

Interesting. The one using a constant gets compiled into the plan as [Expr1004] = Scalar Operator(0x0000000F000001ADB920CB73040200) The one using a variable is [Expr1004] = Scalar Operator(CONVERT_IMPLICIT(binary(15),[@X],0)) Not obvious to me why the one with the constant should be treated differently. Ah gbn's update about decimal(15,0) explains it.

gbn Over a year ago

Thanks for reminding me. 567845329090989 is read as decimal(15,0) which converts differently to bigint

Collectives™ on Stack Overflow

How Binary Data is represented in SQL database

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related