Casting binary to bit

Question

Based on the recent question.

Can someone point me to explanation of the following?

If I cast binary(4) constant 0x80000000 to int, take the resulting value and cast it to bit type, the result is 1.

select cast(0x80000000 as int) --> -2147483648
select cast(-2147483648 as bit) --> 1

But if I cast 0x80000000 to bit type directly the result is 0.

select cast(0x80000000 as bit) --> 0

I hoped to get 1 in this case as well, thinkning that probably this expression equivalent to

select cast(cast(0x80000000 as binary(1)) as bit)

but this is not the case. Instead, it seems that the highest byte of the binary constant is taken and converted to bit. So, effectively it is something like

select cast(cast(right(0x80000000, 1) as binary(1)) as bit)

I'm clear with first binary -> int -> bit part. What I'm not clear with is the second binary -> bit part. I was not able to find this behavior explained in the documentation, where only

Converting to bit promotes any nonzero value to 1.

is stated.

Answer 1

binary is not a number, it's a string of bytes. When you cast binary to another type, a conversion is performed. When binary is longer than the target data-type, it is truncated from the left. When it's shorter than the target, it is padded with zeroes from the left. The exception is when casting to another string type (e.g. varchar or another binary) - there it's padding and truncation from the right, which may be a bit confusing at first :)

So what happens here?

select cast(cast(0x0F as binary(1)) as bit) -- 1 - 0x0F is nonzero
select cast(cast(0x01 as binary(1)) as bit) -- 1 - 0x01 is nonzero
select cast(cast(0x01 as binary(2)) as bit) -- 0 - truncated to 0x00, which is zero
select cast(cast(0x0100 as binary(2)) as bit) -- 0 - truncated to 0x00
select cast(cast(0x0001 as binary(2)) as bit) -- 1 - truncated to 0x01, nonzero

As the documentation says:

When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.

Which is something you can use, because:

select cast(0x0100 as binary(1)) -- 0x01

So if you need non-zero on the whole value, you basically need to convert to an integer data type, if possible. If you want the rightmost byte, use cast as bit, and if you want the leftmost, use cast as binary(1). Any other can be reached by using the string manipulation functions (binary is a string, just not a string of characters). binary doesn't allow you to do something like 0x01000 = 0 - that includes an implicit conversion to int (in this case), so the usual rules apply - 0x0100000000 = 0 is true.

Also note that there are no guarantees that conversions from binary are consistent between SQL server versions - they're not really managed.

Answer 2

Yes, in general when converting from an arbitrary length binary or varbinary value to a fixed size type, it's the rightmost bits or bytes that are converted:

select
CAST(CAST(0x0102030405060708 as bigint) as varbinary(8)),
CAST(CAST(0x0102030405060708 as int) as varbinary(8)),
CAST(CAST(0x0102030405060708 as smallint) as varbinary(8)),
CAST(CAST(0x0102030405060708 as tinyint) as varbinary(8))

Produces:

------------------ ------------------ ------------------ ------------------
0x0102030405060708 0x05060708         0x0708             0x08

I can't actually find anywhere in the documentation that specifically states this, but there again, the documentation does basically state that conversions between binary and other types is not guaranteed to follow any specific conventions:

Converting any value of any type to a binary value of large enough size and then back to the type, will always result in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.

So, the above shown conversions were the "expected" results running on SQL Server 2012, on my machine, but others may get different results.