Shulou(Shulou.com)06/01 Report--

Today, the editor to share with you is to create, view the partition table of the Metadata detailed introduction, I believe that most people do not understand, in order to let you better understand, the editor summed up the following content, words do not say much, let's look down.

Unpartitioned tables can only be stored in one FileGroup; after partitioning the table, each partition is stored in a FileGroup. Table partitioning is a logically complete table that is divided into Partition set according to specific fields and distributed into (the same or different) FileGroup. Each Partition is stored independently in FileGroup, each parititon belongs to a unique table object, and each Partition has a unique ID.

When creating a table, use the On clause to specify the logical location of the table storage:

On filegroup | "default" indicates that the logical storage location is a single FileGroup

ON partition_scheme_name (partition_column_name) indicates that the logical storage location is multiple FileGroup, and the table is split into multiple partition according to partition_column_name, each partition is stored in a specified Filegroup.

CREATE TABLE [schema_name. ] Table_name () [ON {partition_scheme_name (partition_column_name) | filegroup | "default"}] [WITH ([,... n])] [;]

Partition means part of the logical storage space of table.

Corresponding to the logical storage space is the physical storage space, which is specified by File, FileGroup is a collection of File, and each File belongs to a unique FileGroup. Dividing the storage space of table into different FileGroup logically adds a layer of Partition to the storage management system of table, which is between Table and FileGroup. The data of Table is stored in Partition,Partition and stored in FileGroup, and FileGroup manages that File,File is the physical file that actually stores data.

Why does table add Parition? Because FileGroup is shared by all Table, Partition is exclusively owned by a table, and each Partition belongs to a unique table. In this way, you operate on one parititon without affecting the other parition of the table or the other table.

One: steps to create a partition table

Step1, create partition function

The function of the partition function is to provide the type of partition field and the boundary value of the partition

CREATE PARTITION FUNCTION [pf_int] (int) AS RANGE LEFT FOR VALUES (10,20)

Pf_int means to partition according to the int type, and the boundary value of the zone is 10, 20, left, indicating that the boundary value belongs to the left boundary. The two boundary values can be divided into three partitions, namely (- infinite,10], (10) (20), (20)).

Step2, create partition scheme

The role of the partition scheme is to allocate FileGroup,Partition Scheme and FileGroup to Parition logically equivalent, both of which are logical spaces for data storage, except that Partition Scheme specifies multiple FileGroup.

CREATE PARTITION SCHEME [ps_int] AS PARTITION [pf_int] TO ([PRIMARY], [db_fg1], [db_fg1])

Partitions are stored independently, whether in different FileGroup or in the same FileGroup.

Step3, creating partition tables

To create a partition table, you actually use the on clause to specify the logical location of the table storage.

Create table dbo.dt_test (ID int, code int) on [ps_int] (id)

Second, check the Metadata of Partition

1. Check the partition function

Select * from sys.partition_functions

View the boundary values defined by partition function

Select * from sys.partition_range_values

Look at the parmeter defined by partition function. This Parmeter is a data type,system_type_id that identifies the data type.

Select * from sys.partition_parameters

View data type according to system_type_id

Select * from sys.typeswhere system_type_id=56

2. View Partition scheme and filegroup

Select * from sys.partition_schemes

Data_space_ID is the data space ID, and each Parition Scheme has an ID.

Select * from sys.filegroups

Data_space_ID is the data space ID, and each FileGroup has an ID.

3. Check the Data Space

Select * from sys.data_spaces

Each filegroup has one row, and each partition scheme has one row. If the row refers to a partition scheme, data_space_id can be joined with sys.partition_schemes.data_space_id. If the row referes to a file, data_space_id can be joined with sys.filegroups.data_space_id.

Sys.data_spaces is the intersection of sys.filegroups and sys.partition_schemes results, which fully shows that both partition scheme and filegroup are logical spaces for data storage.

4the relationship between scheme and filegroup

A partition scheme can use multiple filegroup to store data, while a filegroup can be used by multiple partition scheme. The relationship between partition scheme and filegroup is that many-to-many,sql server uses sys.destination_data_spaces to provide the relationship between partition scheme and filegroup.

Select * from sys.destination_data_spaces

Partition_scheme_id is the data_space_id of sys.partition_schemes, identifying a Partition Scheme.

Data_space_id is the data_space_id of sys.filegroups, identifying the filegroup used by partition scheme.

Destination_id is Partition number. Partition Number is a number, starting with 1, that identifies the parition number of the table. The partition number of the table is numbered from left to right, and the leftmost partition has a partition number of 1.

5. View the information of the partition

Select * from sys.partitionswhere object_id=object_id ('dbo.dt_test')

Partition_id: each partition has an ID that uniquely identifies the partition.

Rows: the number of rows of data contained in the partition

The type of data compression used by data_compression and data_compression_desc:partition

6. View the statistics of the partition

Select * from sys.dm_db_partition_statswhere object_id=object_id ('dbo.dt_test')

Used_page_count

Bigint

Total number of pages used for the partition. Computed as in_row_used_page_count + lob_used_page_count + row_overflow_used_page_count.

Reserved_page_count

Bigint

Total number of pages reserved for the partition. Computed as in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count.

Row_count

Bigint

The approximate number of rows in the partition.

Sys.dm_db_partition_stats displays information about the space used to store and manage in-row data, LOB data, and row-overflow data for all partitions in a database. One row is displayed per partition.

The counts on which the output is based are cached in memory or stored on disk in various system tables.

In-row data, LOB data, and row-overflow data represent the three allocation units that make up a partition. The sys.allocation_units catalog view can be queried for metadata about each allocation unit in the database.

If a heap or index is not partitioned, it is made up of one partition (with partition number = 1), therefore, only one row is returned for that heap or index. Thesys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database.

The total count for an individual table or an index can be obtained by adding the counts for all relevant partitions.

The above is to create, view partition table Metadata method introduction, detailed use of their own use to know the specific essentials. If you want to know more about it, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.