[Communication] Sql Server Summary of Partitioned Tables

Official documentation

High-level steps to create a partition table

1. Create a file group (similar to oracle tablespace), of course, you can not create it, you can put all partitions in one file group

2. Establish a partition function and distribute data according to what range

3. Establish a partition scheme, associate the partition function, and also associate the file group, the partition function divides the data into several ranges, you need to associate several file groups, of course, you can also put these partition ranges into the same file group

4. Create a table and associate the zoning scheme

A bug encountered

When you directly right-click the table to export the table structure, you cannot export the partition information, and you can only export the partition information of the table by right-clicking the database-task-generate script

Some conclusions from the partition table:

1. Partitioned fields do not necessarily need to be indexed

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

3. Partitioned fields, whether they are clustered indexes or noclustered indexes, are rebuilt into clustered indexes without associated partitioning schemes, and the partitioned table becomes a non-partitioned table

4. Convert an ordinary table to a partitioned table, just create a clustered index in 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 is associated with a partitioning scheme, the table is converted to a partitioned table, and the partitioned field is field 1.

5. Convert the partitioned table to a normal table, if there is a partition field with an index, rebuild the partitioned field as a clustered index and not associate with the partition scheme, if the partition field does not have an index, the partition field can create a new clustered index and not associate with the partition scheme

6. Changing a normal table to a partition table or changing a partition table to an ordinary table can only be achieved by using clustered indexes, because with clustered indexes is index organization tables, and the redistribution of tables is realized through the reconstruction of clustered indexes. If the ordinary table becomes a partitioned table, reconstruct the partition field into a clustered index and associate it with the partition scheme, and if the partition table becomes a normal table, reconstruct the partition field into a clustered index without associating the partition scheme.

7. To create a partition table, the uniqueness constraint must include a partition column

8. When creating a partition scheme, it is necessary to ensure that the number of file groups matches the partition range segment of the partition function, and it does not matter if the file group name is duplicated.

9. Partitioning functions and partitioning schemes are in a database, not facing the whole instance

10. The partition table is too large to take up a lot of disk space, and the size still does not change after deleting some fields

Steps to create a partition table

1.1. Example of establishing a file group

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. Establish an example of a file and associate a file group

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

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

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

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

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

2. An example of establishing a partition function, the partition function is called partfun1

CREATE PARTITION FUNCTION partfun1 (int)

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

--VALUES ('1000', '2000', '3000', '4000') indicates that the table will be divided into 5 zones, which are divided according to the size of the values in the table fields, the five zones are the smallest - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-largest

3. An example of establishing a scheme, associate the partition function partfun1, and associate the file group

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

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

--Built on several file groups of part1, part1000, part2000, part3000, part4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

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

--Built on part1, [PRIMARY] file group, it is no problem to replace part1 with [PRIMARY], so that it is similar to building on the [PRIMARY] file group

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (part1);

--all built on the part1 filegroup

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARY]);

--all built on the [PRIMARY] filegroup

4. 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,'1',getdate()-4);

insert into parttable1 values (1001,'1001',getdate()-3);

insert into parttable1 values (2001,'2001',getdate()-2);

insert into parttable1 values (3001,'3001',getdate()-1);

insert into parttable1 values (4001,'4001',getdate());

5. Verify the data of the partition table

SELECT * FROM parttable1;

--Returns all rows of the partition table

SELECT distinct $PARTITION. [partfun1] (4) FROM parttable1;

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

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

--Returns all rows in the 2nd partition, the ID is the partition field ID

Note: You can't use SELECT * FROM parttable1 where $PARTITION. [partfun1] (ID)=2 has a result, it means that it is a partitioned table, and the last test 7 of this paper is a non-partitioned table, but SELECT * FROM parttable1 where $PARTITION. [partfun1] (ID)=2 still has a result

New partitions

1. Specify a file group that can be used for the partition scheme (add a file group for the 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.partition_number,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, if the partition scheme uses ALL TO ([PRIMARY]), this statement does not need to be executed

--A new partition is added to the second statement, the range is 4000-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 of 1000-2000 was deleted and merged into 1000-3000

--Unable to execute ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME like oracle;

Delete the partition table and the corresponding file group

The order of deletion is: delete the partition table, delete the partition scheme, delete the partition function, and finally delete the file group, and the corresponding file is deleted after the file group is deleted

Example of converting a partitioned table to a normal table and a normal table to a partitioned table

DROP TABLE parttable1;

CREATE TABLE parttable1(

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

[Name] [varchar] (16) NOT NULL,

[Id2] [int] NOT NULL

) ON partschema1(Id2);

insert into parttable1 values ('1',1);

insert into parttable1 values ('1001',1001);

insert into parttable1 values ('2001',2001);

insert into parttable1 values ('3001',3001);

insert into parttable1 values ('4001',4001);

1. The unique constraint created on the partition table must contain a partition column.

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. Create a new clustered index in the partition column id2, and parttable1 is still a partitioned table

create clustered index CI_prattable1_id2 on parttable1(id2);

3. Partition column id2 creates a nonclustered index, and parttable1 is still a partitioned table

drop index CI_prattable1_id2 on parttable1;

create nonclustered index NCI_prattable1_id2 on parttable1(id2);

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

create clustered index CI_prattable1_id on parttable1(id);

5. Partition column ID2 is rebuilt into a nonclustered index and does not use a partitioning scheme, parttable1 is still a partition table

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

6. The partition column id2 is reconstructed into a clustered index without the ON condition, and parttable1 is still a 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. Partitioned column id2 is rebuilt into a clustered index with ON condition but without using partitioning scheme, and parttable1 becomes a non-partitioned table

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

8. Partition column id2 is rebuilt into a clustered index and using the partitioning scheme, parttable1 becomes a partitioned table

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

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

drop index CI_prattable1_id2 on parttable1;

10. Partition column ID2 is created as a clustered index and does not use the partitioning scheme, and 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. The 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);

If the partitioned table is converted to a normal table, the primary key constraint is deleted, and the cluster index is rebuilt or rebuilt into the primary key for the fields of the original primary key, but it is not related to the partitioning scheme

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 the normal table is converted to a partitioned table, if the original primary key is to be retained, the primary key constraint is deleted, and then the primary key is created without setting it as an aggregated index, and then a new aggregated index is created, and the partitioning scheme is used in the aggregated 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 not set as a clustered index

CREATE CLUSTERED INDEX index_name ON Table_name(column) ON Partition Scheme (Partition Field)

--Create a new clustered index in which a partitioning scheme is used

Query the total number of rows and size of a partitioned table, for example, the table is CRM. EmailLog

exec sp_spaceused 'crm. EmailLog';

Query the information of a partitioned table, how many rows each partition has, 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, ''), 120) 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 functions

select * from sys.partition_functions

View the partition architecture

select * from sys.partition_schemes