Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL Integer Data-Type Conversions Expand / Collapse
Author
Message
Posted Monday, June 25, 2012 11:58 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 190, Visits: 442
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.
Post #1321024
Posted Tuesday, June 26, 2012 1:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:19 AM
Points: 846, Visits: 1,381
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
Post #1321071
Posted Tuesday, June 26, 2012 5:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 4, 2013 5:21 AM
Points: 52, Visits: 117
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
Post #1321159
Posted Tuesday, June 26, 2012 7:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 3,919, Visits: 8,896
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1321249
Posted Tuesday, June 26, 2012 11:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:12 PM
Points: 367, Visits: 1,080
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.
Post #1321408
Posted Tuesday, June 26, 2012 11:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 4, 2013 5:21 AM
Points: 52, Visits: 117
Thanks John...I always wondered about that.

George
Post #1321421
Posted Tuesday, June 26, 2012 11:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 190, Visits: 442
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.
Post #1321426
Posted Tuesday, June 26, 2012 1:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:19 AM
Points: 846, Visits: 1,381
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
Post #1321501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse