Estimated row count with BETWEEN and = and logical bit and

 create table t (id int not null identity primary key, status int not null);

--fill table decent amount of data (mine has 1.5million rows).

--the status column can 1s

 

select *

from t

where id between 30000 , 40000

 

the above sql produces , estimated row count of 1180

 

select *

from t

where id between 30000 , 40000

    , status & 0x1f  = 1

 

this 1 produces , estimated row count of 33

 select *

from t

where id between 30000 , 40000

    , status & 0x1f  between 1 , 1

 

and 1 106.

 

does know why?  sql guessing?  numbers in second , third queries come from?

--

--

dan

 

---

-- dan

it depends on collected statistics of table.

 

usually selectivity of "=" less "between", suppose, if estimates 10% of rows selected equality condition, estimates around 30% not-equality based conditon.   

so, first estimated 1180 rows condition of "id between 30000 , 40000"

then when estimated "status & ox1f = 1", if estimates 5% of rows satisfy(percentage depends on sampling values of statistics), calculate 5% of 1180 = around 60 rows...

then condition, "status & ox1f between 1 , 1"  as not equality condition, estimate fat higher 5%. if estimates around 15%, calculate 15% of 1180 = 170 rows etc..

 

this whitepaper give details how calculated... 

http://technet.microsoft.com/en-us/library/cc966419.aspx

 

SQL Server  >  Transact-SQL