How to create and manage SQL Server partitioned tables

Background knowledge:

Partitioned tables, you can save the data in a table to a different filegroup by scope.

Let's take an example:

Data saved to filegroup a before 2014

2014~2015 data is saved to filegroup b

Data after 2015 is saved to filegroup C

Benefits:

1. Improve IO performance---files in filegroups can be located on different disks, and multiple disks work together to improve performance.

2, convenient management---Backup to provide more choices for management, data archiving is convenient.

Pre-preparation: adding Filegroups and files

ALTER DATABASE Studio
Add filegroup FG1;
Go

ALTER DATABASE Studio
Add filegroup FG2;
Go

ALTER DATABASE Studio
Add filegroup FG3;
Go

ALTER DATABASE Studio
Add File (name = Fg1_file,filename= ' E:\DB\FG1_File.ndf ');
Go

ALTER DATABASE Studio
Add File (name = Fg2_file,filename= ' E:\DB\FG2_File.ndf ');
Go

ALTER DATABASE Studio
Add File (name = Fg3_file,filename= ' E:\DB\FG3_File.ndf ');
Go

Operation 1,

Create a partitioned table with the following steps

The first step,

To create a partition function:

It is used to divide a table logically into several different pieces such as: 0~2014,2014~2015,2015~ ....

create Partition Function Pf_for_mytable (datetime)
As Range left
For values (' 2014-01-01 ', ' 2015-01-01 ');
Go-divides the table logically into three parts. or three different logical partitions.

Step Two:

Create a partition scheme, which is essentially binding the logical partition of a table to a filegroup in the database. For example: The first partition is saved to FG1, the second partition is saved to FG2, and the third partition is saved to FG3

create PARTITION Scheme ps_for_mytable
As Partition pf_for_mytable
to (FG1,FG2,FG3);
Go

Step Three:

Build table

CREATE TABLE MyTable2 (DT datetime, DataValue nvarchar (30))
On ps_for_mytable (DT);

Operation 2,

View the partition where the specified data resides

$partition. Partition_function_name (value);

Select $partition. pf_for_mytable (' 2014-10-04 ');

Operation 3,

Add a new partition for the table.

First step: Modify the partitioning scheme

Specifies the filegroup to be used by the next partition.

Alter PARTITION scheme ps_for_mytable
Next used [primary];
Go

Step Two: Modify the partition function

Alter PARTITION function pf_for_mytable ()--see no this without adding datatime is wrong.
Split Range (' 2016-01-01 ');
Go

Operation 4,

removing partitions

The first step:

Alter PARTITION function pf_for_mytable ()
Merge range (' 2016-01-01 ');
Go

Operation 5,

Move a partition to a different table

Pre-preparatory work

CREATE TABLE Mytablehistory (DT datetime, DataValue nvarchar (30));
Go--Two tables are defined exactly the same, no partitioning is not related

The first step:

Move

ALTER TABLE MYTABLE2 switch partition 1

to Mytablehistory;
Go

Its basic syntax is as follows:

ALTER TABLE source_table_name switch [partition Partition_number]

to [schema.] Target_table_name [partition Partition_number]

Summarize:

How SQL Server partitioned tables are created and managed