Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Left-duplicate indexes

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
            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