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

We then create our sample table called tbl_SampleRecords , with ID as the clustered
primary key. Note that the syntax of CREATE TABLE is followed by the ON ps_OneMillion_
LeftRange(ID) clause. This clause specifies the name of the partition scheme and the
partitioning column, which happen to be ps_OneMillion_LeftRange and ID , respectively.
Remember that the data type specified in the partition function and the data type of the
partitioning column ID must match.
Once the table is created, we insert 5 million sample records. In order to generate 5 million
sample rows, we cross join the system catalog view sys.columns twice and retrieve the top
5000000 rows of resulting cross joins. We generate the serial value of ID with the help of
the ROW_NUMBER() function. The resulting rows will be inserted into our partitioned table
tbl_SampleRecords .
By querying the sys.partitions catalog view, we examine the state of partitions and the
number of rows in each partition. Note how the first partition remains empty because there
is no record whose ID value is less than 1. Also notice that after the first 3 million rows
are inserted into partitions 2, 3, and 4, each having 1 million rows, the remaining rows are
inserted into partition 5, because partition 5 is the last partition and an open-ended partition.
Finally, we use the $PARTITION.pf_OneMillion_LeftRange() function to retrieve only
rows of partition 4. This function accepts the value of the partitioning column (value of the ID
column) and returns the partition number to which that particular value belongs. This is the
most obvious benefit of table partitioning; once our table is partitioned, we can retrieve data
only from a specific partition of the table and the remaining partitions are eliminated from
being queried, which greatly improves the performance of queries.
Partitioning a table with RANGE RIGHT
As we saw previously, there are two ways to apply partition ranges while performing table
partitioning. In this recipe, we will do the same table partitioning that we did in the previous
recipe, Partitioning table with RANGE LEFT . However, this time we use the RANGE RIGHT
option for our table partitioning.
Getting ready
The following are the prerequisites for this recipe:
F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
F Path C:\SQLData should be available on your machine