In layman SQL Server 2008 partition functions and partition tables

Original: SQL Server 2008 partition function and partition table

When we have a larger amount of data, we need to split the large table into smaller tables, then queries that only access departmental data can run faster, the basic principle being that the data to be scanned becomes smaller. maintenance tasks (for example, rebuilding an index or backing up a table) can also run faster.

We can no longer get the partition by physically placing the table on multiple disk drives to split the table. If you place a table on a physical drive and you place the related table on another drive, you can improve query performance because multiple disk headers read data simultaneously when you run a query that involves inter-table connections. You can use the SQL Server filegroup to specify the disk where the table is placed.

For partitioning, there are three basic ways: hardware partitioning, horizontal partitioning, and vertical partitioning. Related scenarios can refer to SQL Books Online

Here we introduce the specific combat method of partition table:

The first step is to build the database we want to use, and most importantly, to set up multiple filegroups.

Let's start with a new set of four directories to form a filegroup, a directory to hold the main file: Primary

Three data Files directory: FG1, FG2, FG3

To build a library:

CREATE Database Sales on primary (name=n'Sales', filename=n'G:\data\Primary\Sales.mdf', size=3MB, MaxSize=100MB, FileGrowth=Ten%), filegroup FG1 (NAME= N'File1', FILENAME= N'G:\DATA\FG1\FILE1.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth=Ten%), FILEGROUP FG2 (NAME= N'File2', FILENAME= N'G:\DATA\FG2\FILE2.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth=Ten%), FILEGROUP FG3 (NAME= N'File3', FILENAME= N'G:\DATA\FG3\FILE3.NDF', SIZE=1MB, MAXSIZE=100MB, FileGrowth=Ten%) LOG on (NAME= N'Sales_log', FILENAME= N'G:\data\Primary\Sales_Log.ldf', SIZE=1MB, MAXSIZE=100MB, FileGrowth=Ten%) GO

The second step: to establish a partition function, the purpose is to standardize the different data stored in different directories of the standard, simply say how to partition.

Use Sales   Gocreate PARTITION FUNCTION pf_orderdate (datetime) as RANGE right for   VALUES ('2003/01/01  '2004/01/01') GO

We created a partition function for datetime data type, divided by time period
Filegroup Partition Value Range
FG1 1 (past year, 2003/01/01)
FG2 2 [2003/01/01, 2004/01/01]
FG3 3 [2004/01/01, next year]

Step three: Create a partition scheme that is associated to the partition function. The goal is to organize the partitioned functions that have been built into a set of scenarios, and the simple points are where we partition the data.

Use salesgocreate  partition  scheme ps_orderdate  as partition  Pf_orderdateto (FG2, FG2,FG3) Go

It is simple to apply the partition function established in the second step to the established partition group.
Fourth step: Create a partitioned table. Creates a table and binds it to a partition scheme. We first set up two tables, one original table and another to archive the data and save the archived data.

Use salesgocreate table Orders (OrderIDintIdentity10000,1), OrderDate datetime notNULL, CustomerIDintNotNULL, constraint Pk_orders primary key (Orderid,orderdate)) on Ps_orderdate (OrderDate) gocreate table Ordershistory (Ord EridintIdentity10000,1), OrderDate datetime notNULL, CustomerIDintNotNULL, constraint Pk_ordershistory primary key (Orderid,orderdate)) on ps_orderdate (OrderDate) Go

Here, through the four steps above, we have completed a library with partitioned tables, and we are going to insert some data to test the suitability of our establishment.

First, since the January 1, 2003 is used as a differentiator, we first write the 2002 canonical data to the data table

Use the Sales GO INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2002/6/25', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2002/8/13', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2002/8/25', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2002/9/23', +) GO

Likewise we write 2003 four data

