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
Same vendor, different issue. How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality? Tables should have a unique index. Of some kind. If it's a formal PK, or a unique constraint or a unique index, it needs have *something* unique enforced, or it's not really a relational table, but is instead a pile of unknown quality data.
select so.name
from sys.objects so
left join sys.indexes si
on si.object_id = so.object_id
and si.is_unique = 1
where si.index_id is null
and so.type_desc = 'user_table'
order by 1
428 tables with no unique indexes. This goes with the 36 completely duplicated indexes and 50+ partially duplicated indexes.
And by the way, I checked. is_primary_key implies is_unique, and is_unique_constraint implies is_unique.
So I wanted to start being a bit more aggressive about index management. Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases. So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them.
Here’s what I came up with. Only really works if you have 19 or fewer columns per index. Why 19? I got tired of typing there. You can write this up as a code generator if you don’t like it. There are better ways to do this, but the way I’m doing it you can look at the temp table and use it to find the more subtle problem of a table with two indexes which are subsets of each other, which are usually but not always redundant. Anyway, here ya go.
drop table #tablelist
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(key_ordinal)
for column_id 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], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13],
[14], [15], [16], [17], [18], [19]
from #TableList
group by oname, [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14],
[15], [16], [17], [18], [19]
having count(*) > 1
order by 1