Edit

Share via

sys.internal_partitions (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

Returns one row for each rowset that tracks internal data for columnstore indexes on disk-based tables. These rowsets are internal to columnstore indexes and track deleted rows, rowgroup mappings, and delta store rowgroups. They track data for each table partition. Every table has at least one partition. The Database Engine re-creates the rowsets each time it rebuilds the columnstore index.

Column name Data type Description
partition_id bigint Partition ID for this partition. This is unique within a database.
object_id int Object ID for the table that contains the partition.
index_id int Index ID for the columnstore index defined on the table.

1 = clustered columnstore index
2 = nonclustered columnstore index
partition_number int The partition number.

1 = first partition of a partitioned table, or the single partition of a nonpartitioned table.

2 = second partition, and so on.
internal_object_type tinyint Rowset objects that track internal data for the columnstore index.

2 = COLUMN_STORE_DELETE_BITMAP
3 = COLUMN_STORE_DELTA_STORE
4 = COLUMN_STORE_DELETE_BUFFER
5 = COLUMN_STORE_MAPPING_INDEX
internal_object_type_desc nvarchar(60) COLUMN_STORE_DELETE_BITMAP - This bitmap index tracks rows that are marked as deleted from the columnstore. The bitmap is for every rowgroup since partitions can have rows in multiple rowgroups. These rows are still physically present and take up space in the columnstore.

COLUMN_STORE_DELTA_STORE - Stores groups of rows, called rowgroups, that haven't been compressed into columnar storage. Each table partition can have zero or more deltastore rowgroups.

COLUMN_STORE_DELETE_BUFFER - For maintaining deletes to updateable nonclustered columnstore indexes. When a query deletes a row from the underlying rowstore table, the delete buffer tracks the deletion from the columnstore. When the number of deleted rows exceeds 1,048,576, they're merged back into the delete bitmap by the Tuple Mover background thread or by an ALTER INDEX ... REORGANIZE operation. At any given point in time, the union of the delete bitmap and the delete buffer represents all deleted rows.

COLUMN_STORE_MAPPING_INDEX - Used only when the clustered columnstore index has a secondary nonclustered index. This maps nonclustered index keys to the rowgroup and row ID in the columnstore. It only stores keys for rows that move to a different rowgroup. This occurs when a delta rowgroup is compressed into the columnstore, and when a merge operation merges rows from two different rowgroups.
row_group_id int ID for the deltastore rowgroup. Each table partition can have zero or more deltastore rowgroups.
hobt_id bigint ID of the internal rowset object (HoBT). Can be used in joins with other system views and functions such as sys.dm_db_index_physical_stats() to get more information about the physical characteristics of the internal rowset.
rows bigint Approximate number of rows in this partition.
data_compression tinyint The compression type for each partition:

0 = NONE
1 = ROW
2 = PAGE
data_compression_desc nvarchar(60) The compression type for each partition. Possible values for rowstore tables are NONE, ROW, and PAGE. Possible values for columnstore tables are COLUMNSTORE and COLUMNSTORE_ARCHIVE.

Permissions

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

Remarks

The Database Engine re-creates new columnstore internal indexes each time it creates or rebuilds a columnstore index.

Examples

A. View all of the internal rowsets for a table

This example returns all of the internal columnstore rowsets for a table. You can also use the hobt_id column to join with other system views and functions and find more information about the specific rowset.

SELECT i.object_id,
       i.index_id,
       i.name,
       p.hobt_id,
       p.internal_object_type_id,
       p.internal_object_type_desc
FROM sys.internal_partitions AS p
     INNER JOIN sys.indexes AS i
         ON i.object_id = p.object_id
WHERE p.object_id = OBJECT_ID('<table name>');

Related content