0

I have a partitioned view with 20 tables. Each table has a partition key (usp_id) ranging from 1 to 20. If I query the partitioned view using the partition key then only the table with the correct usp_id is queried which is fine.

Now i have a second table which has two fields. Usp_id and insert_date. The insert_date in this table is updated daily. It is a one to one mapping in this table.

I would like to be able to query my partition view based on insert_date which then would use the usp_id to query the partitioned view.

Is this possible?

Many thanks in advance!

user3167318
  • 31
  • 1
  • Does the insert_date have any relationship to the usp_id? And, what version of SQL Server (2005+? Enterprise or Standard) are you using? – Stuart Ainsworth Jan 07 '14 at 03:41
  • Hi Stuart. The second table has usp_id and an insert_date. Every day the oldest insert_date is replaced with the current_date. There is a one to one mapping between the dates and the usp_id but the dates continually change. The version is Enterprise 2008. Thanks for you help. – user3167318 Jan 08 '14 at 08:09
  • Since you're on Enterprise edition, have you considered moving to a partitioned table instead of a partitioned view? With a table, you could define a foreign key cosntraint to your lookup table, which should invoke partition exclusioning. – Stuart Ainsworth Jan 08 '14 at 13:29
  • Hi Stuart. Yep, I have, but I am not sure how to achieve what I need to using a partition table. Basically I need to partition on the insert date for each day the data is inserted and delete a partition every day keeping 60 partitions in total. I posted another question on stackoverflow in regards to this. "Sql Server 2008 partition table based on insert date" Thanks again Stuart! – user3167318 Jan 12 '14 at 04:52

0 Answers0