CODEWITHSUNDEEP
sql-serverrdbms
SQLProfiler
SQLProfiler

Reputation: 111

Difference between partition_id and partition_number

Can anyone elaborate the difference between partition_number and partition_id in sys.partitions (Transact-SQL) , SQL server? Why do we need two different IDs if one could have worked?

Upvotes: 1

Views: 1749

Answers (2)

Thom A
Thom A

Reputation: 95827

From the documentation you link with added emphasis

partition_id: Indicates the partition ID. Is unique within a database.

partition_number: Is a 1-based partition number within the owning index or heap. For non-partitioned tables and indexes, the value of this column is 1.

partition_id is the id of the partition within the database. The partition_number is for the object it is on, as a table can have multiple partitions, and a database can have multiple partitioned tables.

Upvotes: 2

Christian Phillips
Christian Phillips

Reputation: 18769

partition_id is unique to the database, similar to a PK for a table. partition_number is the value within a partitioned table or index, since tables (if partitioned) would have more than 1 partition

If you look at this article, you'll see an example of how the dates in the table have different partition_numbers.

enter image description here

To see the partition_id in use, see this article, which shows a query against a partitioned table / index within a database.

enter image description here

from the following queries, also taken from the article:

SELECT *
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.HeapTest');

SELECT *
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.ClusteredIndexTest');

Upvotes: 1

Related Questions