Create a partition

[IT expert network exclusive] Are you doing everything you can to optimize the performance of the SQL Server database? If your database contains a large number of tables, putting these table partitions into an independent file group may benefit you a lot. The Table Partitioning technology introduced by SQL Server 2005 allows you to distribute data to different physical disks to improve the parallel processing performance of these disks to Optimize Query performance.

The SQL Server database table partition operation consists of three steps:

1. Create a partition function

2. Create a partition Architecture

3. Partition the table

The following describes each step in detail.

　　Step 1: Create a partition function

This partition function is used to define the parameter value ([u] How [/u]) of how you want SQL Server to partition data. This operation does not involve any tables, but simply defines a technology to split data.

We can define partitions by specifying the boundary conditions for each partition. For example, assume that we have a mers MERs table that contains information about all customers, which is distinguished by customer numbers (from 1 to 1,000,000. The following partition function is used to divide the table into four partitions of the same size:

 

Create Partition Function customer_partfunc (INT) As range right For values (250000,500 000, 750000)

These boundary values define four partitions. The first partition contains all data whose values are less than 250,000, and the second partition contains data between 250,000 and 49,999. The third partition contains data between 500,000 and 7499,999. All data with a value greater than or equal to 750,000 is classified into the fourth partition.

Note that the "range right" statement called here indicates that the boundary value of each partition is the right boundary. Similarly, if the "range left" statement is used, the first partition should include all data whose values are less than or equal to 250,000, the data value of the second partition is between 250,001 and 500,000, and so on.

　　Step 2: Create a partition Architecture

Once a partition function is provided to describe how to split data, a partition architecture is created to define the partition location ([u] Where [/u]). The creation process is straightforward. You just need to connect the partition to the specified file group. For example, if there are four file groups named from "FG1" to "fg4", the following Partition architecture can achieve the desired effect:

 

Create Partition Scheme customer_partscheme As partition customer_partfunc To (FG1, FG2, FG3, fg4)

Note that a partition function is connected to the partition architecture, but the partition architecture is not connected to any data table. This is where reusability works. No matter how many database tables there are, we can use this partition architecture (or just partition functions ).

　　Step 3: partition a table

After defining a partition architecture, you can create a partition table. This is the simplest step in the entire partition operation process. You only need to add an "on" Statement to the table creation command to specify the partition architecture and the table columns that apply the schema. Because the partition architecture has recognized the partition function, you do not need to specify the partition function.

For example, to create a customer table using the preceding partition architecture, you can call the following statements:

 

Create Table MERs (firstname nvarchar (40), lastname nvarchar (40), customernumber INT) On customer_partscheme (customernumber)

The Table Partitioning function of SQL Server is sufficient if you know the above knowledge. Remember! Writing General partition functions and partition architectures that can be used for multiple tables can greatly improve reusability.

======================================================= Second times ======================================================== ==============

-- Create a partition table consists of three steps: Create a partition function, create a partition scheme, and create a partition table.

 

/* This experiment involves two tables: transactionhistory and transactionhistoryarchive. Data is imported from adventureworks,
Create the two tables as partition tables */
-- Create a partition table transactionhistory
-- Create a partition function
Use WJZ
Go
Create Partition Function transactionhistorypf1 (datetime)
As range right for values (
'2014-9-1 ', '2014-10-1', '2014-11-1 ', '2014-12-1', '2014-1-1 ',
'2017-2-1 ', '2017-3-1', '2017-4-1 ', '2017-5-1', '2017-6-1 ',
'2017-7-1 ', '2017-8-1'
);
Go

-- Create a partition scheme
Create Partition Scheme transactionhistoryps1
As partition transactionhistorypf1
([Primary], wjz2, wjz3, wjz4, wjz5, wjz6, wjz7, wjz8, Jun, wjz10, wjz11, wjz12, wjz13, wjz14)
Go

-- Create a partition table
Create Table [transactionhistory] (
[Transactionid] [int] Identity (100000,1) not null,
[Productid] [int] not null,
[Referenceorderid] [int] not null,
[Referenceorderlineid] [int] not null constraint [df_transactionhistory_referenceorderlineid] default (0 )),
[Transactiondate] [datetime] not null constraint [df_transactionhistory_transactiondate] default (getdate ()),
[Transactiontype] [nchar] (1) Collate SQL _latin1_general_cp1_ci_as not null,
[Quantity] [int] not null,
[Actualcost] [money] not null,
[Modifieddate] [datetime] not null constraint [df_transactionhistory_modifieddate] default (getdate ()),

) On transactionhistoryps1 (transactiondate );
Go

-- Create a partition table transactionhistoryarchive
-- Create a partition function
Create Partition Function transactionhistoryarchivepf1 (datetime)
As range right for values ('2017-9-1 ', '2017-10-1 ')
Go

-- Create a partition scheme
Create Partition Scheme transactionhistoryarchiveps1
As partition transactionhistoryarchivepf1 (
[Primary], wjz2, wjz3
);
Go

-- Create a partition table
Create Table [transactionhistoryarchive] (
[Transactionid] [int] not null,
[Productid] [int] not null,
[Referenceorderid] [int] not null,
[Referenceorderlineid] [int] not null constraint [df_transactionhistoryarchive_referenceorderlineid] default (0 )),
[Transactiondate] [datetime] not null constraint [df_transactionhistoryarchive_transactiondate] default (getdate ()),
[Transactiontype] [nchar] (1) Collate SQL _latin1_general_cp1_ci_as not null,
[Quantity] [int] not null,
[Actualcost] [money] not null,
[Modifieddate] [datetime] not null constraint [df_transactionhistoryarchive_modifieddate] default (getdate ()),

) On transactionhistoryarchiveps1 (transactiondate );
Go

-- Load data use SSIS

-- Partition Table created

-- Manage partition tables
-- Move the 2nd partitions of transactionhistory to the 2nd partitions of transactionhistoryarchive.
-- Note that the 2nd partitions of the two tables are located in the same file group wjz2.
Alter table transactionhistory
Switch partition 2
Transactionhistoryarchive
Partition 2
Go
-- Verify that the data has been transferred to the new table within one second.
Select * From transactionhistoryarchive

-- Adds a partition to the right of the transactionhistory table.
-- Note: Because I wrote one more file group when creating the partition scheme, the redundant file group becomes next used,
-- Otherwise, you must modify the partition scheme to add a new available File Group.
Alter Partition Function transactionhistorypf1 ()
Split range ('2014-9-1 ')

-- Combine the two empty zones on the left of the transactionhistory table into one
Alter Partition Function transactionhistorypf1 ()
Merge range ('2014-9-1 ')
Go

-- Add a partition to the right of the transactionhistoryarchive table and prepare for the next transfer. Note that you must modify the partition scheme first.
Alter Partition Scheme transactionhistoryarchiveps1
Next used wjz4
Go
Alter Partition Function transactionhistoryarchivepf1 ()
Split range ('2017-11-1 ')
Go

-- Merge the two partitions on the left of the transactionhistoryarchive table (this is optional, at least good or bad, but I haven't thought about it yet)
Alter Partition Function transactionhistoryarchivepf1 ()
Merge range ('2014-9-1 ')

/* Deleted
Drop table transactionhistoryarchive
Go
Drop Partition Scheme transactionhistoryarchiveps1
Go
Drop Partition Function transactionhistoryarchivepf1
Go

Drop table transactionhistory
Go
Drop Partition Scheme transactionhistoryps1
Go
Drop Partition Function transactionhistorypf1
Go */

-- In the above cases, you can write a job for Loop