SQL Server2008 and above table partition operation detailed

Source: Internet
Author: User

1. Create a partition function

2. Create a partition scheme

3. Partitioning the table

Each step is described in more detail below.

Step one: Create a partition function

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

We can define partitions by specifying the boundary conditions for each partition. For example, suppose we have a Customers table that contains information about all customers, differentiated by one by one corresponding customer numbers (from 1 to 1,000,000). We will divide this table into four partitions of the same size by using the following partitioning function:

CREATE PARTITION FUNCTION customer_partfunc (int)  as RANGE right for VALUES (250000, 500000, 750000)

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

Note that the "RANGE right" statement that is called here indicates that each partition boundary value is the left border. Similarly, if you use the "RANGE left" statement, the first partition above should include all data with values less than or equal to 250,000, the second partition has a data value between 250,001 and 500,000, and so on.

Step two: Create a partition scheme

Once a partition function describing how to split the data is given, a partitioning scheme is created to define the partition location ([u]where[/u]). The creation process is straightforward, as long as the partition is connected to the specified filegroup. For example, if you have four filegroups with group names from "Fg1" to "Fg4", the following partitioning scheme will work as desired:

CREATE PARTITION SCHEME customer_partscheme as  PARTITION customer_partfunc to  (FG1, Fg2, FG3, FG4)
Create the file group code as follows:

Note that a partition function is connected to the partition schema, but the partition schema is not connected to any data tables. That's where reusability works. Regardless of the number of database tables, we can use the partition schema (or just the partitioning function).

Step three: Partition a table

Once you have defined the good one partition schema, you are ready to start creating a partitioned table. This is the simplest step in the entire partitioning operation. You only need to add an "on" statement to the table creation directive that specifies the partition schema and the table columns that the schema is applied to. Because the partition schema already recognizes the partition function, you do not need to specify the partition function again.

For example, using the partition schema above to create a customer table, you can call the following Transact-SQL directives:

CREATE TABLE Customers (FirstName nvarchar (+), LastName nvarchar (+), CustomerNumber int) on Customer_partscheme (Custom Ernumber)

SQL Server2008 and above table partition operation detailed

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.