How to get the selectivity of an index

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

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.

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

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