SQL Server Performance

SQL Server 2008 – Unsigned Integer Data Types

Discussion in 'SQL Server 2008 General DBA Questions' started by Diamond2016, Sep 1, 2011.

  1. Diamond2016 New Member

    I am using SQL SERVER 2008, I have a number of INT, SMALLINT fields in my various tables, And I know they all will be 0 or greater than 0 i.e. I can take them Unsigned.

    Is there a simple way of creating/using Unsigned data types OR will I have to Create type->Make Rule->Use the Created Type; as specified in the following article?

    http://www.julian-kuiters.id.au/article.php/sqlserver2005-unsigned-integer

    If this is the only way to use Unsigned in SQL, is there any disadvantage/drawback of using it?
    Diamond2016, Sep 1, 2011
    #1

  2. Madhivanan Moderator

    One simple method is to use check constraint to disallow negatives

    int_col int check (int_col >=0)
    Madhivanan, Sep 2, 2011
    #2

  3. FrankKalis Moderator

    Yes and no. If you do this, only around half of the potentially available values of the given type are available to you. An unsigned int like, for example MySQL uses, offers a range of values from 0 to 4294967295.
    FrankKalis, Sep 2, 2011
    #3
    Diamond2016 likes this.

  4. Madhivanan Moderator

    Yes thats a good point Frank. SQL Server does not allow to have unsigned intergers as Mysql has
    Madhivanan, Sep 2, 2011
    #4

  5. preethi Member

    I believe your application sends unsigned number to the database.
    I prefer you to handle it in the application.You have a couple of ways to do this:
    1. Use a larger number (if application uses unsigned int16 go with int; if it is int32 then use bigint etc.) and add the check constraints to limit the number.
    2. Convert to an appropriate signed value during the time of inserts and selects. (When you insert an unsigned int16 reduce 32786 and insert; add the same value for selected value)
    preethi, Sep 2, 2011
    #5

Share This Page