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'
[SalesDate] [date] NOT NULL, [Store] [varchar](10) NOT NULL, [GuestCheckNumber] [smallint] NOT NULL, [ItemSequence] [smallint] NOT NULL, [Quantity] [smallint] NOT NULL, (Several more columns)
[SalesDate] ASC, [Store] ASC, [GuestCheckNumber] ASC, [ItemSequence] ASC
What are the possible causes for this behavior?