Database Reference
In-Depth Information
■
there is always an exception to the rule. Consider a shopping Cart system, which allows for searching products
by part of the name. there are several ways to implement this feature, though when the table is small enough, an index scan
operation on the nonclustered index on the
Name
column may provide acceptable performance. in such a scenario, you want to
have the index as compact as possible to reduce its size and the number of reads during a scan operation. thus you can
consider keeping a separate nonclustered index on the
Name
column, even when this index can be consolidated with other ones.
Note
The script shown in Listing 6-14 returns information about potentially redundant indexes with the same leftmost
column defined. Figure
6-9
shows the result of the execution.
Listing 6-14.
Detecting potentially redundant indexes
select
s.Name + N'.' + t.name as [Table]
,i1.index_id as [Index1 ID], i1.name as [Index1 Name]
,dupIdx.index_id as [Index2 ID], dupIdx.name as [Index2 Name]
,c.name as [Column]
from
sys.tables t join sys.indexes i1 on
t.object_id = i1.object_id
join sys.index_columns ic1 on
ic1.object_id = i1.object_id and
ic1.index_id = i1.index_id and
ic1.index_column_id = 1
join sys.columns c on
c.object_id = ic1.object_id and
c.column_id = ic1.column_id
join sys.schemas s on
t.schema_id = s.schema_id
cross apply
(
select i2.index_id, i2.name
from
sys.indexes i2 join sys.index_columns ic2 on
ic2.object_id = i2.object_id and
ic2.index_id = i2.index_id and
ic2.index_column_id = 1
where
i2.object_id = i1.object_id and
i2.index_id > i1.index_id and
ic2.column_id = ic1.column_id
) dupIdx
order by
s.name, t.name, i1.index_id
Figure 6-9.
Potentially redundant indexes
