For SQL Server magazine subscribers, Itzik Ben-Gan has an article on exactly this issue at:

http://www.sqlmag.com/article/microsoft-products/max-and-min-aggregates-against-partitioned-tables.aspx

That was published March 16 2010.

Anyway, about this article. I'm afraid I did not rate it highly for many reasons. One shortcoming is the lack of easily consumable sample code either in-line with the text or as a download.

It would also have been good to mention that a non-partitioned index on a partitioned table does not suffer from this optimiser limitation:

-- Everyone has one of these, feel free to use another test database

USE tempdb;

GO

-- Example partition function and scheme

CREATE PARTITION FUNCTION PF(TINYINT) AS RANGE RIGHT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

GO

-- Test table (partitioned heap)

CREATE TABLE dbo.ExampleTable

(

[ExampleTableID] [bigint] NOT NULL IDENTITY(1, 1),

[CustomerID] [int] NOT NULL,

[PartitionID] [tinyint] NOT NULL,

[IntDate] [int] NOT NULL,

)

ON PS (PartitionID);

GO

-- Partitioned clustered index

-- (note: clustering key should always be unique)

CREATE CLUSTERED INDEX [PCX dbo.ExampleTable IntDate, CustomerID]

ON dbo.ExampleTable

(IntDate, CustomerID)

ON PS (PartitionID);

GO

-- Add 100,000 randomish rows

INSERT dbo.ExampleTable WITH (TABLOCK)

(CustomerID, PartitionID, IntDate)

SELECT TOP (100000)

RAND(CHECKSUM(NEWID())) * 1000 + 1,

RAND(CHECKSUM(NEWID())) * 10 + 1,

CONVERT(INTEGER, CONVERT(CHAR(8), DATEADD(DAY, 0 - RAND(CHECKSUM(NEWID())) * 365 + 1, GETDATE()), 112))

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3;

GO

-- Add partitioned primary key

ALTER TABLE dbo.ExampleTable

ADD CONSTRAINT [PK dbo.ExampleTable ExampleTableID, CustomerID, PartitionID]

PRIMARY KEY NONCLUSTERED (ExampleTableID, CustomerID, PartitionID) ON PS (PartitionID);

GO

-- Add partitioned index on CustomerID

CREATE NONCLUSTERED INDEX [PIX dbo.ExampleTable CustomerID]

ON dbo.ExampleTable (CustomerID)

ON PS (PartitionID);

GO

-- Add non-partitioned index on IntDate

CREATE NONCLUSTERED INDEX [IX dbo.ExampleTable IntDate]

ON dbo.ExampleTable (IntDate)

ON [PRIMARY];

GO

-- +++++++++++++++++++++++++++++++++++++++++++++

-- Efficient plan based on non-partitioned index

-- +++++++++++++++++++++++++++++++++++++++++++++

SELECT minimum_date = MIN(IntDate)

FROM dbo.ExampleTable;

-- ++++++++++++++++++++++++++++++++++++++++++++++

-- Alternative method to create an efficient plan

-- based partly on Itzik Ben-Gan's approach

-- +++++++++++++++++++++++++++++++++++++++++++++

SELECT min_customer_id = MIN(iTVF.partition_minimum)

FROM sys.partitions AS P

CROSS

APPLY (

-- Minimum per partition

SELECT partition_minimum = MIN(ET.CustomerID)

FROM dbo.ExampleTable ET

WHERE $PARTITION.PF(ET.PartitionID) = P.partition_number

) iTVF

WHERE P.[object_id] = OBJECT_ID(N'dbo.ExampleTable', N'U')

AND P.index_id IN (0, 1);

GO

-- Tidy up

DROP TABLE dbo.ExampleTable;

DROP PARTITION SCHEME PS;

DROP PARTITION FUNCTION PF;

Of course it is fair to say that the non-aligned index would have to be dropped and re-created to perform SWITCH operations. In mitigation, I would just say that this likely occurs in a maintenance window anyway, and indexes are pretty quick to build. One other option is to maintain both partitioned and non-partitioned indexes - the optimiser is smart enough to choose the efficient plan based on the non-partitioned index for MIN and MAX queries.

Paul