Detecting Overlapping Indexes in SQL Server 2005http://blogs.msdn.com/sqlprogrammability/archive/2007/06/29/detecting-overlapping-indexes-in-sql-serve......
Detecting Overlapping Indexes in SQL Server 2005
http://blogs.msdn.com/sqlprogrammability/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx
When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable. Based on this, one may be led to believe that having an index for every possible query predicate set would result in all the queries executing optimally. While true, one has to keep in mind that the indexes need to be maintained when the underlying table data in the column included in the index changes, which amounts to overhead for the database engine. So as you may guess, there are advantages of having indexes, but having too many can result in excessive overhead. This implies that you need to carefully evaluate the pros and cons before creating indexes.
In your first evaluation scenario you clearly want to avoid the case of having overlapping indexes as there is no additional value that an overlapping index provides. For example, consider a table ‘TabA’ and its three associated indexes created with the following definitions.
CREATE TABLE TabA
( Col1
1/44 1 2 3 4 5 6 下一页 尾页 |
