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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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')
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.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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;
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

T-SQL
1
2
3
4
SELECT (COUNT(DISTINCT State)* 1.0) / COUNT(*) AS IndexSelectivity,
COUNT(*) AS TotalCount,
COUNT(DISTINCT State) AS DistinctCount
FROM TestCompositeIndex;
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