Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Integer Data-Type Conversions


T-SQL Integer Data-Type Conversions

Author
Message
Peter E. Kierstead
Peter E. Kierstead
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 453
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
okbangas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1387
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
George H.
George H.
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 118
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9733 Visits: 18452
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
john.moreno
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 1112
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.
George H.
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 118
Thanks John...I always wondered about that.

George
Peter E. Kierstead
Peter E. Kierstead
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 453
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
okbangas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1387
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search