I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723):
Declare @StartDate as date = '10/1/2014';
Declare @EndDate as date = '10/2/2014';
select Sum(Quantity)
from Daily_GC_Items
where SalesDate between @StartDate and @EndDate;
If I use string literals for the dates like this, the estimate is almost perfect (estimated rows is 75,337.4; actual is 74,723):
select Sum(Quantity)
from Daily_GC_Items
where SalesDate between '10/1/2014' and '10/2/2014'
Table definition:
[SalesDate] [date] NOT NULL,
[Store] [varchar](10) NOT NULL,
[GuestCheckNumber] [smallint] NOT NULL,
[ItemSequence] [smallint] NOT NULL,
[Quantity] [smallint] NOT NULL,
(Several more columns)
Primary Key:
[SalesDate] ASC,
[Store] ASC,
[GuestCheckNumber] ASC,
[ItemSequence] ASC
What are the possible causes for this behavior?