クラスタ化インデックスは主キーにつけないほうがいいかも

最近のマイブーム（死語？）はインデックスです。
SQL Serverのインデックスを理解するためにとんでもなく分厚い本を買ってしまいました。

アーキテクチャ徹底解説 SQL SERVER2000 (マイクロソフト公式解説書)

そこらへんの国語辞典よりも厚みがある...

1000ページに、こんな文章がありました。

主キー列以外のインデックスの方がクラスタ化インデックスとしてふさわしい場合もある。...（略）... このような場合は、PRIMARY KEY 制約の宣言にNONCLUSTERDを追加し、クラスタ化インデックスは、より必然性の高い利用目的のためにとっておいたほうがよい。

えー、なんだってー。だったらデフォルトで主キーにクラスタ化インデックス張らないでほしいんですけど。SQL Serverはデフォルトで主キーにクラスタ化インデックスを付与するので、クラスタ化インデックスを別のカラムにというのは考えてませんでした。

引用したあたりを勝手にまとめると、「主キーで検索する場合非クラスタ化インデックスで検索してもそれほど遅くないからクラスタ化インデックスが威力を発揮するBETWEENを使った範囲クエリのキーとかにクラスタ化インデックスをつけることを検討したほうがいいよ」といっているようです。

気になったので試してみました。（本はSQL Server 2000のものだけどここで試したのはSQL Server 2005）

/*Hogeテーブルの主キーにはデフォルトのクラスタ化インデックス、
 *範囲クエリで使用するカラムには非クラスタ化インデックスをつける
 */
CREATE TABLE [Hoge] 
(
	[HogeID] int PRIMARY KEY NOT NULL,
	[Name] nvarchar(32) NOT NULL,
	[Quantity] int NOT NULL
)
GO
CREATE NONCLUSTERED INDEX [IDX_Hoge_Quantity] ON [Hoge] 
(
	[Quantity] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

/*Fooテーブルの主キーには明示的に非クラスタ化インデックス、
 *範囲クエリで使用するカラムにはクラスタ化インデックスをつける
 */
CREATE TABLE [Foo] 
(
	[FooID] int NOT NULL,
	[Name] nvarchar(32) NOT NULL,
	[Quantity] int NOT NULL
	CONSTRAINT [Foo_PK] PRIMARY KEY NONCLUSTERED 
	(
		[FooID] ASC
	) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IDX_Foo_Quantity] ON [Foo] 
(
	[Quantity] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

--サンプルデータの作成
SET NOCOUNT ON
DECLARE @i int
SET @i = 0
WHILE (@i < 10000)
BEGIN
	INSERT INTO Hoge (HogeID, [Name], Quantity) VALUES (@i, N'Hoge' + STR(@i), @i)
	INSERT INTO Foo (FooID, [Name], Quantity) VALUES (@i, N'Foo' + STR(@i), @i)
	SET @i = @i + 1
END
GO

Hogeテーブルには、HogeIDカラムにクラスタ化インデックス、Quantityカラムに非クラスタ化インデックスをつけました。
Fooテーブルには、FooIDカラムに非クラスタ化インデックス、Quantityカラムにクラスタ化インデックスをつけました。

これらに対する主キーを使った検索とQuantityカラムを使った範囲検索の相対的なクエリコスト（%）を実行計画でみてみました。（下のものは実際に実行計画で表示されるものではないです。%は実行計画で表示されたもの）

(1) SELECT * FROM Hoge WHERE HogeID = 5000; --5%
(2) SELECT * FROM Foo WHERE FooID = 5000; -- 9%
(3) SELECT * FROM Hoge WHERE Quantity BETWEEN 5000 AND 5100; --82%
(4) SELECT * FROM Foo WHERE Quantity BETWEEN 5000 AND 5100; --5%

(1)と(2)はまあいいとして(3)と(4)の差は歴然！ 16倍差です。さらに(4)は(2)よりも小さいコストです。
(4)ではクラスタ化インデックスIDX_Foo_Quantityが効いています。(3)では非クラスタ化インデックスIDX_Hoge_Quantityは使われずクラスタ化インデックススキャンが行われてました。IDX_Hoge_Quantityを無理やり使うようにヒントをつけてみましたが、よけいにコストがかかっちゃいました。

結論：
他に優先度の高いインデックスが必要ならクラスタ化インデックスは主キーにつけないほうがいいかも。主キーはIDという設計の場合は特に当てはまると思う。