Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

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

share|improve this question

migrated from stackoverflow.com Apr 1 '13 at 15:37

This question came from our site for professional and enthusiast programmers.

    
Have you tried using an inequality instead of a function result for your partition key? I.e. Date BETWEEN '1/1/2011 00:00:00' AND '12/31/2011 11:59:59'? –  JNK Apr 1 '13 at 15:12
    
@jnk same result. –  george9170 Apr 1 '13 at 15:16
    
And is there any actual data in the tables currently? –  JNK Apr 1 '13 at 16:02
    
@jnk 7 million rows split between the three tables –  george9170 Apr 1 '13 at 16:10
    
And is that an actual execution plan? Bear in mind the actuals will show numbers from estimates. I would also run the query with SET STATISTICS IO ON to see what's being accessed. –  JNK Apr 1 '13 at 16:16

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.