是否有人知道可以检测整个数据库中的冗余索引的T-sql脚本？表中冗余索引的示例如下：
Index 1: 'ColumnA','ColumnB','ColumnC' Index 2: 'ColumnA','ColumnB'
忽略其他注意事项,例如列的宽度和覆盖索引,索引2将是多余的.
谢谢.
解决方法
有些情况下冗余不成立.例如,假设ColumnC是一个huuge字段,但有时您必须快速检索它.您的索引1不需要键查找：
select ColumnC from YourTable where ColumnnA = 12
另一方面,索引2要小得多,因此可以在内存中读取需要索引扫描的查询：
select * from YourTable where ColumnnA like '%hello%'
所以他们并不是多余的.
如果您不相信我的上述论点,您可以找到“冗余”索引,例如：
;with ind as (
select a.object_id,a.index_id,cast(col_list.list as varchar(max)) as list
from (
select distinct object_id,index_id
from sys.index_columns
) a
cross apply
(
select cast(column_id as varchar(16)) + ',' as [text()]
from sys.index_columns b
where a.object_id = b.object_id
and a.index_id = b.index_id
for xml path(''),type
) col_list (list)
)
select object_name(a.object_id) as TableName,asi.name as FatherIndex,bsi.name as RedundantIndex
from ind a
join sys.sysindexes asi
on asi.id = a.object_id
and asi.indid = a.index_id
join ind b
on a.object_id = b.object_id
and a.object_id = b.object_id
and len(a.list) > len(b.list)
and left(a.list,LEN(b.list)) = b.list
join sys.sysindexes bsi
on bsi.id = b.object_id
and bsi.indid = b.index_id
