none
SQL Server data type for signed/unsigned integral c# types RRS feed

  • Question

  • The recommendations I've seen for c#/SQL Server integral types are:

    byte - tinyint

    short - smallint

    int - int

    long - bigint

     

    I assume we can use the opposite signed c# types with these same SQL Server types:

    sbyte - tinyint

    ushort - smallint

    uint - int

    ulong - bigint

     

    But I wanted to get some confirmation on this to make sure this isn't a bad assumption.

     

    The assumption is based on the fact that both the signed and unsigned version of each integral type is the same size. E.g., int and unit are both 32 bit, etc.

     

    This got me wondering about how the sign is actually stored then, because it looks like both the signed and unsigned versions have the same number of possible values.

    E.g.,

    byte:    0 to 255 :     256 possible values

    sbyte:  -128 to 127 : 256 possible values

     

    So how is the sign stored in memory, and would it make any difference to store either type in the same SQL Server type?

     

    Thanks

    Thursday, January 10, 2008 10:43 PM

Answers

  •  vtcoder wrote:
    I think I'm leaning towards the recommendation of using the next largest data type, as mentioned: E.g.,

    sbyte - int (just to avoid misinterpretation)

    ushort - int

    uint - bigint

    ulong - bigint (?)

    This raises another question - can bigint handle ulong, or is there another SQL Server type that should be used? I can look this up, and will, but I thought I'd go ahead and post since someone here may know it off hand

    bigint is a 64-bit signed integer, so unsigned 64-bit data can overflow it.  I think decimal/numeric can store wider data if set up properly, but it's significantly less efficient than any of the basic integer types.  You should ask the developers if they intend on using the full range of an unsigned 64-bit integer--the numbers they use might fit just fine.

     

     vtcoder wrote:
    Aside from this, would there be any problems with the above recommendation (aside from the efficiency issue)?

    Nope, it's the best overall choice.

     

     vtcoder wrote:
    And one last item - if one was interested in the storing oppositely signed ints in their SQL Server size-counterparts (i.e., the dangerous option due to misinterpretation), it seems it would only be a problem if the field were manipulated/interpreted from SQL Server. If this were just a data storage mechanism, and the c# code always treated the fields consistently, there should be no problems, correct? I don't think I'm going to go this route, but I just wanted to be sure I understood the specifics of the danger here.

    This is correct, but it's unwise to assume that SQL Server would never be used for processing.  It's hard to be certain how the database will be used in the future.  5 years from now a reporting package or something might be directly connected to it, and wouldn't benefit from your C# applications internal fixing of the values.


    Additionally, your C# application might want to let SQL Server do some of the work, rather than having to do the sophisticated work required to extract the true values and processing the results internally.  T-SQL can do a lot of things a lot more easily than C# can.


    -Ryan / Kardax

    Thursday, January 24, 2008 9:37 PM
  • Reading back up this thread, I really can't believe it's actually sparked any debate and is still not marked as answered. So here's the answer in a nutshell.

    • You can use signed integer types in .NET to represent equivalently sized signed integer types in SQL Server (i.e. Int64 = BIGINT, Int32 = INT, Int16 = SMALLINT, SByte = TINYINT).
    • You can use IEEE 754 compliant types in .NET to represent equivalently sized IEEE 754 compliant types in SQL Server (i.e. Single = REAL, Double = FLOAT).
    • You can use scaled integer types in .NET to represent equivalently sized scaled integer types in SQL Server (i.e. Decimal = DECIMAL aka NUMERIC).
    For advanced/stupid users only:
    • You can use unsigned integer types in .NET to represent equivalently sized signed integer types in SQL Server (i.e. UInt64 ~= BIGINT, UInt32 ~= INT, UInt16 ~= SMALLINT, Byte ~= TINYINT) if, and only if, you fully understand the internal implementation of two's complement integer storage and the resultant implications.
    End of story.
    Sunday, January 13, 2008 1:45 AM
  • The recommendations you have listed are correct.

    But SQL Server uses signed types, so unsigned types won't always work correctly with them. For example if you retrieve a negative number from SQL Server and try to put it into an unsigned type, you'll either get the wrong number or an OverflowException depending on what your overflow detection settings are. Similarly if you try and put a number larger than the signed type would support into SQL Server then you'll get an overflow.

    So yes, you can kind of use unsigned types, as long as you remember never to use negative numbers, or positive numbers outside the range of the equivalent signed types. Though to be honest it's easier just to use the signed types.

    Why do you want to use unsigned types?




    PS: If you want to know how the sign is typically stored check out http://en.wikipedia.org/wiki/Two%27s_complement
    Thursday, January 10, 2008 10:53 PM
  • In C#, you can use unchecked conversions to store unsigned types in their signed equivilants, and use an unchecked conversion back to the original to get the data back, with no loss... using this same technique, it's possible to store unsigned data in signed types in SQL Server without losing anything.  This works because the types are the same length, it's just that one of the bits changes from being part of the value to being a "sign" bit.

    The big loss though is that SQL Server can't accurately process unsigned-stored-in-signed data without a lot of extra complexity in your queries.  The best solution for is to just use a wider data type... you lose a small amount of storage efficiency, but otherwise everything works well Smile

    -Ryan / Kardax
    Friday, January 11, 2008 6:21 PM

