September 23, 2003 at 11:40 am
I'm a little confused with respect to partitioned views.
Let's say I have a series of tables that contain quarterly data (ie. Subscriber_2002_Q1, Subscriber_2002_Q2 etc). Each table has a date field with a CHECK constraint on it to ensure that only records that fall within the appropriate quarter are allowed to be inserted.
The four quarterly tables are then joined in a view using UNION_ALL to create a partitioned view called MonthlySubscriber.
I understand that querying the view and using the date field in the WHERE clause will make things faster than one massive table but can I also use the view to do INSERTs and it to determine the correct table to insert the record into? If so, is there and performance gain/penalty to doing so?
September 23, 2003 at 2:53 pm
quote:
I'm a little confused with respect to partitioned views.Let's say I have a series of tables that contain quarterly data (ie. Subscriber_2002_Q1, Subscriber_2002_Q2 etc). Each table has a date field with a CHECK constraint on it to ensure that only records that fall within the appropriate quarter are allowed to be inserted.
The four quarterly tables are then joined in a view using UNION_ALL to create a partitioned view called MonthlySubscriber.
I understand that querying the view and using the date field in the WHERE clause will make things faster than one massive table but can I also use the view to do INSERTs and it to determine the correct table to insert the record into? If so, is there and performance gain/penalty to doing so?
Yes, and there is a usually a performance penalty. Check out the rules and requirements for Updatable Partitioned Views in BOL.
--Jonathan
--Jonathan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply