Range query against date column works very poorly compared to fixed values.

GokhanVarol 2016-05-27 17:17:43

TraceFlag 9481 is turned on globally. SQL Server 2014 Enterprise SP1. Object2.Column1 columns is defined as date data type (not datetime, just date). For some reason using a range of dates creates a very poor query plan vs using actual values produces much better even though estimates coming out of the seek seem identical.

alt text

alt text

link text

SQLkiwi 2016-05-29 19:40:40
The cardinality estimate in the final plan might look the same, but that does not mean the derived statistics (including histogram) at that point were identical. Most likely, they are not, which would go a long way to explaining the different plan selections.

In general, you cannot assume that two logically identical predicates will get estimated the same way. So x < date < y is not necessarily the same as writing the intervening dates out by hand, and using IN. If I remember correctly, there was some logic of this sort for integers enabled under the original CE model when TF 2301 was enabled, but I do not recall seeing that this would apply to date data types. You could try it though.