title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.partitions (Transact-SQL) |
sys.partitions contains a row for each partition of all the tables and most types of indexes in the database. |
rwestMSFT |
randolphwest |
10/25/2023 |
sql |
system-objects |
reference |
|
|
|
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML aren't included in this view. All tables and indexes in [!INCLUDE ssNoVersion] contain at least one partition, whether or not they are explicitly partitioned.
Column name | Data type | Description |
---|---|---|
partition_id |
bigint | Indicates the partition ID. 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. 0 = heap 1 = clustered index 2 or greater = nonclustered index |
partition_number |
int | 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 (HoBT) that contains the rows for this partition. |
rows |
bigint | Indicates the approximate number of rows in this partition. |
filestream_filegroup_id |
smallint | Applies to: [!INCLUDE ssSQL11] and later versions. Indicates the ID of the FILESTREAM filegroup stored on this partition. |
data_compression |
tinyint | Indicates the state of compression for each partition: 0 = NONE 1 = ROW 2 = PAGE 3 = COLUMNSTORE 1 4 = COLUMNSTORE_ARCHIVE 2 Note: Full text indexes are compressed in any edition of [!INCLUDE ssNoVersion]. |
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 . |
xml_compression |
bit | Applies to: [!INCLUDE ssSQL22] and later versions. Indicates the state of XML compression for each partition: 0 = OFF 1 = ON |
xml_compression_desc |
varchar(3) | Applies to: [!INCLUDE ssSQL22] and later versions. Indicates the state of XML compression for each partition. Possible values are OFF and ON . |
1 Applies to: [!INCLUDE ssSQL11] and later versions.
2 Applies to: [!INCLUDE ssSQL14] and later versions.
Requires membership in the public role. For more information, see Metadata Visibility Configuration.
The following query returns all the object in a database, the amount of space used in each object, and partition information related to each object.
SELECT object_name(object_id) AS ObjectName,
total_pages / 128. AS SpaceUsed_MB,
p.partition_id,
p.object_id,
p.index_id,
p.partition_number,
p.rows,
p.data_compression_desc
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS au
ON p.partition_id = au.container_id
ORDER BY SpaceUsed_MB DESC;