numeric(38,0) 作为主键列；好，坏，谁在乎? [英] numeric(38,0) as primary key column; good, bad, who cares?

问题描述

在我当前的项目中，我遇到了我们的主数据库脚本.仔细观察，我注意到我们所有的原始主键的数据类型都是 numeric(38,0)我们目前运行 SQL Server 2005 作为我们的主要数据库平台.

On my current project, I came across our master DB script. Taking a closer look at it, I noticed that all of our original primary keys have a data type of numeric(38,0) We are currently running SQL Server 2005 as our primary DB platform.

简单来说，我们同时支持 Oracle 和 SQL Server 作为我们的后端.在 Oracle 中，我们的主键的数据类型为 number(38,0).

For a little context, we support both Oracle and SQL Server as our back-end. In Oracle, our primary keys have a data type of number(38,0).

有人知道这种实现可能产生的副作用和性能影响吗?我一直提倡和实现 int 或 bigint 作为主键，并且很想知道 numeric(38,0) 是否是更好的选择.

Does anybody know of possible side-effects and performance impact of such implementation? I have always advocated and implemented int or bigint as primary keys and would love to know if numeric(38,0) is a better alternative.

推荐答案

好吧，您正在花费更多的数据来存储您永远不会真正到达的数字.

Well, you are spending more data to store numbers that you will never really reach.

bigint 在 8 字节中达到 9,223,372,036,854,775,807

bigint goes up to 9,223,372,036,854,775,807 in 8 Bytes

int 在 4 个字节中上升到 2,147,483,647

int goes up to 2,147,483,647 in 4 bytes

一个 NUMERIC(38,0) 将占用 17 个字节.

A NUMERIC(38,0) is going to take, if I am doing the math right, 17 bytes.

差别不大，但是:更小的数据类型 = 内存中的更多行(或相同行数的更少页面)= 更少的磁盘 I/O 进行查找(索引或数据页查找).复制、日志页面等也是如此.

Not a huge difference, but: smaller datatypes = more rows in memory (or fewer pages for the same # of rows) = fewer disk I/O to do lookups (either indexed or data page seeks). Goes the same for replication, log pages, etc.

对于 SQL Server:INT 是 IEEE 标准，因此 CPU 更容易进行比较，因此您可以通过使用 INT 与 NUMERIC(一种压缩十进制格式)获得轻微的性能提升.(注意在 Oracle 中，如果当前版本与我长大的旧版本匹配，则所有数据类型都被打包，因此内部的 INT 与 NUMERIC( x,0 ) 几乎相同，因此没有性能差异)

For SQL Server: INT is an IEEE standard and so is easier for the CPU to compare, so you get a slight performance increase by using INT vs. NUMERIC (which is a packed decimal format). (Note in Oracle, if the current version matches the older versions I grew up on, ALL datatypes are packed so an INT inside is pretty much the same thing as a NUMERIC( x,0 ) so there's no performance difference)

因此，总体而言——如果您有大量磁盘、RAM 和备用 I/O，请使用您想要的任何数据类型.如果您想获得更高的性能，请保守一些.

So, in the grand scheme of things -- if you have lots of disk, RAM, and spare I/O, use whatever datatype you want. If you want to get a little more performance, be a little more conservative.

否则在这一点上，我会保持原样.无需更改.

Otherwise at this point, I'd leave it as it is. No need to change things.

这篇关于numeric(38,0) 作为主键列；好，坏，谁在乎?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！