-- 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;

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

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