Solomon Rutzky (5/4/2010)
Hello. I just tried your code and it had the same effect as the regular SELECT MIN(IntDate). It took 10 - 11 seconds each time. There is really no getting around using the Partition Key in the WHERE clause (outside of doing a non-partitioned Index like Paul mentioned).
What about the other version I edited in:
SELECT MIN(MinIntDate)
FROM (SELECT PartitionID FROM dbo.ExampleTable GROUP BY PartitionID) a
CROSS APPLY (SELECT MIN(IntDate) MinIntDate
FROM dbo.ExampleTable b
WHERE B.PartitionID = a.PartitionID) c
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.)