partition function in SQL Server 2005

Question

In MSDN about partition function from here, $PARTITION(Transact-SQL).

I am confused about what the below sample is doing underlying. My understanding is, this SQL statement will iterate all rows in table Production.TransactionHistory, and since for all the rows which will mapping to the same partition, $PARTITION.TransactionRangePF1(TransactionDate) will return the same value, i.e. the partition number for all such rows. So, for example, all rows in partition 1 will result in one row in returning result since they all of the same value of $PARTITION.TransactionRangePF1(TransactionDate). My understanding correct?

USE AdventureWorks ;
GO
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition, 
COUNT(*) AS [COUNT] FROM Production.TransactionHistory 
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
GO

Answer 1

It returns the number of records in each of the non-empty partitions in the partitioned table Production.TransactionHistory, so yes your reasoning is correct.

Answer 2

Have you tried generating an execution plan for the statement? That might give you some insight into what it's actually doing underneath the cover.

Press "Control-L" to generate an execution plan and post it here if you'd like some interpretation.

Answer 3

If your parition function is defined like

CREATE PARTITION FUNCTION TransactionRangePF1(DATETIME)
AS RANGE RIGHT FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01')

, then this clause:

$PARTITION.TransactionRangePF1(TransactionDate)

is equivalent to:

CASE
  WHEN TransactionDate < '2007-01-01' THEN 1
  WHEN TransactionDate < '2008-01-01' THEN 2
  WHEN TransactionDate < '2009-01-01' THEN 3
  ELSE 4
END

If all your dates fall before '2007-01-01', then the first WHEN clause will always fire and it will always return 1.

The query you posted will return at most 1 row for each partition, as it will group all the rows from the partition (if any) into one group.

If there are no rows for any partition, no rows for it will be returned in the resultset.