June 25, 2012 at 11:58 pm
Comments posted to this topic are about the item T-SQL Integer Data-Type Conversions
June 26, 2012 at 1:53 am
T-SQL type Int is storage-equivalent to VarBinary(4).
T-SQL type BigInt is storage-equivalent to VarBinary(8).
This is definitely not so, as varbinary require a VLB (Variable Length Block), whereas int and bigint does not. If they are storage-equivalent to something, that would be binary(4) and binary(8) respectively.
Furthermore, I do not appreciate casts to variable length data without specifying the max length, even though it in your cases implicitly means varchar(30).
Casting to varbinary does not convert it to hex, hex is a representation which may be used for displaying the data to the client.
June 26, 2012 at 5:40 am
OK, so this is going to show my ignorance/stupidity but the mentioning of Hex and varbinary is bringing up a topic that I've been confused about for a while. As I've understood it, binary represents 0's and 1's...so the byte for the letter "a" is represented as 01100001. Yet whenever you run a command in SQL server like:
declare @n as varchar(20) = 'abcdefg'
select CAST(@n as varbinary)
You get a result like:
0x61626364656667
Why does SQL Server spit the results out in this format rather than in straight binary, like this:
01100001 01100010 01100011 01100100 01100101 01100110 01100111
I apologize if this is a really stupid question.
Thanks,
George
June 26, 2012 at 7:57 am
As okbangas said, hex is used to represent the information. Your looking at the same thing:
a = 01100001 (binary) = 61 (hex) = 97 (decimal)
So you're looking at the binary data in hex format.
June 26, 2012 at 11:01 am
George H. (6/26/2012)
Why does SQL Server spit the results out in this format rather than in straight binary, like this:
01100001 01100010 01100011 01100100 01100101 01100110 01100111I apologize if this is a really stupid question.
Not a stupid question. Really there isn't a real reason, just convention and the fact that most people find base 16 (hex) easier to deal with than base 2, and not as bit-blind as base 10.
June 26, 2012 at 11:12 am
Thanks John...I always wondered about that.
George
June 26, 2012 at 11:15 am
okbangas (6/26/2012)
T-SQL type Int is storage-equivalent to VarBinary(4).
T-SQL type BigInt is storage-equivalent to VarBinary(8).
This is definitely not so, as varbinary require a VLB (Variable Length Block), whereas int and bigint does not. If they are storage-equivalent to something, that would be binary(4) and binary(8) respectively.
Furthermore, I do not appreciate casts to variable length data without specifying the max length, even though it in your cases implicitly means varchar(30).
Casting to varbinary does not convert it to hex, hex is a representation which may be used for displaying the data to the client.
Thank you okbangas for so ardently pointing out these minor details...
You are correct, I should have used Binary(4) and Binary(8) in my examples or have used the phrase "length-equivalent" instead of "storage-equivalent" in my final notes, however, the end results are the same.
Casting either Int or BigInt to VarChar will always work without truncation as the default VarChar length is 30 characters and neither Int nor BigInt could contain that many digits when represented as a VarChar data type.
Any time data is displayed to a user it is a representation of the internal storage type, i.e. binary. Casting simply changes the representation to that of the Cast's target type. Guess you missed my use of the term "representation" just before the Final Notes. Also, as one poster pointed out VarBinary seems misleading; maybe they should have called it VarHex 😉
I hope that okbangas's well-meaning academic adherence to principle didn't detract from the point of this article; simple ways to avoid byte-at-a-time conversions to hexadecimal representation.
Rate this anyway you please. I don't post for accolades, I post to help folks solve real-world problems using a woefully inadequate programming language (T-SQL).
June 26, 2012 at 1:18 pm
First of all, I am not an academic guy, but rather a practical guy who have started to dig into SQL Server. As for the comments.
I commented on varbinary not for academic reasons, but for practical. The academics (at least the ones I've spoken to) tell you to use variable length whenever the length is varying, I recommend to use only variable length when it results in reduced storage needed.
I dislike casting to varchar without length because it is a bad habit which does not always work, for instance it does not work for uniqueidentifers. Casting to a suitable length does always work.
As for varbinary, that is a perfect name. It is stored binary, now matter how you look at it. If you retrieve the data from for instance .NET Framework, it will be binary, and you'll have to convert it to hex yourself. It is SQL Server Management studio (or whatever other software you're using) which is representing the data as hex to you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply