I have a large table consisting of approximately 500 M records. Going further, since all the records are not always accessed, planning to partition based on a date column, which is suitable for partition the entire data into several partitions. In order to get the maximum benefit out of it, do I need to make the changes in all the dependent queries? Is it mandatory to have the partition key column included in where clauses, even if I align all the existing indexes with the partition keys column?
A quick google search found the answer:
https://technet.microsoft.com/en-us/library/ms187924(v=sql.105).aspx *When querying data or performing updates, there is no difference in the way you reference a partitioned table versus a table that is not partitioned* You don't have to change the underlying queries. You query a partitioned table just like you do any other table. That's one of the benefits of partitioning. Hope that helps.!