UMG Developer (5/4/2010)
I would hope it was similar to the other cross apply that Paul supplied. (I guess the real problem there is that you would have to have an index with the PartitionID as the first column to get the best performance and using Sys.Partitions avoids that.)
Ah, sadly no - that APPLY results in two full scans of the partitioned index. You can use the test rig code to test for yourself - just don't create the non-partitioned index. You have to include the $PARTITION function to help the optimiser along.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi