Is there any reason for numeric rather than int in T-SQL?

Question

Why would someone use numeric(12, 0) datatype for a simple integer ID column? If you have a reason why this is better than int or bigint I would like to hear it.

We are not doing any math on this column, it is simply an ID used for foreign key linking.

I am compiling a list of programming errors and performance issues about a product, and I want to be sure they didn't do this for some logical reason. If you follow this link: http://msdn.microsoft.com/en-us/library/ms187746.aspx

... you can see that the numeric(12, 0) uses 9 bytes of storage and being limited to 12 digits, theres a total of 2 trillion numbers if you include negatives. WHY would a person use this when they could use a bigint and get 10 million times as many numbers with one byte less storage. Furthermore, since this is being used as a product ID, the 4 billion numbers of a standard int would have been more than enough.

So before I grab the torches and pitch forks - tell me what they are going to say in their defense?

And no, I'm not making a huge deal out of nothing, there are hundreds of issues like this in the software, and it's all causing a huge performance problem and using too much space in the database. And we paid over a million bucks for this crap... so I take it kinda seriously.

Answer 1

In your example I can't think of any logical reason why you wouldn't use INT. I know there are probably reasons for other uses of numeric, but not in this instance.

Answer 2

According to: http://doc.ddart.net/mssql/sql70/da-db_1.htm

decimal

Fixed precision and scale numeric data from -10^38 -1 through 10^38 -1.

numeric

A synonym for decimal.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

It is impossible to know if there is a reason for them using decimal, since we have no code to look at though.

Answer 3

There are many reasons to use numeric - for example - financial data and other stuffs which need to be accurate to certain decimal places. However for the example you cited above, a simple int would have done.

Perhaps sloppy programmers working who didn't know how to to design a database ?

Answer 4

In some databases, using a decimal(10,0) creates a packed field which takes up less space. I know there are many tables around my work that use that. They probably had the same kind of thought here, but you have gone to the documentation and proven that to be incorrect. More than likely, I would say it will boil down to a case of "that's the way we have always done it, because someone one time said it was better".

Answer 5

Before you take things too seriously, what is the data storage requirement for each row or set of rows for this item?

Your observation is correct, but you probably don't want to present it too strongly if you're reducing storage from 5000 bytes to 4090 bytes, for example.

You don't want to blow your credibility by bringing this up and having them point out that any measurable savings are negligible. ("Of course, many of our lesser-experienced staff also make the same mistake.")

Can you fill in these blanks?

with the data type change, we use
    ____ bytes of disk space instead of ____
    ____ ms per query instead of ____
    ____ network bandwidth instead of ____
    ____ network latency instead of ____

That's the kind of thing which will give you credibility.

Answer 6

It is possible they spend a LOT of time in MS Access and see 'Number' often and just figured, its a number, why not use numeric?

Based on your findings, it doesn't sound like they are the optimization experts, and just didn't know. I'm wondering if they used schema generation tools and just relied on them too much.

I wonder how efficient an index on a decimal value (even if 0 scale is set) for a primary key compares to a pure integer value.

Like Mark H. said, other than the indexing factor, this particular scenario likely isn't growing the database THAT much, but if you're looking for ammo, I think you did find some to belittle them with.

Answer 7

How old is this application that you are looking into?

Previous to SQL Server 2000 there was no bigint. Maybe its just something that has made it from release to release for many years without being changed or the database schema was copied from an application that was this old?!?

Answer 8

Perhaps they're used to working with Oracle?

All numeric types including ints are normalized to a standard single representation among all platforms.