locked
SQL Server 2008 – Unsigned Integer Data Types RRS feed

  • Question

  • 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?
    Thursday, September 1, 2011 7:44 AM

Answers

  • Uri/Kent,

    Even if I agreed that it rarely makes sense to increase complexity of the system (for example store unsigned int as signed casting it back and force), row size always matter. You want to reduce row size -> # of data pages -> # of IO operations as much as possible.


    Thank you!

    My blog: http://aboutsqlserver.com



    Thank you for stating this; I never meant to imply that size doesn't matter! Yes, I agree that rowsize always matters; nonetheless, it is still my opinion that in this case, at least from the superficial view that we are given, this looks misguided.  Yes, this will have an impact on performance, and if the table was small then my opinion might change such that maybe I want to do this reduction.  For instance, if we are talking about the key of a table I might be more open to discuss this because we are reducing the key from an 8-byte column to a 4-byte column; however, the tone that I see from the OP is that this post is talking about broad brush strokes.

    To me this question is asking about doubling the range of a set of values as a general design philosophy by re-using the sign bit where possible.  My comment is that if you want to double the range then take the datatypes as they are and consider also using the negative numbers rather than mucking up the whole design.  Otherewise, scale up to the next available datatype.  For me rather than stating, "... You want to reduce row size -> # of data pages -> # of IO operations as much as possible.", I would instead say something like, "... You want to reduce row size -> # of data pages -> # of IO operations as much as practical."  Almost the same thing; we might really mean the same thing, I'm just not sure.

    I do appreciate the discussion; as always, please continue to challenge me when you think a better idea is available; thank you for picking me up.

    :)

    • Proposed as answer by Stephanie Lv Wednesday, September 7, 2011 2:59 AM
    • Marked as answer by Stephanie Lv Friday, September 9, 2011 12:30 PM
    Friday, September 2, 2011 1:35 PM

All replies

  • Can you estimate on what will be range of data 0-255, or 0-2 million? I would not go for CREATE RULE and etc. (maintenance)

    What is your concern? INT datatype stores 4 bytes, BIGINT -8 Bytes....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 1, 2011 7:55 AM
  • Basically, my values could be greater than 2,147,483,647 (Signed Int's max value) but they will be less than 4294967294 (Unsigned Int's max value), so I want to avoid BigInt to save memory/storage.

    Thursday, September 1, 2011 8:07 AM
  • >>>>so I want to avoid BigInt to save memory/storage

    How do you save memory? Memory and storage nowadays is not an issue. Using DATETIME data type column which stores 8 bytes as well we can create even CI index on to improve performance for range queries. 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 1, 2011 8:13 AM
  • Another option would be to ADD a CONSTRAINT to the TABLE itself CHECKing the INT was positive: CHECK(col_1 >= 0)

    Thursday, September 1, 2011 12:02 PM
    Answerer
  • The real question you seem to be asking seems to be "can I double the positive range of the integer datatype by somehow casting the data as an unsigned integer".  The short answer to that question no -- at least not directly.  Therefore, use the BIGINT datatype.  Since you know that you will exceed the size of INT you should go with BIGINT.  I understand what you are saying about the desire to avoid size of the BIGINT datatype; however, this still looks like the best choice.  You might also be tempted to ask whether it will take up less space to use the decimal(10) datatype.  No, it will not.  The decimal(10) datatype requires 1 more byte than BIGINT.
    Thursday, September 1, 2011 1:32 PM
  • >>>>so I want to avoid BigInt to save memory/storage

    How do you save memory? Memory and storage nowadays is not an issue. Using DATETIME data type column which stores 8 bytes as well we can create even CI index on to improve performance for range queries. 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    I agree; this kind of motivation to me seem mis-guided.
    Thursday, September 1, 2011 7:17 PM
  • Hi Steve,

    If you're using 2008/R2 Enterprise edition and concern about row size, you can enable ROW compression (needs to be done for every index). In such case, SQL Server will use only amount of space needed to store specific value (e.g. bigint would use from 0 to 8 bytes) in price of very light CPU overhead.

    Uri/Kent,

    Even if I agreed that it rarely makes sense to increase complexity of the system (for example store unsigned int as signed casting it back and force), row size always matter. You want to reduce row size -> # of data pages -> # of IO operations as much as possible.


    Thank you!

    My blog: http://aboutsqlserver.com


    Thursday, September 1, 2011 7:40 PM
  • Thanx, i see i misunderstood the question.
    Friday, September 2, 2011 11:54 AM
    Answerer
  • Uri/Kent,

    Even if I agreed that it rarely makes sense to increase complexity of the system (for example store unsigned int as signed casting it back and force), row size always matter. You want to reduce row size -> # of data pages -> # of IO operations as much as possible.


    Thank you!

    My blog: http://aboutsqlserver.com



    Thank you for stating this; I never meant to imply that size doesn't matter! Yes, I agree that rowsize always matters; nonetheless, it is still my opinion that in this case, at least from the superficial view that we are given, this looks misguided.  Yes, this will have an impact on performance, and if the table was small then my opinion might change such that maybe I want to do this reduction.  For instance, if we are talking about the key of a table I might be more open to discuss this because we are reducing the key from an 8-byte column to a 4-byte column; however, the tone that I see from the OP is that this post is talking about broad brush strokes.

    To me this question is asking about doubling the range of a set of values as a general design philosophy by re-using the sign bit where possible.  My comment is that if you want to double the range then take the datatypes as they are and consider also using the negative numbers rather than mucking up the whole design.  Otherewise, scale up to the next available datatype.  For me rather than stating, "... You want to reduce row size -> # of data pages -> # of IO operations as much as possible.", I would instead say something like, "... You want to reduce row size -> # of data pages -> # of IO operations as much as practical."  Almost the same thing; we might really mean the same thing, I'm just not sure.

    I do appreciate the discussion; as always, please continue to challenge me when you think a better idea is available; thank you for picking me up.

    :)

    • Proposed as answer by Stephanie Lv Wednesday, September 7, 2011 2:59 AM
    • Marked as answer by Stephanie Lv Friday, September 9, 2011 12:30 PM
    Friday, September 2, 2011 1:35 PM
  • Hi Kent,

    I'm completely agree with you. It's obviously not a good idea to choose smaller data type to reduce the row size at the price of additional superficial logic (type casting, storing unsigned integers as binary(4), etc).

    I'm sorry, I understand that I made an observation out of context. Thank you for pointing to that! Frankly I mainly respond to Uri's "memory and storage nowadays is not an issue" phrase. I know that he did not mean it but it could be a little bit confusing for others especially taking it out of content same way I did :)

    I really hope we're on the same page! :)

    Sincerely,

    Dmitri


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, September 2, 2011 2:11 PM
  • Hi Kent,

    I'm completely agree with you. It's obviously not a good idea to choose smaller data type to reduce the row size at the price of additional superficial logic (type casting, storing unsigned integers as binary(4), etc).

    I'm sorry, I understand that I made an observation out of context. Thank you for pointing to that! Frankly I mainly respond to Uri's "memory and storage nowadays is not an issue" phrase. I know that he did not mean it but it could be a little bit confusing for others especially taking it out of content same way I did :)

    I really hope we're on the same page! :)

    Sincerely,

    Dmitri


    Thank you!

    My blog: http://aboutsqlserver.com


    Thank you, Dmitri; yes, I think we are on the same page.  Thanks for picking me up and again, I appreciate your rock solid contributions.

    :)


    Friday, September 2, 2011 2:47 PM