SQL Server Table Partitioning (1): Basic knowledge and implementation methods

1. What is Table Partitioning?
SQL Server uses three different types of files to store data:. MDF,. NDF, And. LDF. The main data is stored in
. MDF files, such as tables, indexes, and stored procedures .. The NDF file is also used to store the data .. LDF files are used to store operation logs.
The table is stored in the. MDF file by default. When updating a table, SQL Server locks the table. Other operations must wait until the update operation is complete.
If a table is large, query operations or update operations will have poor performance. If the tables are physically independent but logically continuous
Partitioning, SQL Server can greatly optimize the performance of query operations and update operations.

2. How to Implement Table Partitioning?
1) determine the partition policy, that is, the logical rule for dividing data. For example, put data whose integer value is less than 100 on a partition, And put data greater than or equal to 100 on the second partition.
Partition, and so on.
Use partition functions in SQL Server to implement partition policies:

Create Partition Function Pf_order ( Datetime )
As Range Right   For   Values
( ' 20090101 ' , ' 20100101 ' , ' 20110101 ' )

2) determine the partition Storage Architecture, that is, the physical storage rules for data division. The final partition can be stored in the primary file group, that is, the. MDF file, or in a non-primary file group,
That is, in the. NDF file.
Use the partition architecture in SQL Server to implement the partition Storage Architecture:

Create Partition Scheme ps_order
As Partition pf_order
To ( [ FG1 ] , [ FG2 ] , [ FG3 ] , [ Fg4 ] )

3) create a table in the partitioned Storage Architecture and specify the column used as the basis for data partitioning. Note that the column used as the basis for Data Division must be in the clustered index.
When updating data, SQL Server first uses column data as a parameter to call the Partition Function to determine which file group (logical partition) the data should be stored in ),
Then, write the data to the physical file corresponding to the file group.

Create a partition table in SQL Server:

Create   Table DBO. [ Order ]
(
Orderid Bigint   Identity ( 1 , 1 ),
Orderdate Datetime   Not   Null ,
Constraint Pk_order_orderid_orderdate Primary   Key   Clustered (Orderid, orderdate)
)
On Ps_order (orderdate)

 

The following is a detailed example.Code:

-- Table partition test code
Use   [ Master ]
Go

-- Create a test database with multiple file groups
If   Exists ( Select   1   From SYS. Databases Where   [ Name ] = N ' Testtablepartitiondb ' )
Begin
Drop   Database   [ Testtablepartitiondb ]
End

Create   Database   [ Testtablepartitiondb ]
On   Primary
(
Name = ' Primaryfg ' ,
Filename = ' D: \ test \ tablepartition \ testtablepartitiondb. MDF ' ,
Size = 5 ,
Filegrowth = 1
),
Filegroup [ FG1 ]
(
Name = ' FG1 ' ,
Filename = ' D: \ test \ tablepartition \ fg1.ndf ' ,
Size = 5 ,
Filegrowth = 1
),
Filegroup [ FG2 ]
(
Name = ' FG2 ' ,
Filename = ' D: \ test \ tablepartition \ fg2.ndf ' ,
Size = 5 ,
Filegrowth = 1
),
Filegroup [ FG3 ]
(
Name = ' FG3 ' ,
Filename = ' D: \ test \ tablepartition \ fg3.ndf ' ,
Size = 5 ,
Filegrowth = 1
),
Filegroup [ Fg4 ]
(
Name = ' Fg4 ' ,
Filename = ' D: \ test \ tablepartition \ fg4.ndf ' ,
Size = 5 ,
Filegrowth = 1
)

UseTesttablepartitiondb
Go

-- Create a partition function
Create Partition Function Pf_order ( Datetime )
As Range Left   For   Values
( ' 20090101 ' , ' 20100101 ' , ' 20110101 ' )

-- Create a partition Architecture
Create Partition Scheme ps_order
As Partition pf_order
To ( [ FG1 ] , [ FG2 ] , [ FG3 ] , [ Fg4 ] )

-- Create a partition table
Create   Table DBO. [ Order ]
(
Orderid Bigint   Identity ( 1 , 1 ),
Orderdate Datetime   Not   Null ,
Constraint Pk_order_orderid_orderdate Primary   Key   Clustered (Orderid, orderdate)
)
On Ps_order (orderdate)

--Which is the logical partition of the test data?
Select$ Partition. pf_order ('20090101')

-- test the partition status of a partitioned table
select partition_number, rows
from sys. partitions
where [ object_id ] = object_id (n ' DBO. [order] ' )

-- drop index mytable_ixc on mytable with (move to [Data Partition Scheme] (ID)