Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the relevant knowledge points of SqlServer about the partition table". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the relevant knowledge points of SqlServer about the partition table"?

General steps for creating a partition table

1. Set up a filegroup (a tablespace similar to oracle), of course, or not, or put all partitions in a filegroup.

2. Establish a partition function, and what range does the data distribute according to?

3. Set up the partition scheme, associate the partition function, and also associate the filegroup. If the partition function divides the data into several ranges, you need to associate several filegroups. Of course, you can also put these partition ranges into the same filegroup.

4. Set up the table and associate the partition scheme

A Bug I met

When you directly right-click the table to export the table structure, you can not export the partition information. You can only right-click the database-- Task-- generate script to export the partition information of the table.

Some conclusions of the partition table:

1. Partition fields do not necessarily need to be indexed

2. Partition fields can be created as clustered indexes or noclustered indexes.

3. The partitioned table becomes a non-partitioned table when the partitioned field, whether it is a clustered index or an noclustered index, is rebuilt to a clustered index and there is no associated partitioning scheme.

4. Convert a regular table to a partitioned table, as long as you create a clustered index on the table and use the partitioning scheme on the clustered index. For example, when field 1 of a non-partitioned table creates a clustered index and associates a partitioning scheme, the table is converted to a partitioned table, and the partitioned field is field 1.

5. Convert a partitioned table to a regular table. If a partitioned field has an index, you can rebuild the partitioned field to clustered index without associated partitioning scheme. If the partitioned field does not have an index, create a new clustered index for the partitioned field and do not associate with the partitioning scheme.

6. Changing the ordinary table to the partitioned table or the partitioned table to the ordinary table can only be realized by using the clustered index, because with the clustered index, the index organizes the table, and the table is redistributed through the reconstruction of the clustered index. The ordinary table becomes the partition table, the partition field is rebuilt to the clustered index and associated with the partition scheme, the partition table becomes the ordinary table, and the partition field is rebuilt to the clustered index without the associated partition scheme.

7. Create a uniqueness constraint for partition tables, which must contain partition columns

8. When creating a partition scheme, you must make sure that the number of filegroups matches the partition range segment of the partition function. It does not matter if the filegroup name is repeated. Of course, you can also use ALL to specify a filegroup name, so that the partition range segment data of all partition functions fall into this filegroup.

9. The partition function and partition scheme are in each database, rather than facing the whole instance

10. The partition table is too large to take up a lot of disk space, and the size remains unchanged after some fields in delete. If you merge the partition or convert the partition table to a normal table, the size will be reduced.

To create a partition tabl

1.1. Example of establishing a filegroup

Alter database test1 add filegroup part1

Alter database test1 add filegroup part1000

Alter database test1 add filegroup part2000

Alter database test1 add filegroup part3000

Alter database test1 add filegroup part4000

1.2. Create an example of a file and associate it with a filegroup

ALTER DATABASE test1 ADD FILE (NAME = test1part1,FILENAME ='G:\ test1part1.ndf',SIZE = 5MB maxize = 100MB FILEGROWTH = 5MB) TO FILEGROUP part1

ALTER DATABASE test1 ADD FILE (NAME = test1part1000,FILENAME ='G:\ test1part1000.ndf',SIZE = 5MB maxize = 100MB FILEGROWTH = 5MB) TO FILEGROUP part1000

ALTER DATABASE test1 ADD FILE (NAME = test1part2000,FILENAME ='G:\ test1part2000.ndf',SIZE = 5MB maxize = 100MB FILEGROWTH = 5MB) TO FILEGROUP part2000

ALTER DATABASE test1 ADD FILE (NAME = test1part3000,FILENAME ='G:\ test1part3000.ndf',SIZE = 5MB maxize = 100MB FILEGROWTH = 5MB) TO FILEGROUP part3000

ALTER DATABASE test1 ADD FILE (NAME = test1part4000,FILENAME ='G:\ test1part4000.ndf',SIZE = 5MB maxize = 100MB FILEGROWTH = 5MB) TO FILEGROUP part4000

2. An example of creating a partition function, whose name is partfun1

CREATE PARTITION FUNCTION partfun1 (int)

AS RANGE LEFT FOR VALUES ("1000", "2000", "3000", "4000")

