Left-duplicate indexes

Thu Nov 12, 2009 by Chris Miller in kctechblog-syndication

At Kim Tripp's PASS session on indexing and covered indexes, she more-or-less proved that wider is better for indexes, within reason and searchability. It's better to have the index (Column1, Column2, Column3) than the index (Column1) and then a (Column1, Column2) and then a (Column1, Column2, Column3). And the first two of that set are redundant and don't provide any significant performance improvement over a wider index in most applications.

Finding cases where you have multiple indexes like that isn't a trivial problem. Here's the answer I came up with. This particular query finds cases of a (Column1, Column2....ColumnN) with a (Column1) but it could be pretty easily modified to do a multiple left side key required. It's all filtering, just add [3] and the appropriate group by/max and you're set. And once again, yes, you could do this without a temp table, but the temp table's kind of interesting on its own.

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

select object_name(object_id) oname,

index_id, key_ordinal, Column_ID

from sys.index_columns) AS SourceTable

pivot(max(column_id)

for key_ordinal in([1], [2], [3], [4], [5],

[6], [7], [8], [9], [10], [11], [12],

[13], [14], [15], [16], [17], [18], [19])

) as PivotTable

select oname, [1], max([2]), count(*)

from #TableList

group by oname, [1]

having count(*) > 1

order by 1