I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results.
If I execute the query below, where I know there is no histogram on the field, SQL Server outputs a row estimate equal to the actual the result. If I were to run the same query where I use
DECLARE @i NUMERIC(19,2) = 30.0 instead of a constant
WHERE [Rate] < @i, the estimate is
94.8 as I would expect for the shown query since the database has no statistics on the field.
So my question is why is SQL Server outputting the correct result when it has no statistics on the field?
When turning off
AUTO_CREATE_STATISTICS as indicated by my answer, I experience some odd behaviour on the row estimates.
A constraint exists on the table restricting
[Rate] to be between
6.5 - 200.0. The estimates vary a lot when experimenting depending on if
> is used or
>= as well as
64 format queries where the predicate shows an implict conversion to type
WHERE [Rate] > 6.5 the estimate is
28.44 which then stops at rates above
WHERE [Rate] > 7 where decimals are excluded, the
28.44 estimate runs all the way up to
WHERE [Rate] >= 6.5 the
28.44 estimate runs up to about
100 then changes to
17.7764 up to
1000 before the estimate drops to 1.
So there doesn't seem to be much consistency. It seems like SQL Server is aware of the constraint based on some of the queries I'm experimenting with, but it also ignores its presence at times.