问题描述
在我当前的项目中,我遇到了我们的主数据库脚本.仔细观察,我注意到我们所有的原始主键的数据类型都是 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) 作为主键列;好,坏,谁在乎?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!