-- VALUES indicates that the table will be divided into five regions according to the value of the table field, each of which is the smallest-- 1000-2000-2000-3000-4000-- the largest

3. Set up an example of a solution, associate the partition function partfun1, and associate the filegroup

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (part1,part1000,part2000,part3000,part4000)

-- built on several filegroups of part1,part1000,part2000,part3000,part4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

TO (part1, [PRIMARY], [PRIMARY])

-- based on part1, [PRIMARY] filegroup, it's okay to change part1 to [PRIMARY], so it's similar to building on [PRIMARY] filegroup.

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (part1)

-- all based on part1 filegroups

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARY])

-- all based on [PRIMARY] filegroup

4. An example of establishing a partition table

CREATE TABLE parttable1 (

[ID] [int] NOT NULL

[IDText] [nvarchar] (max) NULL

[Date] [datetime] NULL)

ON [partschema1] (ID)

Insert into parttable1 values (1-4)

Insert into parttable1 values (1001 Getdate ()-3)

Insert into parttable1 values (2001 ~ (1))

Insert into parttable1 values (3001)

Insert into parttable1 values (4001 Getdate ())

5. Verify the data of the partition table

SELECT * FROM parttable1

-returns all rows of the partitioned table

SELECT distinct $Partition N. [partfun1] (4) FROM parttable1

-- returns the partition to which the row with an ID field value of 4 belongs

SELECT * FROM parttable1 where $PARTITION.[ partfun1] (ID) = 2

-- returns all rows of the second partition. ID is the partition field ID.

Note: just because SELECT * FROM parttable1 where $Partition N. [partfun1] (ID) = 2 does not mean that it is a partitioned table. At the end of this paper, it is tested that this table is a non-partitioned table, but the execution of SELECT * FROM parttable1 where $Partition N. [partfun1] (ID) = 2 still has a result.

New partition

1. Specify a filegroup that can be used for the partition scheme (the filegroup for the new partition scheme).

2. Modify the partition function (add the data range of the partition function)

ALTER PARTITION SCHEME partschema1 NEXT USED [PRIMARY]

ALTER PARTITION FUNCTION partfun1 () SPLIT RANGE ('4500')

Select p.partitionalist numbering p.rows from sys.indexes i inner join sys.partitions p on p.object_id = i.object_id and i.object_id = object_id ('parttable2') order by 1

The first statement, which does not need to be executed if the partition scheme uses ALL TO ([PRIMARY])

-- add a partition to the second statement, which ranges from 4000 to 4500

The third statement verifies whether the new partition exists and whether there are rows.

Delete\ merge Partition

ALTER PARTITION FUNCTION partfun1 () MERGE RANGE ('2000')

The partition 1000-2000 was deleted and merged into 1000-3000.

-- cannot execute ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME like oracle

Delete partition table and corresponding filegroup

The order of deletion is: delete partition table, delete partition scheme, delete partition function, and finally delete filegroup. After deleting filegroup, the corresponding files are deleted.

An example of converting a partition table to a normal table and a normal table to a partition table

DROP TABLE parttable1

CREATE TABLE parttable1 (

[Id] [int] IDENTITY (1) NOT NULL

[Name] [varchar] (16) NOT NULL

[Id2] [int] NOT NULL

) ON partschema1 (Id2)

