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
Date BETWEEN '1/1/2011 00:00:00' AND '12/31/2011 11:59:59'
?SET STATISTICS IO ON
to see what's being accessed.