DECIMAL vs NUMERIC for ID values

  • We have ID columns with values with up to 10 digits. Those ID's are usually third (and last) segment in indices on various tables. Normally I define them as NUMERIC(10), but looking into other people's code, I see they have lately (from SQL 2014) started to define them as DECIMAL(10,0). I would assume it is a bad idea, especially when it comes to JOIN expressions involving tableA.ID = tableB.ID, but I do not have enough knowledge to address it nor could find something, when searching for SQL2014

  • Decimal and numeric are synonyms in SQL Server. It doesn't matter if you choose one or the other, although it's a good practice to be consistent.

    On the other side, why aren't you using int or even bigint for really large tables?

    Luis C.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/1/2016)


    Decimal and numeric are synonyms in SQL Server. It doesn't matter if you choose one or the other, although it's a good practice to be consistent.

    On the other side, why aren't you using int or even bigint for really large tables?

    Thank you, Luis, for the reply.

    INT in this case won't work, as any ID of 10 digits over 2147483647 will fail, I presume. With regards to BIGINT, I admit I have never used it before, hence do not know

    what affect it might have on our DB (number of records varies between 1 and 9 million from customer to customer) .

  • If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence or something like this that might need to have always 10 digits (even then, there would be over a billion (10^9) values). All this at the storage cost of 4 Bytes per row.

    Bigint will double the bytes ( 8), but will give you mover values than you'll ever need.

    Right now, using decimal(10) requires 9 Bytes.

    Someone would say that if you want to handle like a 10 digit value every time, you could go with a char(10) with a check constraint. The only issue is that it will add one byte to the storage used by decimal, but will also allow you to have leading zeros, which are not available on numeric values but could be added for display.

    One million bytes are under a MB which is unexpensive this days, but adding the small improvements, you might get a great result.

    Luis C.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/1/2016)


    If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence

    Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.

  • BOR15K (11/1/2016)


    Luis Cazares (11/1/2016)


    If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence

    Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.

    NUMERIC and DECIMAL are equivalent (link). Adding (10) or (10,0) to either is also equivalent, because the default precision for DECIMAL/NUMERIC is zero.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • BOR15K (11/1/2016)


    Luis Cazares (11/1/2016)


    If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence

    Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.

    Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.

    https://msdn.microsoft.com/en-us/library/ms187746.aspx

    If you don't think that one byte matters that is another discussion. 😀

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (11/1/2016)


    BOR15K (11/1/2016)


    Luis Cazares (11/1/2016)


    If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence

    Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.

    Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.

    https://msdn.microsoft.com/en-us/library/ms187746.aspx

    If you don't think that one byte matters that is another discussion. 😀

    Correct, unless you want to use foreign key. Having a new table with BIGINT and original one - NUMERIC(10) will not allow you to create any foreign key.

    I am not in a position to start changing all live DB's from NUMERIC(10) to BIGINT.

  • BOR15K (11/1/2016)


    TheSQLGuru (11/1/2016)


    BOR15K (11/1/2016)


    Luis Cazares (11/1/2016)


    If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence

    Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.

    Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.

    https://msdn.microsoft.com/en-us/library/ms187746.aspx

    If you don't think that one byte matters that is another discussion. 😀

    Correct, unless you want to use foreign key. Having a new table with BIGINT and original one - NUMERIC(10) will not allow you to create any foreign key.

    I am not in a position to start changing all live DB's from NUMERIC(10) to BIGINT.

    Hopefully you can educate the architect(s) so this doesn't keep happening in the future. Smallest data type for the data is a valuable mantra.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 8 (of 8 total)

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