0

In our MSSQL server 2008 R2 environment, we have a table which we have partitioned based on a yearly partition function.

We need to define a view on this table and want to take advantage of table partitioning while running query on this query.

From following text quoted from MSDN I understand that we will need to create a CHECK constraint on our partitioned tables.

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column.

Please suggest how I can achieve this. If, in case, I am wrong in my approach, please help me correct the same.

Puneet Jain
  • 21
  • 1
  • 5
  • partitioned tables and partitioned views are different things. If you already have a partitioned table, you don't need to define a partitioned view on it, and so the text you're quoting is irrelevant. – Damien_The_Unbeliever Sep 23 '15 at 06:44
  • @Damien_The_Unbeliever We need a view which is a sub-table from our partitioned table with some manipulation. While querying on this view we want to take advantage of partitioning on our main table. Please suggest how I can achieve this. – Puneet Jain Sep 23 '15 at 06:53
  • Write the view. Done. Like I said, the text you're quoting relates to partitioned views, which are a different thing, and not something that you need. – Damien_The_Unbeliever Sep 23 '15 at 06:55
  • But if we write a simple view then its running query across all the partitions of table. Please suggest what we can do to let query in view take advantage of partition on table. – Puneet Jain Sep 23 '15 at 07:15
  • thanks for your response, I was able to make it work by tweaking our query. We need to include our partition column in our query in view to take benefit of table partition. – Puneet Jain Sep 23 '15 at 09:20

0 Answers0