Table and Index Partitioning - Microsoft SQL Server 2012 Performance Tuning - page 298

With table partitioning, the following tasks can be performed in an efficient manner:
F Retrieving a certain range of data
F Deleting and archiving old data
F Loading new millions of data in bulk
F Rebuilding and reorganizing indexes
We can also partition an index on a large table and rebuild or reorganize a partitioned index
on a particular partition. Because Table/Index partitioning is a very important feature that
helps in managing a large amount of data in an efficient manner, this becomes the subject
of this chapter.
You can create as many as 15,000 partitions in SQL Server 2012. You cannot
partition a column of the data type text , ntext , image , xml , timestamp ,
varchar (max) , nvarchar (max) , or varbinary (max) .
Partitioning a table with RANGE LEFT
Let's suppose that you are required to design a database and there is one particular table
that is expected to contain millions of rows. To improve the performance, you decide to
partition this particular table based on ID column (identity column) in such a way that each
partition contains a certain number of rows, rather than having all the millions of rows in
one single table. Initially, you decide to start with four partitions, so that they contain rows
as follows:
F Partition 1: Rows with ID values greater than or equal to 0
F Partition 2: Rows with ID values from 1 to 1,000,000
F Partition 3: Rows with ID values from 1,000,001 to 2,000,000
F Partition 4: Rows with ID values from 2,000,001 to 3,000,000
However, in our case, partition 1 is never going to contain any rows, because the value in
the ID column will start from 1, increasing by 1, and partition 1 will always remain empty; it
is still a good idea to have this partition range for scalability and the future requirement of
archiving data.
Table partitioning has two configurations to set range values of partitions— RANGE LEFT and
RANGE RIGHT . In this example, we will use RANGE LEFT to perform table partitioning.