Querying a Partitioned Table in SQL SERVER
1. Querying the data from a particular partition
The Syntax is as follows
SELECT < Column Name1 >…/* FROM < Table Name > WHERE $PARTITION.< Partition Function Name >(< Column Name >) = < Partition Number >
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2 and so on.
The below examples returns all the records in the partition 1.
SELECT * FROM EMP WHERE $PARTITION.EmployeePartitionLeft (EMPLOYEEID) = 1
2. Querying for Knowing the Partition Number
The syntax is as follows
SELECT $PARTITION.< Partition Function Name >(< Column Name >) = < Partition Range Value >
The below example returns 2 as the partition number for partition range >1 and less than equal to 100.
SELECT $PARTITION.EmployeePartitionLeft(100)
3. Querying to find the count of records in each partition
The syntax is as follows
SELECT $PARTITION.< Partition Function Name >(< Column Name >), COUNT(*) FROM < Table Name > GROUP BY $PARTITION.< Partition Function Name >(< Column Name >)
The below example returns the number of records in each partitions.
SELECT $PARTITION.EmployeePartitionLeft (EMPLOYEEID) AS Partition, COUNT(*) AS [COUNT] FROM EMPLOYEE GROUP BY $PARTITION.EmployeePartitionLeft (EMPLOYEEID) ORDER BY Partition ;
The Syntax is as follows
SELECT < Column Name1 >…/* FROM < Table Name > WHERE $PARTITION.< Partition Function Name >(< Column Name >) = < Partition Number >
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2 and so on.
The below examples returns all the records in the partition 1.
SELECT * FROM EMP WHERE $PARTITION.EmployeePartitionLeft (EMPLOYEEID) = 1
2. Querying for Knowing the Partition Number
The syntax is as follows
SELECT $PARTITION.< Partition Function Name >(< Column Name >) = < Partition Range Value >
The below example returns 2 as the partition number for partition range >1 and less than equal to 100.
SELECT $PARTITION.EmployeePartitionLeft(100)
3. Querying to find the count of records in each partition
The syntax is as follows
SELECT $PARTITION.< Partition Function Name >(< Column Name >), COUNT(*) FROM < Table Name > GROUP BY $PARTITION.< Partition Function Name >(< Column Name >)
The below example returns the number of records in each partitions.
SELECT $PARTITION.EmployeePartitionLeft (EMPLOYEEID) AS Partition, COUNT(*) AS [COUNT] FROM EMPLOYEE GROUP BY $PARTITION.EmployeePartitionLeft (EMPLOYEEID) ORDER BY Partition ;
Comments