Insert into parttable1 values ('1century 1)

Insert into parttable1 values ('1001)

Insert into parttable1 values ('2001 Jun 2001)

Insert into parttable1 values ('3001, 3001)

Insert into parttable1 values ('400114001)

1. Unique constraints created on the partition table must contain partition columns.

ALTER TABLE parttable1 ADD CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC)

Error Column 'Id2' is partitioning column of the index' PK_prattable1_id'. Partition columns for a unique index must be a subset of the index key.

2. Partition column id2 new clustered index, parttable1 or partition table

Create clustered index CI_prattable1_id2 on parttable1 (id2)

3. Partition column id2 creates nonclustered index, parttable1 or partition table

Drop index CI_prattable1_id2 on parttable1

Create nonclustered index NCI_prattable1_id2 on parttable1 (id2)

4. Non-partitioned column id column creates clustered index, parttable1 or partitioned table, indicating that non-partitioned column can be cluster index column

Create clustered index CI_prattable1_id on parttable1 (id)

5. Partition column id2 is rebuilt to nonclustered index and no partition scheme is used, parttable1 or partition table

Create nonclustered index NCI_prattable1_id2 on parttable1 (id2) WITH (DROP_EXISTING = ON) ON [PRIMARY]

6. Partition column id2 is rebuilt to clustered index without ON condition, and parttable1 is still partition table.

Drop index CI_prattable1_id on parttable1

Drop index NCI_prattable1_id2 on parttable1

Create clustered index CI_prattable1_id2 on parttable1 (id2)

Create clustered index CI_prattable1_id2 on parttable1 (id2) WITH (DROP_EXISTING = ON)

7. The partitioned column id2 is rebuilt to a clustered index with ON conditions but no partitioning scheme is used, and parttable1 becomes a non-partitioned table

Create clustered index CI_prattable1_id2 on parttable1 (id2) WITH (DROP_EXISTING = ON) ON [PRIMARY]

8. The partition column id2 is rebuilt to a clustered index and using the partition scheme, parttable1 becomes a partition table

Create clustered index CI_prattable1_id2 on parttable1 (id2) WITH (DROP_EXISTING = ON) on partschema1 (Id2)

9. After deleting the clustered index of the above 8, parttable1 is still a partitioned table

Drop index CI_prattable1_id2 on parttable1

10. The partitioned column id2 is newly created as a clustered index without using the partitioning scheme, and the parttable1 becomes a non-partitioned table.

Create clustered index CI_prattable1_id2 on parttable1 (id2) ON [PRIMARY]

11. After deleting the clustered index of the above 10, parttable1 is still a non-partitioned table

Drop index CI_prattable1_id2 on parttable1

12. Partition column id2 is newly created as a nonclustered index. Although the partitioning scheme is used, it is still a non-partitioned table.

Create nonclustered index NCI_prattable1_id2 on parttable1 (id2) on partschema1 (Id2)

When a partition table is converted to a regular table, if the partition field is a primary key, the primary key constraint is deleted, and then the cluster index or primary key is rebuilt on the fields of the original primary key, but the partitioning scheme is not associated.

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>

CREATE CLUSTERED INDEX PK_NAME ON Table_name (column) WITH (ON [PRIMARY]

Or

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY CLUSTERED (column) WITH (ON [PRIMARY]

If an ordinary table is converted to a partitioned table, if you want to retain the original primary key, delete the primary key constraint, create the primary key without setting it as a clustered index, create a new clustered index, and use the partitioning scheme in the clustered index.

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED (column) WITH (ON [PRIMARY]

Create a primary key, but do not set it as a clustered index

CREATE CLUSTERED INDEX index_name ON Table_name (column) ON partition scheme (partition field)

Create a new clustered index and use the partitioning scheme in the clustered index

Query the total number of rows and size of a partitioned table, such as crm.EmailLog

Exec sp_spaceused 'crm.EmailLog'

Query the information of a partition table and how many rows are there in each partition, for example, the table is crm.EmailLog

Select convert (varchar (50), ps.name

) as partition_scheme

P.partition_number

Convert (varchar (10), ds2.name

) as filegroup

Convert (varchar (19), isnull (v.value,'') as range_boundary

Str (p.rows, 9) as rows

From sys.indexes i

Join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

Join sys.destination_data_spaces dds

On ps.data_space_id = dds.partition_scheme_id

Join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id

Join sys.partitions p on dds.destination_id = p.partition_number

And p.object_id = i.object_id and p.index_id = i.index_id

Join sys.partition_functions pf on ps.function_id = pf.function_id

LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id

And v.boundary_id = p.partition_number-pf.boundary_value_on_right

WHERE i.object_id = object_id ('crm.EmailLog')

And i.index_id in (0,1)

Order by p.partition_number

Query partition function

Select * from sys.partition_functions

View the partition schema

Select * from sys.partition_schemes

Thank you for your reading, the above is the content of "what are the relevant knowledge points of SqlServer about the partition table". After the study of this article, I believe you have a deeper understanding of what are the relevant knowledge points of SqlServer about the partition table, and the specific use still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.