(iv) SQL Server partition management

Partition (Split)

Add a new partition on an existing partition.

As shown, split partition 03 into 03 and 04 partitions, the Split method first locks all the data of the old 03 partition, and then migrates the old 03 partition related data to partition 04, and finally deletes the corresponding partition data on the old 03, which consumes Io, resulting in IO log read and write is four times times the transferred data. Therefore, in the management partition, the general advance to add the partition, for example, the current 03 partition without data, we completed the 04 partition Add.

To add a partition step:

1. Specify the filegroup for the next partition

2. Split partition

--Adding partitions Use [partiontest]ALTERPARTITION SCHEME[pc_partiontest01] NEXTUsed[PartionTest201412] Use [partiontest];ALTERPARTITIONFUNCTION [pf_partiontest01]() SPLIT RANGE ('2014-12-11')

Indicates that the partition was added successfully:

Ii. Merging Partitions (merge)

After the migration of partition data, partition merging is required, that is, deleting useless partition, partition merging is same as splitting, it will cause a lot of IO consumption, so this partition data will usually be sliced out, and the partition can be merged with many partitions.

Since the merge is merging two partitions, is there a need to merge the partition boundary points to the left or merge to the right? This depends on the range mode, and if range left is merged to the right, RANG-R will be merged to the Ieft.

Partition 4, which is derived from the split partition above, now merges partition 4 with the range right partitioning method, so partition 4 is merged to the left and merged into Partition 3. The following results can be used to demonstrate this view.

 Use [partiontest] ; ALTER FUNCTION [pf_partiontest01] () Merge RANGE ('2014-12-11')
As you can see, only partitions are left 3

Four, partition switching (switch PARTITION)

Switching partitions can quickly and efficiently manage subsets of data. You can use Alter TABLE ... SWITCH PARTITION. Statement. When a partition is moved, it simply modifies the relevant metadata and does not move the data, so the switching speed is very fast.

Switching partitions requires the following requirements: http://msdn.microsoft.com/zh-cn/library/ms191160 (v=sql.105). aspx

1, the original table and the target table structure must be the same, and must be in the same filegroup.

2. The target table or partition must be empty.

3. If the source table has a clustered partition index, the target table is required to have the same clustered partition index.

4. All indexes of the source table must be aligned with the source table partition.

5, if the target table exists index, constraint, foreign key, requires the source table must be the same structure as the target table index, constraints, foreign keys.

6. Replication distribution cannot exist for both the target table and the source table.

7. The constraints on whether the partitioning column can be empty must be the same.

The general switching partition can be processed as follows:

1. The
2. assigns an existing table as a partition to an existing partitioned table.

   alter [dbname]. [dbo] [tablename] switch to [dbname]. [dbo] [staging_tablename] partition source_partition_number_expression

3. The
4. switches a partition from one partitioned table to another partitioned table.

   alter [dbname]. [dbo] [tablename] switch partition source_partition_number_expression to [dbname]. [dbo] [staging_tablename] partition source_partition_number_expression

6. Switches a partition to an existing table.

   ALTER TABLE [dbname]. [dbo] . [TableName] SWITCH PARTITION source_partition_number_expression to [dbname]. [dbo] . [Staging_tablename]

   
   
   

Iv. $PARTITION

Returns the partition number for any specified partition function, and a set of partition column values is mapped to that partition number.

[] $PARTITION. partition_function_name (expression)

/*returns a value belonging to a partition*/SELECT$PARTITION. PF_PARTIONTEST01 ('2014-12-26 00:00:00.000')/*querying all values of a partition*/Select *  from [dbo].[PartionTest01]where$PARTITION. pf_partiontest01 (SalesDate)=2

(iv) SQL Server partition management