All replies

  • The recommendations you have listed are correct.

    But SQL Server uses signed types, so unsigned types won't always work correctly with them. For example if you retrieve a negative number from SQL Server and try to put it into an unsigned type, you'll either get the wrong number or an OverflowException depending on what your overflow detection settings are. Similarly if you try and put a number larger than the signed type would support into SQL Server then you'll get an overflow.

    So yes, you can kind of use unsigned types, as long as you remember never to use negative numbers, or positive numbers outside the range of the equivalent signed types. Though to be honest it's easier just to use the signed types.

    Why do you want to use unsigned types?




    PS: If you want to know how the sign is typically stored check out http://en.wikipedia.org/wiki/Two%27s_complement
    Thursday, January 10, 2008 10:53 PM
  • In C#, you can use unchecked conversions to store unsigned types in their signed equivilants, and use an unchecked conversion back to the original to get the data back, with no loss... using this same technique, it's possible to store unsigned data in signed types in SQL Server without losing anything.  This works because the types are the same length, it's just that one of the bits changes from being part of the value to being a "sign" bit.

    The big loss though is that SQL Server can't accurately process unsigned-stored-in-signed data without a lot of extra complexity in your queries.  The best solution for is to just use a wider data type... you lose a small amount of storage efficiency, but otherwise everything works well Smile

    -Ryan / Kardax
    Friday, January 11, 2008 6:21 PM
  • I can just imagine the converstions as a result of storing unchecked conversions from unsigned types in the database...

    > "Our SSRS report says that we have -183745 page hits today"
    > "No, look, you can see in this web portal that is was 736493 hits
    > "So why does the database say -183745?"
    > "Ah, well, you see if you want to generate reports off the database then you need to take the two's complement of any negative numbers to convert them to the real value"
    > "Um, why did you do it like that?"
    > "Um........"



    PS: Ryan - I know you were just pointing out the technical possibility and not recommending this approach Smile
    Saturday, January 12, 2008 10:09 AM
  • I have the datatype comparison chart below but what is not in there is the fact the SQL Server decides when you can use bigint because you can define bigint but SQL Server will only use bigint when your operation runs out of int digits.  Float is required for most math functions but you cannot set precision and scale in SQL Server so if you use float you should convert it to Decimal if you don't want strange result. 

     

    http://msdn2.microsoft.com/en-us/library/ms131092.aspx

     

    Saturday, January 12, 2008 3:31 PM
  • Not quite sure what you mean there?

    The BIGINT data type takes 8 bytes of storage, and is equivalent to System.Int64 in the framework. I'm not sure where you found the information that it only stores the data in 4 bytes for smaller values, but even if so that's an implementation detail rather than a contract. So you decide what the range of data you want is, not SQL Server.

    With the FLOAT data type you can set the precision; the default is 53 which makes it equivalent to System.Double in the framework. You shouldn't be converting it to/from System.Decimal because they are different concepts with different semantics - one is an IEEE floating point number and the other is a scaled integer.
    Saturday, January 12, 2008 7:38 PM
  • (but even if so that's an implementation detail rather than a contract. So you decide what the range of data you want is, not SQL Server.)


    It is not an implementation detail if your operation is within INT values you cannot use Bigint, many strange results in SQL Server is related to  FLoat most long time users tell users not to use it but most mathematical functions are float dependent meaning you cannot get expected results without Float.  In SQL Server you can only set precision and scale in Decimal and Numeric.  All I posted is actually in the BOL since since 1999.

     

    Transact-SQL INT

     

    http://msdn2.microsoft.com/en-us/library/ms187745.aspx

     

    (The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.)


    Transact-SQL Float

     

    http://msdn2.microsoft.com/en-us/library/ms173773.aspx

     

    Transact-SQL Decimal

     

    http://msdn2.microsoft.com/en-us/library/ms187746.aspx

    Saturday, January 12, 2008 11:46 PM
  • if your operation is within INT values you cannot use Bigint


    Er, so you're saying that BIGINT cannot store the value 1?

    In SQL Server you can only set precision and scale in Decimal and Numeric


    As I said, you can set the precision for a FLOAT data type. I didn't say you could set the scale, which is unsurprising given that it is mandated in the IEEE 754 specification for floating point arithmentic. And unsurprisingly matches the range and semantics of System.Double perfectly in its default scale - not System.Decimal which has a different range and different semantics. On the page you linked to is the following information which demonstrates this point.

    float [ ( n ) ]

    Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.



    Anyway, we're somewhat off topic here. The original post was asking whether you can use unsigned types to represent equivalent values, and as I posted earlier, yes you can within restrictions but it's best not to.



    Sunday, January 13, 2008 1:03 AM
  • (what is not in there is the fact the SQL Server decides when you can use bigint because you can define bigint but SQL Server will only use bigint when your operation runs out of int digits.)

     

    my original post did not say anything about not storing int in bigint.

     

    (Er, so you're saying that BIGINT cannot store the value 1?)

    Sunday, January 13, 2008 1:44 AM
  • Reading back up this thread, I really can't believe it's actually sparked any debate and is still not marked as answered. So here's the answer in a nutshell.

    • You can use signed integer types in .NET to represent equivalently sized signed integer types in SQL Server (i.e. Int64 = BIGINT, Int32 = INT, Int16 = SMALLINT, SByte = TINYINT).
    • You can use IEEE 754 compliant types in .NET to represent equivalently sized IEEE 754 compliant types in SQL Server (i.e. Single = REAL, Double = FLOAT).
    • You can use scaled integer types in .NET to represent equivalently sized scaled integer types in SQL Server (i.e. Decimal = DECIMAL aka NUMERIC).
    For advanced/stupid users only:
    • You can use unsigned integer types in .NET to represent equivalently sized signed integer types in SQL Server (i.e. UInt64 ~= BIGINT, UInt32 ~= INT, UInt16 ~= SMALLINT, Byte ~= TINYINT) if, and only if, you fully understand the internal implementation of two's complement integer storage and the resultant implications.
    End of story.
    Sunday, January 13, 2008 1:45 AM
  • All of the above is not relevant.

    Sunday, January 13, 2008 1:49 AM
  • All of the above is not relevant.


    Now the thread is really confusing to users reading it. Because 'all' of the above is not irrelevant.

    As you're a moderator it probably makes sense to delete all posts in this thread other than the first post which is the question, and the seconds post which is mine that answers it.
    Sunday, January 13, 2008 1:55 AM
  • No I provided a chart of all three data types which you did not provide so I also answered the question.  All you posted are semantics and insults to me. 

     

    Sunday, January 13, 2008 2:02 AM
  • I hardly see how I posted any insults. I was trying to dispel the misinformation you posted that SQL Server decides for you whether to use BIGINT or INT, and that it's a good idea to convert between IEEE 754 floating point numbers and scaled integers.

    So I apologise if you feel insulted, if you thought it was a personal attack then it wasn't the intention. I was merely trying to ensure that the information in reply to the post was correct and clear.

    In my opinion, posting about the semantics of numerical types is important when the post is asking about the semantics of numerical types.
    Sunday, January 13, 2008 2:09 AM
  • EDIT

     

     Greg Beech wrote:

     

    Reading back up this thread, I really can't believe it's actually sparked any debate and is still not marked as answered. So here's the answer in a nutshell.


    For advanced/stupid users only:


     


     Greg Beech wrote:

     

    I hardly see how I posted any insults. I was trying to dispel the misinformation you posted that SQL Server decides for you whether to use BIGINT or INT,


     

    (what is not in there is the fact the SQL Server decides when you can use bigint because you can define bigint but SQL Server will only use bigint when your operation runs out of int digits.)

     

    Back to my original post it is not what you are interpreting which would be misinformation.  And you did not insult me.

     

    Sunday, January 13, 2008 2:17 AM
  • Thank you for the information, and the spirited debate. You confirmed my hunch. Also, thanks for the link on two's complement storage.

     

    As to why I'd want to use unsigned types, it was really just a matter of trying to find the best recommendations for our developers. If a developer happens to use an unsigned integer variable for something, I need to be able to tell them exactly what SQL Server type to use if it needs to be persisted. There may be another argument on the use of unsigned integral types in c#, but that's perhaps an argument for another thread. Assuming developers can use all types in c#, I need to specify corresponding SQL Server data types.

     

    I think I'm leaning towards the recommendation of using the next largest data type, as mentioned: E.g.,

    sbyte - int (just to avoid misinterpretation)

    ushort - int

    uint - bigint

    ulong - bigint (?)

    This raises another question - can bigint handle ulong, or is there another SQL Server type that should be used? I can look this up, and will, but I thought I'd go ahead and post since someone here may know it off hand.

     

    Aside from this, would there be any problems with the above recommendation (aside from the efficiency issue)?

     

    And one last item - if one was interested in the storing oppositely signed ints in their SQL Server size-counterparts (i.e., the dangerous option due to misinterpretation), it seems it would only be a problem if the field were manipulated/interpreted from SQL Server. If this were just a data storage mechanism, and the c# code always treated the fields consistently, there should be no problems, correct? I don't think I'm going to go this route, but I just wanted to be sure I understood the specifics of the danger here.

     

    Thanks

    Monday, January 21, 2008 5:14 PM
  •  vtcoder wrote:
    I think I'm leaning towards the recommendation of using the next largest data type, as mentioned: E.g.,

    sbyte - int (just to avoid misinterpretation)

    ushort - int

    uint - bigint

    ulong - bigint (?)

    This raises another question - can bigint handle ulong, or is there another SQL Server type that should be used? I can look this up, and will, but I thought I'd go ahead and post since someone here may know it off hand

    bigint is a 64-bit signed integer, so unsigned 64-bit data can overflow it.  I think decimal/numeric can store wider data if set up properly, but it's significantly less efficient than any of the basic integer types.  You should ask the developers if they intend on using the full range of an unsigned 64-bit integer--the numbers they use might fit just fine.

     

     vtcoder wrote:
    Aside from this, would there be any problems with the above recommendation (aside from the efficiency issue)?

    Nope, it's the best overall choice.

     

     vtcoder wrote:
    And one last item - if one was interested in the storing oppositely signed ints in their SQL Server size-counterparts (i.e., the dangerous option due to misinterpretation), it seems it would only be a problem if the field were manipulated/interpreted from SQL Server. If this were just a data storage mechanism, and the c# code always treated the fields consistently, there should be no problems, correct? I don't think I'm going to go this route, but I just wanted to be sure I understood the specifics of the danger here.

    This is correct, but it's unwise to assume that SQL Server would never be used for processing.  It's hard to be certain how the database will be used in the future.  5 years from now a reporting package or something might be directly connected to it, and wouldn't benefit from your C# applications internal fixing of the values.


    Additionally, your C# application might want to let SQL Server do some of the work, rather than having to do the sophisticated work required to extract the true values and processing the results internally.  T-SQL can do a lot of things a lot more easily than C# can.


    -Ryan / Kardax

    Thursday, January 24, 2008 9:37 PM