471,049 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Inner Join on Partitioned View

Hello All,

I have managed to create a partitioned view ona large table - the view is updateable, and looking at the execution plan, only the relevant table is queried.

The partition is by month - we have a date dimension that I join to this table in queries e.g. query below, and again - only one table is scanned

SELECT COUNT(*) FROM
VF_Fact_Call_Rec a
INNER JOIN
date_Dim b ON a.DateID=b.DateID
WHERE b.dateID =20070101

However, when I run this query -

SELECT COUNT(*) FROM
VF_Fact_Call_Rec a
INNER JOIN
date_Dim b ON a.DateID=b.DateID
WHERE b.[Year Month] = '200701'

it scans ALL tables, should this be the case - since the only ID's pulled in relate to a single month...surely this defeats the object of partitioning, as the only time it will scan less tables is if I explicitly query the DateID (which users will not do!)

Am I missng something behind the theory of partitioning i.e. is this normal behaviour of SQL Server 2000 (we have SP4 on the box)

Thanks

Tobias
Feb 19 '07 #1
0 1223

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by michaelnewport | last post: by
3 posts views Thread by LemonHead | last post: by
4 posts views Thread by Nathan | last post: by
3 posts views Thread by Zeff | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.