SQL Server BIGINT 或 DECIMAL(18,0) 用于主键 [英] SQL Server BIGINT or DECIMAL(18,0) for primary key
问题描述
我们有一个 SQL Server 2005 数据库,我们希望提高批量删除/插入/选择的性能,我注意到它使用 decimal(18,0)
作为其主键.我知道这会给我们带来比 bigint
多得多的价值,但我希望这可能是一个快速的胜利,并且根据我的计算,应该能让我们持续数百万年的增长.
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 bigint
but was hoping it might be a quick win and should last us for many million years of growth by my calculations.
我在 .net 中看到 docs 小数取16 个字节而不是 longs 所需的 8 个字节,但在 SQL Server 中它看起来像 bigint
占用 8 个字节 但 decimal(18,0)
只占用 5 字节 - 正如 select DATALENGTH(max(id)) from table
所见.这是正确的吗?
I see in the .net docs decimals take 16 bytes instead of the 8 required by longs but in SQL Server it looks like bigint
take 8 bytes but the decimal(18,0)
takes only 5 bytes - as also seen by select DATALENGTH(max(id)) from table
. Is this correct?
是否还有其他原因 bigint
可能会变慢或者我应该坚持使用 decimal(18,0)
?
Is there any other reason bigint
might be slower or should I stick to decimal(18,0)
?
推荐答案
DATALENGTH 在计算字节数之前转换为 varchar.所以你的最大值是 <100000.
DATALENGTH is casting to varchar before counting bytes. So your max value is < 100000.
这9个字节可以证明.sys.columns 有一个 max_length 列(十进制是固定长度,所以它是总是 9 个字节,在你另外询问之前)
The 9 bytes can be proved with this. sys.columns has a max_length column (decimal is fixed length so it is always 9 bytes, before you ask otherwise)
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
出于遗留原因,在 SQL Server 2000 添加 bigint 之前,decimal(18, 0)
通常用作64 位整数"的替代.
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)
和 bigint
的范围大致相同:decimal 在 9 个字节处多 1 个字节根据文档
decimal(18, 0)
and bigint
are roughly the same in range: decimal is one byte more at 9 bytes as per the documentation
最重要的是,纯整数会比小数快一些(可能无法测量).也就是说,如果预计在明年或 5 年内有超过 40 亿行,那么性能应该很重要.如果没有,那么只需使用 int
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
这篇关于SQL Server BIGINT 或 DECIMAL(18,0) 用于主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!