Use the Sales goinsert into dbo. Orders (OrderDate, CustomerID) VALUES ('2003/6/25', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2003/8/13', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2003/8/25', +INSERT into dbo. Orders (OrderDate, CustomerID) VALUES ('2003/9/23', +) GO

Let's see if this data is fully entered:

Because we have not archived data for the Ordershistory table, we are empty.

Let's do this by sub-conditional query:

1. Query a partition

Here we are going to use the $partition function. This function is explained in Books Online:

usage: Returns the partition number for any specified partition function, and a set of partition column values will be mapped to that partition number. Syntax: [database_name.] $PARTITION. Partition_function_name (expression) parameter: database_name The name of the database that contains the partition function. Partition_function_name the name of any existing partition function on which to apply a set of partitioned column values. expression whose data type must match or be implicitly convertible to its corresponding partition column data type. Expression can also be the name of the partition column that is currently participating in the partition_function_name. return type:int1int  value. The $PARTITION will return the partition number for any valid value, regardless of whether the value currently exists in the partitioned table or index using the partition function. 

Let's query the first partition of the partition table order, with the following code:

We can see that the data we query is all 2002 years, that is, in the first partition we have deposited data is less than 2003, according to this inference 2003 years of data, it should exist in the second partition:

As we expected, we can group by this partition to see how many rows of data are in each partition, the code is as follows:

Select  as  from dbo. Ordersgroup by $partition. Pf_orderdate (OrderDate)

You can also use the $partition function to get the partition number of a set of partition identity column values, for example, to get 2002 of which partition, the code is as follows:

2. Archive data

If this is the beginning of 2003, then we can archive 2002 of all your transactions into the history order form Histroryorder we just established. The code is as follows:

Switch 1 1 Go

Now let's look at the data for these two tables again:

At this point the Orders table has only 2003 years of data left, and the Odershistory table contains 2002 years of data.

The simple point is to import data from the first area into the first section of another partitioned table.

Of course, we can archive all the transaction data for 2003 years by the beginning of the 2004 year.

Switch 2 2 Go

It is important to note that when we make the data modification according to the district, we must operate the partition table under the same partition function, and the partition structure corresponds, if not this will be an error, for example:

3. Adding partitions

When we need to add a new partition, we need to modify the partitioning scheme, for example, now that we are at the beginning of the year 2005, we need to prepare partitions for the 2005-year transaction, we need to add the partition:

= N'File4', FILENAME = n'G:\data\FG4\File4.ndf' , SIZE = 3072KB, filegrowth = 1024KB) to FILEGROUP [Fg4]go

We have created a new filegroup, and we have also modified the partitioning function and scheme as described above:

Use salesgoalter partition scheme ps_orderdate  next used [fg4]alter partition function  pf_orderdate () Split Range ('2005/01/01') go

Here we use the ALTER partition Scheme ps_orderdate Next used FG4 to specify the data for the new partition in that file. Here Next used FG4 Specifies the fourth filegroup we have just created. Of course we can put in the original set of filegroups, in order to prevent data chaos storage We are mostly the new set of filegroups.

Alter PARTITION function Pf_orderdate () split range (' 2005/01/01 ') represents the creation of a new partition, where split range is the key syntax for creating a new partition.

At this point, we have four partitions, where the interval is as follows:

Filegroup Partition Value Range
FG1 1 (past year, 2003/01/01)
FG2 2 [2003/01/01, 2004/01/01]
FG3 3 [2004/01/01, 2005/01/01]

FG4 4 [2004/01/01, next year]

4. Delete a partition

Delete partition is also called merge partition, simply say is two partition data to merge, for example we want to merge 2002 year partition and 2003 partition to a partition, we can use the following code:

Use salesgoalter partition function  pf_orderdate () Merge range ('2003/01/01' ) Go

That is, the 2003 partition point is removed, the data inside the partition is automatically merged together.

After executing the above code, the partition interval is as follows:
Filegroup Partition Value Range
FG2 1 [last year, 2004/01/01]
FG3 2 [2004/01/01, 2005/01/01]
FG2 3 [2005/01/01, next year]

Merging data from 2002 and 2003 to 2003 years later, we execute the following code:

SELECT Sales. $PARTITION. Pf_orderdate ('2003')

You will find that the result of the return is 1. The original return was 2, because the partition where the data was before 2002 was merged into the 2003 partition.
At this point we execute the following code:

SELECT * fromdbo. Ordershistory   2

The result is that a row of data is not returned, as is the case, because the Orderhistroy table only stores historical data for 2002 and 2003, and before the partition is merged, executing the code above will definitely query for 2003 data, but after merging the partitions, The above code actually queries the data for 2004 in the second partition.
But let's change to the following code:

SELECT * fromdbo. Ordershistory   1

8 rows of data will be queried, including 2002 and 2003, as the data for the 2002 and 2003 years after the merge partition becomes the 1th partition.
5. View Meta data

We can view our partitioning functions, partitioning schemes, boundary value points, etc. through three system views.

Select  from sys.partition_functions    Select  from sys.partition_range_values Select  from Sys.partition_schemes

In layman SQL Server 2008 partition functions and partition tables