Hey Paul. Actually, I had not had time to test the CROSS APPLY approach and was answering in terms of my solution against the simple WHERE clause solutions. I did just test your example and the result was rather interesting in that it depends on which metric you go by.

If you go by query cost (based on Actual Execution Plan), then the CROSS APPLY + $PARTITION is slightly faster coming in at 0.0387787 as opposed to my solution which has a total cost of 0.1361422 (which is not a huge difference but still one definitely costs less).

If you go by STATISTICS IO and STATISTICS TIME, then my solution is slightly faster. Here is the output for the CROSS APPLY + $PARTITION query:

Table 'ExampleTable'. Scan count 11, logical reads 48, physical reads 38, read-ahead reads 0.

Table 'sysrowsets'. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 33.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 340 ms.

Whereas my approach shows an aggregate "elapsed time" of just 8 ms and "cpu time" of 0 ms while IO stats show aggregates of 11 Scans, 59 Logical reads, 0 Physical reads, and 0 Read-ahead reads.

Now, as far as dynamic goes, the solution I presented here was simplified for the example. In our systems where I work we have a dynamic approach to get the highest PartitionID since we store those along with the CustomerIDs. However, I do appreciate that your approach (and maybe also that of Ben-Gan) is dynamic in more situations than mine since my approach assumes a non-sparse list of numbers (or something sequential). For my purposes that is all it will ever be but it is good for people to have another approach if their data is not as predictable (or in my situation if we skipped PartitionID 11 and went to 12--which we won't do but that would expose the issue).

As far as set-based approaches go, yes, the CROSS APPLY + $PARTITION approach is more ideal.

Take care,

Solomon...

[edited to add the following:]

PS, for the sake of being thorough, I added in the dynamic MAX(PartitionID) lookup that we use since that would more so equate to the fully dynamic approach of using CROSS APPLY + $PARTITION. That added 1 Scan and 24 logical reads to my approach. The new total is 12 Scans and 83 logical reads which is the same as the CROSS APPLY + $PARTITION approach.

And just to be more thorough, I then tested this in another environment and the STATISTICS showed no physical reads for the CROSS APPLY + $PARTITION approach and an elapsed time of 1 ms. So now they appear to be equal.