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