在 T-SQL 中使用 numeric 而不是 int 有什么原因吗？

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

提问by Jasmine

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.

为什么有人会对简单的整数 ID 列使用 numeric(12, 0) 数据类型？如果你有一个理由为什么这比 int 或 bigint 更好，我想听听。

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

我们没有在此列上做任何数学运算，它只是一个用于外键链接的 ID。

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

我正在编制有关产品的编程错误和性能问题的列表，我想确保他们没有出于某种逻辑原因这样做。如果您点击此链接：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.

...您可以看到 numeric(12, 0) 使用了 9 个字节的存储空间并且被限制为 12 位数字，如果包括负数，总共有 2 万亿个数字。当一个人可以使用 bigint 并以少一个字节的存储空间获得 1000 万倍的数字时，为什么要使用它。此外，由于它被用作产品 ID，标准 int 的 40 亿个数字已经足够了。

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.

不，我不是无中生有，软件中有数百个这样的问题，它们都导致了巨大的性能问题并占用了数据库中的太多空间。我们为这个垃圾支付了超过一百万美元......所以我有点认真。

回答by Mark Harrison

Perhaps they're used to working with Oracle?

也许他们习惯于与 Oracle 合作？

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

包括整数在内的所有数字类型都被规范化为所有平台中的标准单一表示。

回答by no_one

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.

使用数字的原因有很多 - 例如 - 财务数据和其他需要精确到某些小数位的东西。但是，对于您上面引用的示例，一个简单的 int 就可以完成。

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

也许是那些不知道如何设计数据库的草率程序员？

回答by Martin

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?!?

在 SQL Server 2000 之前，没有 bigint。也许它只是多年来一直在发布而没有被更改的东西，或者数据库模式是从这么旧的应用程序中复制的？！？

回答by TheTXI

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.

在您的示例中，我想不出您不使用 INT 的任何合乎逻辑的原因。我知道数字的其他用途可能有其他原因，但在这种情况下不是。

回答by Mark Harrison

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.

您的观察是正确的，但例如，如果您将存储从 5000 字节减少到 4090 字节，您可能不想过于强烈地呈现它。

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.

这就是那种会给你可信度的东西。

回答by Adam

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?

他们可能会在 MS Access 上花费大量时间并经常看到“数字”，只是想通了，它是一个数字，为​​什么不使用数字？

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.

我想知道主键的十进制值索引（即使设置了 0 比例）与纯整数值相比效率如何。

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.

就像 Mark H. 所说的那样，除了索引因素之外，这种特殊情况可能不会使数据库增长太多，但是如果您正在寻找弹药，我认为您确实找到了一些贬低它们的方法。

回答by PCPGMR

In your citation, the decimal shows precision of 1-9 as using 5 bytes. Your column apparently has 12,0 - using 4 bytes of storage - same as integer.

在您的引文中，小数显示 1-9 的精度为使用 5 个字节。您的列显然有 12,0 - 使用 4 个字节的存储 - 与整数相同。

Moreover, INT, datatype can go to a power of 31: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

此外，INT，数据类型可以是 31 的幂：-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)

While decimal is much larger to 38: - 10^38 +1 through 10^38 - 1

虽然十进制比 38 大得多： - 10^38 +1 到 10^38 - 1

So the software creator was actually providing more while using the same amount of storage space.

因此，软件创建者实际上在使用相同数量的存储空间的同时提供了更多。

Now, with the basics out of the way, the software creator actually limited themselves to just 12 numbers or 123,456,789,012 (just an example for place holders not a maximum number). If they used INT they could not scale this column - it would go up to the full 31 digits. Perhaps there is a business reason to limit this column and associated columns to 12 digits.

现在，抛开基础知识，软件创建者实际上将自己限制在 12 个数字或 123,456,789,012（只是占位符的示例，而不是最大数字）。如果他们使用 INT，他们将无法缩放此列 - 它会上升到完整的 31 位数字。可能出于商业原因将此列和相关列限制为 12 位数字。

An INT is an INT, while a DECIMAL is scalar.

INT 是 INT，而 DECIMAL 是标量。

Hope this helps.

希望这可以帮助。

PS: The whole number argument is: A) Whole numbers are 0..infinity B) Counting (Natural) numbers are 1..infinity C) Integers are infinity (negative) .. infinity (positive) D) I would not cite WikiANYTHING for anything. Come on, use a real source! May as well be http://MyPersonalMathCite.com

PS：整数参数是：A）整数是 0..infinity B）计数（自然）数字是 1..infinity C）整数是无穷大（负）..无穷大（正）D）我不会引用 WikiANYTHING为了任何东西。来吧，使用真实的来源！也可以是http://MyPersonalMathCite.com

回答by GEOCHET

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

根据：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).

十进制

修复了从 -10^38 -1 到 10^38 -1 的精度和小数位数数据。

数字

十进制的同义词。

整数

从 -2^31 (-2,147,483,648) 到 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.

不可能知道他们是否有理由使用十进制，因为我们没有代码可以查看。

回答by Ryan Guill

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".

在某些数据库中，使用小数 (10,0) 会创建一个占用较少空间的压缩字段。我知道我的工作中有很多表格都在使用它。他们在这里可能有同样的想法，但是您已经阅读了文档并证明这是不正确的。更有可能的是，我会说这将归结为“这是我们一直这样做的方式，因为有人曾经说过这样更好”。