2 added 501 characters in body edited Jun 6 '18 at 7:01 KumarHarsh 1,09966 silver badges99 bronze badges Formula for Calculating Selectivity: Suppose you need to calculate selectivity of Col1 in table1 ``````Declare @distinctcount int,@Totoalrows int Select @distinctcount =count(distinct col1) from table1 select @Totoalrows =count(*) from table1 So Selectivity=@distinctcount /cast(@Totoalrows as decimal(5,2)) `````` Ideal `Selectivity =1` i.e that is unique key. So Selectivity should be very near to 1. We know not to place index on bit column, 50/50. "Rows with 50/50 distribution, it might buy you very little performance gain " Bit column alone is not very Selective but we can put bit column in `composite index` to gain performance. Or we can use bit column in `Filtered index` to gain performance. This article is stating around 31%? Read the article carefully. This article is saying Number of data page read during query also matter beside Selectivity. Two table both with identity column as index is considered to be Ideal Selective. Table `manyrows` with all int column occupy less data page.So less data page are read,so Selectivty of 0.16% is enough. For table with `fewrows` with one column as `char(4000)` contain many data pages.So because lot of data page will be read,Selectivity is 31%.. Remember if optimizer has to read data across many data page then optimizer may decide to Scan than seek i.e. Selectivity of Index is ignored. Formula for Calculating Selectivity: Suppose you need to calculate selectivity of Col1 in table1 ``````Declare @distinctcount int,@Totoalrows int Select @distinctcount =count(distinct col1) from table1 select @Totoalrows =count(*) from table1 So Selectivity=@distinctcount /cast(@Totoalrows as decimal(5,2)) `````` Ideal `Selectivity =1` i.e that is unique key. So Selectivity should be very near to 1. We know not to place index on bit column, 50/50. "Rows with 50/50 distribution, it might buy you very little performance gain " Bit column alone is not very Selective but we can put bit column in `composite index` to gain performance. Or we can use bit column in `Filtered index` to gain performance. This article is stating around 31%? Read the article carefully. This article is saying Number of data page read during query also matter beside Selectivity. Formula for Calculating Selectivity: Suppose you need to calculate selectivity of Col1 in table1 ``````Declare @distinctcount int,@Totoalrows int Select @distinctcount =count(distinct col1) from table1 select @Totoalrows =count(*) from table1 So Selectivity=@distinctcount /cast(@Totoalrows as decimal(5,2)) `````` Ideal `Selectivity =1` i.e that is unique key. So Selectivity should be very near to 1. We know not to place index on bit column, 50/50. "Rows with 50/50 distribution, it might buy you very little performance gain " Bit column alone is not very Selective but we can put bit column in `composite index` to gain performance. Or we can use bit column in `Filtered index` to gain performance. This article is stating around 31%? Read the article carefully. This article is saying Number of data page read during query also matter beside Selectivity. Two table both with identity column as index is considered to be Ideal Selective. Table `manyrows` with all int column occupy less data page.So less data page are read,so Selectivty of 0.16% is enough. For table with `fewrows` with one column as `char(4000)` contain many data pages.So because lot of data page will be read,Selectivity is 31%.. Remember if optimizer has to read data across many data page then optimizer may decide to Scan than seek i.e. Selectivity of Index is ignored. 1 answered Jun 6 '18 at 6:51 KumarHarsh 1,09966 silver badges99 bronze badges Formula for Calculating Selectivity: Suppose you need to calculate selectivity of Col1 in table1 ``````Declare @distinctcount int,@Totoalrows int Select @distinctcount =count(distinct col1) from table1 select @Totoalrows =count(*) from table1 So Selectivity=@distinctcount /cast(@Totoalrows as decimal(5,2)) `````` Ideal `Selectivity =1` i.e that is unique key. So Selectivity should be very near to 1. We know not to place index on bit column, 50/50. "Rows with 50/50 distribution, it might buy you very little performance gain " Bit column alone is not very Selective but we can put bit column in `composite index` to gain performance. Or we can use bit column in `Filtered index` to gain performance. This article is stating around 31%? Read the article carefully. This article is saying Number of data page read during query also matter beside Selectivity.