Microsoft SQL Server 2012 Performance Tuning Cookbook

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:

• Partition 1: Rows with ID values greater than or equal to 0
• Partition 2: Rows with ID values from 1 to 1,000,000
• Partition 3: Rows with ID values from 1,000,001 to 2,000,000
• Partition 4: Rows with ID values from 2,000,001 to 3,000,000

However, in ...