SQL Server - Table Partitioning technique

Technical concept :

partitioning in simple word is spreads your data in multi file groups , file groups it's physical files on your Hard drive .

Usage:
This technique is used heavily in case of very large tables which are hard to get rid of this data on a regular basis, so it would be difficult to work out on the tables of millions of data that are used only when needed.

Therefore stems need to provide a mechanism to keep all the data available, while maintaining the good
performance of the database.

Partitioning a SQL Server database table is four-steps process:

1-Create the partition function
2-Create File Groups
3-Create the partition scheme
4-Partition the table


The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

partition defines the function how you want SQL Server to split the data. At this point, we are not concerned with any particular table, we select only the general technique to split the data.

Define partitions to determine the limits of each partitions .For example, suppose we have a customer table contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We may decide to split the table into four partitions equally spaced, using the following partition function (I call it customer_func):

CREATE PARTITION FUNCTION customer_func (int 
AS RANGE RIGHT 
FOR VALUES (250000, 500000, 750000)

These limits determine four partitions. The first contains all values ​​less than 250,000. The second contains values ​​between 250,000 and 499,999. The third contains values ​​between 500 000 and 749 999. All values ​​greater than or equal to 750,000 to go in the fourth section.

Note that I used the phrase "RANGE RIGHT" in this example. This indicates that the threshold value itself should go in the partition on the right side. Alternatively, if you have used the "LEFT RANGE", the first partition has included all values ​​less than [U] or equal to [/ U] 250,000; second section will be included values ​​between 250,001 and 500,000, and so on .

Step 2:Create File Groups

Create and defines 4 file groups .

ALTER DATABASE Customers
ADD FILEGROUP fg1;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg2;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg3;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg4;
GO

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg1_dat,
    FILENAME = 'C:\Databases\Customersdb\fg1_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg1;

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg2_dat,
    FILENAME = 'C:\Databases\Customersdb\fg2_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg2;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg3_dat,
    FILENAME = 'C:\Databases\Customersdb\fg3_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg3;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg4_dat,
    FILENAME = 'C:\Databases\Customersdb\fg4_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg4;

Step 3: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme

 AS PARTITION customer_func

 TO (fg1, fg2, fg3, fg4)

Notes that now link the partition function of the partition scheme, but we are still not linked to the partition scheme to any specific database table. Since the re-use of force comes into play. We can use this partition scheme (or simply a function of the division, and if we wish) on any number of database tables.

Step 4: Partitioning a Table

After defining the partition scheme, and you are now ready to create a partitioned table. This is the simplest step of the process. You can simply add the condition "ON" for the statement to create a table to determine the partition scheme and table column that applies to it. You do not need to determine the function of the partition because the partition scheme sets already.

For example, if you want to create the customer table using the partition scheme we have, and you use Transact-SQL statement following:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)

 ON customer_partscheme (CustomerNumber)

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!