UMG Developer (5/4/2010)

---

What happens if you use a sub query to get the minimum value for each partition, and then get the min of that?

SELECT MIN(MinIntDate)
FROM (SELECT PartitionID, MIN(IntDate) MinIntDate
	FROM dbo.ExampleTablePartitioned
	GROUP BY PartitionID) a

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).

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

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.)

Solomon Rutzky (5/4/2010)

---

...you make a good point about the non-partitioned index. However, it is not that applicable in my situation. The index comes with two costs: disk space (in this case over 1 GB!) and it needs to be maintained across INSERT and DELETE operations.

Solomon Rutzky (5/4/2010)

---

Hello Alen and thanks. I am surprised that you cannot find 2 TB of available space given that I keep hearing how "disk is cheap" . Good luck!

Take care,
Solomon...