I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning. The database is on SQL Server 2008 r2. I'm inheriting this from someone else and found something I didn't expect in some of their code. In some cases, I've seen that adding WHERE $partition.archiving_Partition_function(MyTable.IsArchived) = 1 to the Where clause improves query performance.

(The client wants to "archive" old data by partitioning it into an "archive" partition. They can't use dates, since some projects are considered old after different time intervals. So they want to manually flip a bit to archive it.)

So, I have a handful of tables on which I have modified the clustered index in order to partition the table. (This is my first go-around with partitioning, so bear with me if my terminology is lacking.) And I've been performance tuning several queries, often adding something like Where MyTable.IsArchived = CONVERT(BIT,0) in order to restrict to a single partition. However, some queries perform better when I replace MyTable.IsArchived = CONVERT(BIT,0) with $partition.archiving_Partition_function(MyTable.IsArchived) = 1. This surprises me. My expectation is that the latter simply passes MyTable.IsArchived to the partition function and compares the result to an integer constant. It isn't sargable and doesn't look like it would help determine what index is best. It looks like it simply adds some overhead by calling a function, whereas the former code does the same thing... Compare the bit value to some constant. Except that the former code follows the guidelines to help use the clustered index.

So, I guess my question is, "What's going on under the covers with $partition.MyPartitionFunction? It looks to me like unnecessary overhead, but sometimes performs better than checking the partition column directly. And I can't seem to find any documentation that sheds light on this.


  • As a related note, Paul White has a blog post that comes very close to answering my question. He even uses the partition function in a where clause to restrict to a single partition. But I still don't see why this could affect performance any better than using an equality operator on the partition column, directly. (One benefit that I do see is not having to worry about changes to the partition function, though. But that's not my point of interest.) – DeadZone Jan 27 '15 at 19:21
  • I know this has been a good long while since you asked this question, but can you generate a small mcve for this to help others who may want to answer this? – Max Vernon Apr 24 '18 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.