Casting and Data Type Conversion

  • Jason Whitish

    SSChasing Mays

    Points: 642

    Comments posted to this topic are about the item Casting and Data Type Conversion

  • Iulian -207023


    Points: 7509

    Nice to know, I would have expected to return an overflow.

    Thank you for the question.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Very interesting question, thanks.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Great question, thanks for sharing


  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286983

    Nice question to get the brain moving in the morning. Thanks.

  • Raghavendra Mudugal


    Points: 10658

    thank you for the post, interesting one.

    this is all due to the "negative" sign... remove that sign, all we get is 2, as the tinyint stores only 0 to 255 (positive numbers) the actual varbinary which is holding 2 with the sign "0xFFFFFFFE".... something happens in sql and it gives 254 as output.

    (I guess.. this is the best stupid explanation from side.... 😀 😛 )

    ww; Raghu
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Mike Hays


    Points: 1871

    Wowwwww! I need to read up on my varbinary...

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    A very odd statement in the explanation though: "Tinyint is only 2 bytes". No it isn't, it's only 1 byte. Perhaps it meant to say "only 2 quartets"?


  • Dana Medley


    Points: 6764

    Thanks for the question. I learned something today.

    Everything is awesome!

  • Mark Fitzgerald-331224

    SSCrazy Eights

    Points: 8489

    This is two's complement numbers being used.

    When converting down from varbinary to an integer data type you only get the last (least significant) byte. In this case the least significant bits are 1111 1110.

    In tinyint with would equate to the decimal values : 128,64,32,16,8,4,2 => 254

    With the signed SQL integer types then the most significant value is a negative of what you would expect. So for a smallint (2 bytes) the values are : -32768, 16384,8192,4096,2048,1024,512,256,128,64,32,16,8,4,2,1. In this format :


    0000000000000000 -> 0

    1000000000000000 -> -32768

    0111111111111111 -> 32767

    1111111111111111 -> -1's_complement


  • Thomas Abraham


    Points: 10761

    Love Love Love the question. When casting and converting, I think we sometimes focus more on what data types can be converted to which other types, and don't think about how the length of the datatype in storage will affect the actual value stored. This question points it out very well. Bravo!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • stephen.long.1


    Points: 2577

    Great question, Jason. Thanks!


    SSC Guru

    Points: 281243

    Interesting question.

    It seems this would not be an issue if tinyint supported signed values.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Revenant


    Points: 42467

    This is definitely off the beaten path. Thanks, Jason!

Viewing 15 posts - 1 through 15 (of 24 total)

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