Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari iniPelayar ini tidak lagi disokong.
Naik taraf kepada Microsoft Edge untuk memanfaatkan ciri, kemas kini keselamatan dan sokongan teknikal yang terkini.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Returns the partition number into which a set of partitioning column values can be mapped for any specified partition function.
Transact-SQL syntax conventions
[ database_name. ] $PARTITION.partition_function_name(expression)
The name of the database that contains the partition function.
The name of any existing partition function against which a set of partitioning column values are being applied.
An expression whose data type must either match or be implicitly convertible to the data type of its corresponding partitioning column. This parameter can also be the name of a partitioning column that currently participates in partition_function_name.
int
$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.
This example creates a partition function RangePF1
using RANGE LEFT 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.
CREATE PARTITION FUNCTION RangePF1(INT)
AS RANGE LEFT
FOR VALUES (10, 100, 1000);
GO
SELECT $PARTITION.RangePF1 (10);
GO
This example shows how to use $PARTITION
to return the number of rows in each partition of table that contains data.
Nota
To execute this example, you must first create the partition function RangePF1
using the code in the previous example.
Create a partition scheme, RangePS1
, for the partition function RangePF1
.
CREATE PARTITION SCHEME RangePS1
AS PARTITION RangePF1
ALL TO ('PRIMARY');
GO
Create a table, dbo.PartitionTable
, on the RangePS1
partition scheme with col1
as the partitioning column.
CREATE TABLE dbo.PartitionTable
(
col1 INT PRIMARY KEY,
col2 CHAR (20)
) ON RangePS1 (col1);
GO
Insert four rows into the dbo.PartitionTable
table. These rows are inserted into partitions based on the partition function RangePF1
definition: 1
and 10
go to partition 1
, while 500
and 1000
go to 3
.
INSERT dbo.PartitionTable (col1, col2)
VALUES (1, 'a row'),
(10, 'another row'),
(500, 'another row'),
(1000, 'another row');
GO
Query the dbo.PartitionTable
and uses $PARTITION.RangePF1(col1)
in the GROUP BY
clause to query the number of rows in each partition that contains data.
SELECT $PARTITION.RangePF1 (col1) AS Partition,
COUNT(*) AS [COUNT]
FROM dbo.PartitionTable
GROUP BY $PARTITION.RangePF1 (col1)
ORDER BY Partition;
GO
Here's the result set.
Partition | COUNT |
---|---|
1 | 2 |
3 | 2 |
Rows aren't returned for partition number 2
, which exists but doesn't contain data.
The following example returns all rows that are in partition 3 of the table PartitionTable
.
SELECT col1, col2
FROM dbo.PartitionTable
WHERE $PARTITION.RangePF1 (col1) = 3;
Here's the result set.
col1 | col2 |
---|---|
500 |
another row |
1000 |
another row |
Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari ini