Can I prevent SQL Server from consolidating histogram steps in statistics?

@Utwigg: thanks for adding an example query for my repro script that highlights the problem in a more practical sense.

@wBob: interesting find, about that one particular rule, but it is not what I am seeing (on version 10.0.5500.0). Because if you force a Merge Join (regardless of the method), you will see that the optimizer estimates that it will return just 1 row to the consumer (instead of 348585 rows).

Also - and you can use the repro script I posted earlier - if you change int to bigint or decimal(10,0), it make no difference. Also, subtracting the value 2,000,000,000 from all rows also doesn't make a difference. Nor does using the data type decimal(10,2) with values like 20130115.02.

If you ask me, this is a bug in the estimate of the seek of the FactTable (not the DimDatum table). Because the estimate for the date range DatumID >= 2013011500 is 1 row, but the estimate for the date range DatumID >= 2013011502 is 348585 rows. That is mathematically impossible.

SELECT f.DatumID,f.SomeFacts,dd.LastVersion
FROM dbo.FactTable f
INNER JOIN dbo.DimDatum AS dd
  ON  f.DatumID = dd.DatumID
  AND dd.LastVersion = 1
WHERE dd.DatumID >= 2013011500 AND dd.DatumID < 2013011600
AND   f.DatumID  >= 2013011502 AND f.DatumID  < 2013011600
OPTION(RECOMPILE)

---

Maybe the optimizer is forgetting to incorporate the EQ_ROWS value of the statistics that fall in the range?

Gert-Jan