SQL Server Magazine

To tell the difference between statistics and indexes, try this technique. Using the IsStatistics property to call the INDEXPROPERTY() function will return a 1 if the name refers to non-index statistics or a 0 if the name refers to an index. About the IsStatistics property, SQL Server Books Online (BOL) says: "Statistics indexes are used as a placeholder for column-level statistics." However, there's no such thing as a "statistics index;" there are indexes, and there are column-level statistics. IsStatistics refers to column statistics, whether automatically or manually created. Alternatively, using the IsAutoStatistics parameter when calling the INDEXPROPERTY() function returns a 1 only for statistics that SQL Server automatically created and returns 0 for all manually created statistics and indexes.

Another special type of row that can appear in sysindexes is a hypothetical index. The Index Tuning Wizard creates hypothetical indexes as it tests various possibilities; the names look something like hind_c_2133582639_1A, where hind stands for Hypothetical INDex. Calling INDEXPROPERTY with the IsHypothetical property tells you whether a name in sysindexes refers to one of these indexes. SQL Server automatically removes these indexes after a while, so don't worry if you see them occasionally if you examine the sysindexes table before SQL Server has cleaned them up.

The more you know about indexes and the statistics on tables, the more easily you'll be able to understand the query performance you see, and you'll know why the query optimizer chooses a particular plan for a query. Next time, I'll discuss more of the columns in the sysindexes table and look at other tools you can use for examining index structures.

End of Article

Prev. page     1 [2]     next page -->