Sql Server 2005 Check Constraint not being applied in execution when using variables
- by DarylS
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.
