SQL Server2005のインデックスのソート順についての調査

確かSQL SERVER2000から、インデックスが双方向連結リストとなっていたと思うのでこれの調査
まずはデータ作成から。

testデータベースを作って、testtblを作って、1万件データを入れます。
ここまでのsqlはこんな感じ。
create database test
go
use test
create table testtbl
(
  pk1 int primary key identity(1,1),
  id1 int,
  id2 int,
  id3 int
)

declare @a as int, @b as int, @c as int, @i as int

set @i=1
while (@i <> 10000)
begin
set @a=rand()*10000
set @b=rand()*10000
set @c=rand()*10000
insert testtbl (id1,id2,id3) values(@a,@b,@c)
set @i=@i+1
end


次に、インデックスを張らずに、以下のSQLを実行し実行プランを確認する。
select * from testtbl order by id1




当然、SORTという処理が入っている。
続いて、以下のSQLにてid1昇順でid2,id3を付加列に先ほどのselect文をカバーするインデックスを作成する。
create index testtbl_id1 on testtbl (id1 asc) include(id2,id3)
インデックスにpk1が入っていないけど、testtblがクラスタ化インデックステーブルのため行ロケータとしてキー値が使われるので今作成したインデックスに必ず含まれているから指定しなくても付加列と同じ振る舞いが期待できるので、クエリをカバーするインデックスといえる。
で、同じSQL実行。
以下が実行プラン。






実行結果は、ソート処理がなくなっている。
続いて、今のインデックスのままソートキーで逆順に表示する以下のSQL文を発行。
select * from testtbl order by id1 desc
以下が実行プラン。




ちゃんとインデックスを使ってsort処理が不要となっている。
これによりインデックスが単方向リストではないことが確認できた。ソート項目が１項目であれば昇順/降順は注意しなくてもよさそうです。
続いて今のインデックスのまま、以下のSQLにて２つの項目でソートしてみます。
select * from testtbl order by id1,id2
以下が実行プラン




インデックスは使っているが、SORT処理が行われている。しかも第１キーではソート済みであるにもかかわらず、コストの95%はソート処理。

次に複数列のインデックスを張ってみる。
create index id1_id2 on testtbl (id1 asc,id2 asc) include (id3)

で先ほどのsqlを実行。




今回は、SORT処理は不要。
同様にid1 desc,id2 descではSORT処理は不要でした。ただし、id1 asc,id2 descやid1 desc,id2 ascでは、インデックスを使うがSORTも発生する。
同様に、id2,id1のソートでもSORTが発生する。

というわけで、インデックスのソート順はソート指定が複数ある場合にソートの向きが異なる場合、意識したほうがよい程度のものでした。

最後に、クエリをカバーしていないインデックスのみの場合、インデックスを使うかテーブルスキャンになるかを確認。
drop index id1_id2 on testtbl
drop index testtbl_id1 on testtbl
create index id1 on testtbl (id1) include (id2)
最後に作成したインデックスは、id3が付加列にないため、以下のクエリをカバーしていないインデックスになります。
select * from testtbl order by id1
実行プランは以下。





クエリをカバーしていないと全権検索ではインデックスを使わないみたい。