Sql Server 2005 Check Constraint not being applied in execution when using variables

Here is some SQL sample code:

--Create 2 Sales tables with constraints based on the saledate
create table Sales1(SaleDate datetime, Amount money)
ALTER TABLE dbo.Sales1 ADD CONSTRAINT
    CK_Sales1 CHECK (([SaleDate]>='01 May 2010'))
GO
create table Sales2(SaleDate datetime, Amount money)
ALTER TABLE dbo.Sales2 ADD CONSTRAINT
    CK_Sales2 CHECK (([SaleDate]<'01 May 2010'))
GO

--Insert some data into Sales1
insert into Sales1 (SaleDate, Amount)
values ('02 May 2010', 50)
insert into Sales1 (SaleDate, Amount)
values ('03 May 2010', 60)
GO

--Insert 开发者_开发问答some data into Sales2
insert into Sales2 (SaleDate, Amount)
values ('30 Mar 2010', 10)
insert into Sales2 (SaleDate, Amount)
values ('31 Mar 2010', 20)
GO

--Create a view that combines these 2 tables
create VIEW [dbo].[Sales]
AS
    SELECT SaleDate, Amount FROM Sales1
    UNION ALL
    SELECT SaleDate, Amount FROM Sales2
GO

--Get the results
--Query 1
select * from Sales where SaleDate < '31 Mar 2010' -- if you look at the execution plan this query only looks at Sales2 (Which is good)

--Query 2
DECLARE @SaleDate datetime
SET @SaleDate = '31 Mar 2010'
select * from Sales where SaleDate < @SaleDate -- if you look at the execution plan this query looks at Sales1 and Sales2 (Which is NOT good)

Looking at the execution plan you will see that the two queries are differnt. For Query 1 the only table that is accessed is Sales1 (which is good). For Query 2 both tables are accessed (Which is bad). Why are these execution plans different, and how do i get Query 2 to only access the relevant table when variables are used?

I have tried to add indexes for the SaleDate column and that does not seem to help.

A query plan is cached for reuse, so it is created for the general case. The cached plan has to be valid for all possible values of @SalesDate. That means the query plan must look in both views.

You can avoid that by running dynamic sql:

declare @sql varchar(max)
set @sql = 'select * from Sales where SaleDate < ' + @SaleDate
exec (@sql)

There is an option to optimize a query for a specific value of a variable, like:

SELECT * 
FROM Sales 
WHERE SaleDate < ' + @SaleDate
OPTION ( OPTIMIZE FOR (@SalesDate = '2010-01-01') )

But SQL Server would still look at both tables, because it has to return the correct results for 2009-01-01 even if it's trying to optimize for 2010-01-01.

This is as expected. The plan is now reusable with any value in the variable.

With a literal value, it doesn't bother with resuability because the plan changes if the literal changes.

Now, I had an excellent link describing this behaviour.. the key word being had...

Edit:

• Conor's article that is related about parameterisation.
• SO: TSQL Constants… Use Variable or Literal?