Export (0) Print
Expand All
Expand Minimize

sys.partitions (Transact-SQL)

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 are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type

Description

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.

0 = heap

1 = clustered index

2 or greater = nonclustered index

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.

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.

Applies to: SQL Server 2012 through SQL Server 2014.

data_compression

tinyint

Indicates the state of compression for each partition:

0 = NONE

1 = ROW

2 = PAGE

3 = COLUMNSTORE

Applies to: SQL Server 2012 through SQL Server 2014.

4 = COLUMNSTORE_ARCHIVE

Applies to: SQL Server 2014 through SQL Server 2014.

Note Note

Full text indexes will be compressed in any edition of SQL Server.

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.

Requires membership in the public role. For more information, see Metadata Visibility Configuration.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft