What Is sys.partitions view in SQL Server?

sys.partitions is a system view in a SQL Server Database that contains information about each data partition used by a data object in the database.

Each record in the sys.partitions table represents a single data partition with the following key fields:

• partition_id - bigint: Indicates the partition ID. Is unique within a database.
• object_id - int: Indicates the ID of the object to which this partition belongs. Every table or view is composed of at least one partition.
• index_id - int: Indicates the ID of the index within the object to which this partition belongs.
• partition_number - int: 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.
• hobt_id - bigint: Indicates the ID of the data heap or B-tree that contains the rows for this partition. The hobt_id is also unique within a database and it is used as the container_id in the sys.allocation_units view to allocate a storage unit for this partition.
• rows - bigint: Indicates the approximate number of rows in this partition.
• filestream_filegroup_id - smallint: Indicates the ID of the FILESTREAM filegroup stored on this partition.
• data_compression - tinyint: Indicates the state of compression for each partition:
• data_compression_desc - nvarchar(60): Indicates the state of compression for each partition. Possible values for rowstore tables are NONE, ROW, and PAGE. Possible values for columnstore tables are COLUMNSTORE and COLUMNSTORE_ARCHIVE.

Here is a list of records from sys.partitions showing data partitions used in a SQL Server database:

SELECT * FROM sys.partitions

partition_id  object_id  index_id  partition_number  hobt_id  rows  ...
103872        1          1         1                 397632   8572  ...
300480        5          1         1                 463168   1195  ...
824768        7          1         1                 528704   1368  ...
890304        8          0         1                 594240   20    ...

⇒ What Is sys.allocation_units

⇐ What Is a SQL Server Partition

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials