Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
stackoverflow.com/questions/7655010/… –  user9399 Jul 31 '12 at 18:47
add comment

migrated from stackoverflow.com Jul 31 '12 at 18:27

This question came from our site for professional and enthusiast programmers.

1 Answer

Assuming that you have the primary key on a clustered index then the partitioning key needs to be part of the primary key.

You will not loose the benefit of partitioning by joining to non-partitioned tables, providing that the queries are designed to make use of the partitioned table, for example the following query WILL benefit from partitioning

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE F.Col1 = 5

But the following query WILL NOT benefit from partition elimination

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE D.Col1 = 5

It is a subtle difference, but in the first query, the join key is filtered in the partitioned table, taking advantage of elimination and then joined to the dimension. In the second query, the key is filtered in the dimension and then joined against the whole of the fact table, rather than just required partitions.

It goes without saying that the partitioning key needs to be in the WHERE clause for elimination to work, otherwise SQL Server does not know which partition(s) the data is in.

Adding a filter criteria on the JOIN clause will not help you. It needs to be in the WHERE clause to benefit from elimination.

The Partition Key does not need to be part of a non-clustered index (NCI) but if the NCI is unique, then it needs to contain the partitioning key in order to align the index. This is where the NCI is built on the same partition scheme as the table. NCIs should also be partition aligned unless there is an exceedingly good reason not to. I have never come across a good enough reason!

share|improve this answer
    
+1 for the clear and concise answer with lots of detail. –  Max Vernon Sep 25 '12 at 14:15
    
For an interesting trap with partition elimination, see bit.ly/PartElim –  Paul White Sep 26 '12 at 13:00
1  
Regarding the case for non-aligned NCIs, it is possible to make a case for them where MIN/MAX/TopN Sort is used. Details at sqlmag.com/article/microsoft-products/… and bug report: connect.microsoft.com/SQLServer/feedback/details/240968/… –  Paul White Sep 26 '12 at 13:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.