SQL Server BIGINT 或 DECIMAL(18,0) 用于主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6838374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server BIGINT or DECIMAL(18,0) for primary key
提问by Adam Butler
We have a SQL Server 2005 database for which we want to improve performance of bulk delete/insert/selects and I notice it uses decimal(18,0)for its primary keys. I understand this will give us many more values than bigintbut was hoping it might be a quick win and should last us for many million years of growth by my calculations.
我们有一个 SQL Server 2005 数据库，我们希望提高批量删除/插入/选择的性能，我注意到它decimal(18,0)用于其主键。我知道这将给我们带来更多的价值，bigint但我希望它可能是一个快速的胜利，并且根据我的计算应该能让我们持续数百万年的增长。
I see in the .net docsdecimals take 16 bytes instead of the 8 required by longs but in SQL Server it looks like biginttake 8 bytesbut the decimal(18,0)takes only 5 bytes- as also seen by select DATALENGTH(max(id)) from table. Is this correct?
我在 .net文档中看到小数需要 16 个字节，而不是 longs 所需的 8 个字节，但在 SQL Server 中它看起来bigint需要 8 个字节，但decimal(18,0)只需要5 个字节- 正如select DATALENGTH(max(id)) from table. 这样对吗？
Is there any other reason bigintmight be slower or should I stick to decimal(18,0)?
还有其他原因bigint可能会变慢还是我应该坚持decimal(18,0)？
采纳答案by gbn
DATALENGTH is casting to varchar before counting bytes. So your max value is < 100000.
在计算字节数之前，DATALENGTH 正在转换为 varchar。所以你的最大值是 < 100000。
The 9 bytes can be proved with this. sys.columnshas a max_length column (decimal is fixed length so it is always 9 bytes, before you ask otherwise)
9个字节可以用这个来证明。sys.columns有一个 max_length 列（十进制是固定长度，所以它总是 9 个字节，在你问其他之前）
CREATE TABLE dbo.foo (bar decimal(18,0))
GO
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('foo')
GO
DROP TABLE dbo.foo
GO
For legacy reasons, decimal(18, 0)was often used as a surrogate for "64 bit integer" before bigint was added with SQL Server 2000.
由于遗留原因，decimal(18, 0)在 SQL Server 2000 添加 bigint 之前，它经常被用作“64 位整数”的替代。
decimal(18, 0)and bigintare roughly the same in range: decimal is one byte more at 9 bytes as per the documentation
decimal(18, 0)并且bigint范围大致相同：根据文档，十进制在 9 个字节处多 1 个字节
On top of that, plain integer will be fractionally (maybe not measurable) faster then decimal. Saying that, if expect to have more then 4 billion rows in the next year or 5, then the performance should matter. If it doesn't, then just use int
最重要的是，纯整数将比小数快一点（可能无法测量）。也就是说，如果预计在明年或 5 年内有超过 40 亿行，那么性能应该很重要。如果没有，那么只需使用int
回答by Brian Webster
You get this range with bigint:
你可以用 bigint 得到这个范围：
-2^63 to 2^63-1
also known as roughly:
-9.2 x 10^18 to 9.2 x 10^18
You get this range with decimal(18,0):
您可以使用 decimal(18,0) 获得此范围：
-10^18 to 10^18
Decimal: Storage Bytes per Precision
十进制：每个精度的存储字节数
Precision Storage Bytes
1-9: 5
10-19: 9
20-28: 13
29-38: 17
Integer Types and Storage Bytes
整数类型和存储字节
integer type Storage Bytes
bigint 8
int 4
smallint 2
tinyint 1
Thoughts
想法
The two examples posted in your Question actually yield virtually the same quantity of unique values.
您的问题中发布的两个示例实际上产生了几乎相同数量的唯一值。
Also, you are not going to see a significant performance change no matter your choice, but you will see a change in efficiency for other programmers on the team if you start using decimals where programmers are expecting an integer. This is a minor point.
此外，无论您做出何种选择，您都不会看到显着的性能变化，但是如果您开始使用小数，而在程序员期望整数的情况下，您会看到团队中其他程序员的效率发生了变化。这是一个小问题。
To address your specific issue, if you want a larger range, use Decimal(38,0). This gives you:
为了解决您的特定问题，如果您想要更大的范围，请使用 Decimal(38,0)。这给你：
-10^38 to 10^38
If you are concerned about speed, use the minimum precision that will last the lifetime of your software.
如果您担心速度，请使用将持续您的软件生命周期的最低精度。
If you're not measuring time in nano-seconds, then choose the option that will fit best for your programmers' mindsets and your desire to have a very long set of numbers.
如果您不是以纳秒为单位来衡量时间，那么请选择最适合您的程序员的思维方式和您想要拥有一组很长数字的愿望的选项。
References
参考
