XML Feeds
The selectivity of an index is extremely important. If your index is not selective enough then the optimizer will simply have to do a scan. This is also a reason why creating an index on a gender column does not make a lot of sense.
First create this table
tsql
- USE tempdb
- go
- CREATE TABLE TestCompositeIndex (STATE CHAR(2),Zip CHAR(5))
- INSERT TestCompositeIndex VALUES('NJ','08540')
- INSERT TestCompositeIndex VALUES('NJ','08540')
- INSERT TestCompositeIndex VALUES('NY','10028')
- INSERT TestCompositeIndex VALUES('NY','10021')
- INSERT TestCompositeIndex VALUES('NY','10021')
- INSERT TestCompositeIndex VALUES('NY','10021')
- INSERT TestCompositeIndex VALUES('NY','10001')
- INSERT TestCompositeIndex VALUES('NJ','08536')
- INSERT TestCompositeIndex VALUES('NJ','08540')
If you have a composite index (composite means the index contains more than one column) you need to run this code.
tsql
- DECLARE @COUNT INT
- SELECT DISTINCT STATE, Zip
- FROM TestCompositeIndex;
- SET @COUNT = @@ROWCOUNT;
- SELECT (@COUNT*1.0) / COUNT(*) AS IndexSelectivity,
- COUNT(*)AS TotalCount,
- @COUNT AS DistinctCount
- FROM TestCompositeIndex;
Result
--------
IndexSelectivity TotalCount DistinctCount
.555555555555 9 5
If you have a one column index you can use this code
tsql
- SELECT (COUNT(DISTINCT STATE)* 1.0) / COUNT(*) AS IndexSelectivity,
- COUNT(*) AS TotalCount,
- COUNT(DISTINCT STATE) AS DistinctCount
- FROM TestCompositeIndex;
Result
--------
IndexSelectivity TotalCount DistinctCount
.222222222222 9 2
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.