2 added 501 characters in body
source | link

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
source | link

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.