sql server partitioned view execution plan questions

Question

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table)

datepart(year,[Date])=(2010)
datepart(year,[Date])=(2011)
datepart(year,[Date])=(2012)

schema for table 2010 (same for all other tables)

CREATE TABLE [dbo].[tb_Sales2010](
    [Date] [smalldatetime] NOT NULL,
    [ID] [int] NOT NULL

 CONSTRAINT [PK_tb_Sales2010] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tb_Sales2010]  WITH CHECK ADD  CONSTRAINT [CK_Date_2010] CHECK  ((datepart(year,[Date])=(2010)))
GO

My query is

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE DATEPART(YY, DATE) = 2011

My question is, the execution plan indicates that it is scanning all 3 tables. Instead of looking at the check constraint and saying table b has the information it needs. Why is that. i feel the execution plan should only show 1 table being scanned.

Statistics turned on results

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2012'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2011'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2010'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

View Definition CREATE VIEW PartitionedTb_Sales AS SELECT * FROM tb_sales2010

UNION ALL

SELECT * FROM tb_sales2011

UNION ALL

SELECT * FROM tb_sales2012

Answer 1

The DATEPART() function still has to be performed on every row before any filtering can be done/calculated.

Your view would be much more efficient if you changed it like this:

CREATE VIEW PartitionedTb_Sales 
AS 
SELECT *, 2010 AS SalesYear FROM tb_sales2010
UNION ALL
SELECT *, 2011 AS SalesYear FROM tb_sales2011
UNION ALL
SELECT *, 2012 AS SalesYear FROM tb_sales2012

Then, instead of using DATEPART() to calculate the year for each row, it will already be defined.

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE SalesYear = 2011