Export (0) Print
Expand All

$PARTITION (Transact-SQL)

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function in SQL Server 2014.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

[ database_name. ] $PARTITION.partition_function_name(expression)

database_name

Is the name of the database that contains the partition function.

partition_function_name

Is the name of any existing partition function against which a set of partitioning column values are being applied.

expression

Is an expression whose data type must either match or be implicitly convertible to the data type of its corresponding partitioning column. expression can also be the name of a partitioning column that currently participates in partition_function_name.

$PARTITION returns an int value between 1 and the number of partitions of the partition function.

$PARTITION returns the partition number for any valid value, regardless of whether the value currently exists in a partitioned table or index that uses the partition function.

A. Getting the partition number for a set of partitioning column values

The following example creates a partition function RangePF1 that will partition a table or index into four partitions. $PARTITION is used to determine that the value 10, representing the partitioning column of RangePF1, would be put in partition 1 of the table.

USE AdventureWorks2012;
GO
CREATE PARTITION FUNCTION RangePF1 ( int )
AS RANGE FOR VALUES (10, 100, 1000) ;
GO
SELECT $PARTITION.RangePF1 (10) ;
GO

B. Getting the number of rows in each nonempty partition of a partitioned table or index

The following example returns the number of rows in each partition of table TransactionHistory that contains data. The TransactionHistory table uses partition function TransactionRangePF1 and is partitioned on the TransactionDate column.

To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.

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

C. Returning all rows from one partition of a partitioned table or index

The following example returns all rows that are in partition 5 of the table TransactionHistory.

Note Note

To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.

SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft