SQL Server中唯一标识符列上的群集/非群集索引 [英] Clustered/non-clustered index on unique identifier column in SQL Server

问题描述

我已经阅读了这里的各种问题/答案，基本上表明，由于性能原因，在uniqueidentifier列上使用聚簇索引是一个糟糕的选择。无论如何，我需要使用uniqueidentifier作为我的主键，我不想使用newsequentialid（），因为生成的值彼此太相似（我需要更多随机ID，因此用户无法[合理]'猜测'另一个ID）。

那么，对这个PK进行索引的最佳方法是什么？即使此列上的聚簇索引不理想，它是否优于唯一的非聚集索引？

我正在使用SQL Server 2005.首先，如果您尝试使用 NEWID（）



它通常不是理想的选择，因为其他索引会引用聚簇索引键和长聚簇索引键将降低所有索引的性能。您可以创建一个 IDENTITY 整数列，并使其成为聚簇索引，并在 uniqueidentifier上创建一个非聚集的唯一索引专栏。

I have read the various questions/answers here that basically indicate that having a clustered index on a uniqueidentifier column is a poor choice for performance reasons. Regardless, I need to use a uniqueidentifier as my primary key, and I do NOT want to use newsequentialid() because the generated values are too similar to one another (I need more random IDs so users can't [reasonably] 'guess' another ID).

So, what is the best way to index this PK? Even though a clustered index on this column is not ideal, is it better than a "unique, non-clustered" index?

I'm using SQL Server 2005.

解决方案

First of all, if you are trying to use NEWID() to achieve randomness (more than just looking random), you're in trouble.

It's usually less than ideal choice because other indexes will reference the clustered index key and a long clustered index key will degrade the performance of all indexes. You could create an IDENTITY integer column and make that the clustered index and just create a non-clustered unique index on the uniqueidentifier column.

这篇关于SQL Server中唯一标识符列上的群集/非群集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！