T-SQL Integer Data-Type Conversions

  • Peter E. Kierstead

    SSCarpal Tunnel

    Points: 4114

    Comments posted to this topic are about the item T-SQL Integer Data-Type Conversions



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • okbangas

    SSChampion

    Points: 11773

    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.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • George H.

    Default port

    Points: 1462

    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

  • Luis Cazares

    SSC Guru

    Points: 183583

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • john.moreno

    Default port

    Points: 1485

    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 01100111

    I 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.

  • George H.

    Default port

    Points: 1462

    Thanks John...I always wondered about that.

    George

  • Peter E. Kierstead

    SSCarpal Tunnel

    Points: 4114

    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).



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • okbangas

    SSChampion

    Points: 11773

    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.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

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

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