Sql Server：uniqueidentifier加整数复合PK ...使用什么类型的索引？ [英] Sql Server: uniqueidentifier plus integer compound PK ... what type of index to use?

问题描述

在我的SQL Server 2005数据库中有一个连接表，它由两列组成：

• object_id（uniqueidentifier）








为SELECT性能创建此PK索引的最佳方法是什么？


如果列是两个整数，我只使用复合聚集索引（默认）。





任何人都有这种情况的经验？

解决方案

是的，GUID对集群索引是非常糟糕的，因为GUID的设计非常随机，因此导致大量碎片，从而导致性能问题。





查看Kim Tripp的博客 - 最着名的是锁定索引辩论继续和 GUIDs as PRIMARY and / or CLUSTERED key - for a lot of valuable background info。





真的需要在这两个列上有一个索引，我建议一个非聚集索引 - 它可以是一个主索引 - 只是最好不是一个聚集索引。





马克




I have a junction table in my SQL Server 2005 database that consist of two columns:

• object_id (uniqueidentifier)

• property_id (integer)

These values together make a compound primary key.

What's the best way to create this PK index for SELECT performance?

If the columns were two integers, I would just use a compound clustered index (the default). However, I've heard bad things about clustered indexes when uniqueidentifiers are involved.

Anyone have experience with this situation?

解决方案

Yes, GUID's are really bad for clustered indexes, since the GUIDs is by design very random and thus leads to massive fragmentation and thus performance problems.

See Kim Tripp's blog - most notably "The CLustered Index Debate continues" and "GUIDs as PRIMARY and/or CLUSTERED key" - for a lot of valuable background info.

If you really need to have an index on these TWO columns, I'd suggest a non-clustered index - it can be a primary index - just better not a clustered index.

Marc

这篇关于Sql Server：uniqueidentifier加整数复合PK ...使用什么类型的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！