We are planning to use table partitioning for our database based on an integer column. I have read about it on MSDN at http://msdn.microsoft.com/en-us/library/ms190787.aspx and have few queries.
a) Is it required that partition key be part of primary key? Even if it is not required still would it be of any benefit if it is part of primary key or clustered index?
b) In our case, we would be performing JOIN operation of partitioned tables with non-partitioned master data. Will I loose the performance gained by partition elimination? In general, if any of the tables used in JOIN operation is non-partitioned then will I loose the benefit of Partitioning?
c) Does adding filter criteria in JOIN criteria over Partition key help in any way in partition elimination? Or only the filter criteria in WHERE clause over Partition column is used for Partition elimination?
d) Should partition key be part of non-clustered index?
Thanks in advance.