Partitioned Tables in SQL Server 2005

Nigel Rivett provides an in-depth, practical examination of how to create and manipulate partitioned tables and indexes in SQL 2005.

Partitioned Tables in SQL Server 2005

Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. The feature is only available for enterprise and developer edition. For other editions you can get a similar functionality with a partitioned view.

This article focuses on how to create a partitioned table and manipulate the partitions, rather than exploring the performance aspects.

Creating the Partitioned Table

First of all, we will create a partitioned table and prove that it is acting in a partitioned manner, in that the queries will only access the partitions that are required.

To start with, we need to create a partition function. This will define how many partitions exist, and the values contained in partition columns within those partitions.

The partition function has been named MyPartitionRange.

The partition column is an int.

‘Range left’ means that the value is the upper bound for the partition.

The above code will define a partitioned table that contains 3 partitions.

  • Partition 1 – Partition value <= 1
  • Partition 2 – Partition value > 1 and <= 2
  • Partition 3 – Partition value > 2

As the partition column is an integer the partitions will actually be

  • Partition 1 – Partition value <= 1
  • Partition 2 – Partition value =2
  • Partition 3 – Partition value > 2

Note: that the range left / range right determines the partition for data that matches the partition literal value. For integer data this would change the partition number for all the data.

Now we create a partition scheme:

The partition scheme is named MyPartitionScheme and references the partition function MyPartitionRange. All of the partitions are to be held on the primary filegroup.

Now we can create the table. All that is needed is to reference the partition scheme naming the partition column in an “on” clause.

We can check the table structure via…

…which gives…

Now we add some data:

… and check that the rows have been added to the correct partitions…

… giving as expected…

A function, $partition, is available to give the partition number for the data.

The following is a valid statement and shows that a row with partition value 6 would be added to partition 3:

The function gives the row counts for each partition and so can be useful to find which partition holds the data.

Now to test that queries only access the required partition. We make partition 2 very large (this is why we made s a char(8000)).

…and we check the rowcounts …

…giving as expected:

On my laptop that is enough to give an appreciable difference in query times – if the first query below is too quick to detect on your machine then increase the number of loops to add rows to the partition.

Now try the queries:

You should find that the first takes a lot longer than the second because in both cases only one partition is accessed, a single read for partition 1, many reads for partition 2.

Adding and Removing Partitions

In the previous example, we added data to the partition by inserting rows into the partitioned table. It is also possible to populate a table, and then add that table to the partitioned table as a partition. There are many restrictions on the nature of the table and data that can be added.

The command to add the table as a partition is “alter table …. Switch…”. It actually swaps the table with a partition already existing in the partitioned table.

We will now add a new partition to MyPartitionedTable for partition value 3 and then swap it for a new table MyNewPartition.

To add a new partition to MyPartitionedTable, use the split range command on the partition function.

This has added a new partition for partition value 3. The reverse of this is a merge range statement:

Note that the existing rows for partition value 3 have been moved to partition 3. The row with partition value 4 has been moved to partition 4.

We create a new table to swap with a partition. This table must have the same structure as the partition. It must also include a check constraint to ensure that the partition column values in the table are included in the correct partition. The check constraint must be at least as restrictive as the partition range function for that partition.

I normally create and populate the table and add the check constraint later. Now we add some data to the new table:

To perform this operation the partition must be empty, so…

And we can swap the table with the partition:

Viewing the partitions:

We see that the partition has been swapped with the new table.

The advantage of this is that the swap does not move the data – it just updates the metadata so that the table becomes the partition. This means that it is very fast. The table can be created, populated, and then added as a partition, thereby causing minimal impact on the partitioned table.

Switching a populated partition

Of course the partition that is being swapped out would often contain a lot of data – usually this would be used for adding another partition to the right to split up the data. Deleting the data from the partition would not be feasible – nor would the split on a populated partition.

To accomplish this, you would need to first populate the two tables to replace the ‘catch all’ partition (in our scenario partition 3).

Now create a table MyOldPartition3 – remember this must be the same structure as above. As this is a destination for a switch the check constraint must be less restrictive than that on the partitioned table.

Now switch the partition out:

Now the partition split can be carried out on an empty partition and the two partitions switched in without moving any data.

Identities in a partitioned table

Remember that an identity is not guaranteed to be unique or sequential. It just allocates the next value from the current seed. With that in mind nothing that follows should come as a surprise.

For this we will create a new partition function, scheme and table:

i           s          PartCol
———– ———- ———–
1           a          1
3           b          1
2           a          2
4           b          2

Showing that the identity is a property of the partitioned table rather than the partition.

More interesting is what happens when partitions are swapped:

i           s          PartCol
———– ———- ———–
1           a          1
3           b          1
2           a          2
4           b          2
1           c          3
2           d          3

And we see duplicate identity values from the new partition.

Adding a new row:

i           s          PartCol
———– ———- ———–
1           a          1
3           b          1
5           e          1
2           a          2
4           b          2
1           c          3
2           d          3

We see that the partition swap has not affected the identity seed for the table.

Partitioned tables and Indexes

If the index contains the partitioning column then the index is referred to as being ‘aligned’ with the table.

If the index uses the same partitioning scheme as the table and is in the same filegroup then the index must be aligned with the table.

For a non-clustered non-unique index the partitioning column can be included to align the index rather than being indexed.

I think it is best to always explicitly include the partitioning column in your indexes.

The following assumes a single filegroup and scheme.

Clustered index

As stated this index must be aligned with the table. If it is not then the partitioning column will be implicitly added as the last column of the index.

…gives the error…

Whereas these all succeed:

This will also work:

…showing that the partitioning column has been implicitly added to the clustered index in the partitioned table.

Note that this extra column will not be shown by sp_helpindex on the partitioned table nor by scripting the index but it is shown by sys.index_columns

I don’t know why Microsoft decided to add the column to the index automatically. I think it would be less confusing to give an error, thereby forcing the user to add the column explicitly.

Unique index

Unique indexes must contain the partitioning column as an indexed column.

…gives the error:

… one of the more explanatory error messages.

The Partitioning column must be part of the index so…

…will also fail.

Unlike clustered indexes the partitioning column must be explicitly part of the index. These will both work:

Non-unique index

Non-unique indexes do not need to have the partitioning column as part of the index, it can be an INCLUDE column. If it is not explicitly included, then the column will be automatically added – again this will not appear in sp_helpindex.

Is successful, as are…


Partitioning on multiple columns

Although the partitioning column must be a single column, it does not need to be numeric and it can be calculated so that the range can include multiple columns. For instance it is common to partition on datetime data by month. This will work well, because that data is usually in a single column, but what do you do if you have data for multiple companies and you also want to partition by company? For this you could use a computed column for the partitioning column. This will create a computed column using the ‘company id’ and ‘order month’ which is then used for the partitions. It will partition three companies for the first three months of 2007.

The computed column must be ‘persisted’ to form the partitioning column.

We will investigate the maintaining of partitioned data in this table later.

Monthly Data – the sliding range

A common requirement is to partition by month. This means that new month partitions need to be added and possibly old data partitions removed. I will describe the process for the addition of a new partition for later data, to remove an old partition the process is the same except that you swap out two partitions, merge the range and swap in a single table.

We create a partitioned table for data by OrderDate month

This will give four partitions…

Therefore the data will be split intopartitions by month, and we insert some test data:

To add the next month’s partition it is possible to just split the range and let the system take care of the data. This though would mean that the data would be off-line for the duration of the operation. It is better to use the experience we have gained in switching partitions to create the new data in separate tables then switch them in. This means that the table would be off-line for a very short time – just while the switch operations are taking place.

If data is being continually added to the partitioned table then a snapshot can be taken, the new tables prepared on this and the switch-in operation will need to take the table off-line, merge the new data with the prepared snapshot data then perform the switch-in. An identity column on the table would help to identify new data added since the snapshot. This would mean longer downtime than for static data but still a lot less than splitting a populated range.

To add a new month’s partition of static data:

  1. Create the table containing the new months data.
  2. Create the table containing the data after the new month
  3. Swap out the last month from the partitioned table
  4. Split the (empty) range in the partitioned table
  5. Swap in the new months data
  6. Swap in the table containing the data after the new month
  7. Check the result
  1. Create the table containing the new months data.

In the initial discussion we created a table for this but you might find it easier to create a partitioned table for the operation.

Note that if you script the existing table and indexes make sure that you remember that not all indexed columns may appear in the index script.

Also if you take this route you will have to use a new partition function and scheme as you will need to split the range. I prefer to use non-partitioned tables for flexibility.

To population of the table will depend on where the data resides but wil only affect the production table if you need to read the data from that table.

Create the table containing the data after the new month

In the same way as the previous table populate with the data that is later than the new month.

At this point take the production table off-line for the swap.

Swap out the last month from the partitioned table

For this you will need an empty table to swap the data into. It is tempting again to use a partitioned table for this – if so you will need to create a new partition function and scheme as you would not want to lose the data until after splitting the range on the production table.

Split the (empty) range in the partitioned table

This is done by adding a new value to the partition function.

As the partition is empty this should be quick as it just means creating a new empty partition.

Swap in the new month’s dataSwap in the table containing the data after the new monthCheck the result

Gives the expected result:

partition_id         object_id   index_id  partition_number hobt_id              rows
——————– ———– ——— —————- ——————– —-
72057594038845440    213575799   0         1                72057594038845440    1
72057594038910976    213575799   0         2                72057594038910976    1
72057594038976512    213575799   0         3                72057594038976512    1
72057594039042048    213575799   0         4                72057594039042048    1
72057594039173120    213575799   0         5                72057594039173120    2
72057594039238656    213575799   0         6                72057594039238656    1

Now the table Orders_200704_Old can be dropped at your leisure.

Adding a new partition with a computed partition function

We return to the table partitioned by company and month.

In order too add a new month to this table, you will need to split each company’s range for that month. To add a new company partition means splitting all months for that company.

There is no need to add all the partitions in one process, these operations can be performed one partition at a time. This should not affect the results of queries on the table.

The process of adding the new company or month is the same as for the sliding month data, only with many splits and swaps.

Other uses of partitioned tables

Usually, partitioned tables are used to horizontally, or vertically, partition the data. However, the partitions are sometimes used for different purposes – not partitioning the data at all.

I recently came across a reporting system that was querying a single flat table for aggregated results. The table was about 15Gb in size and could be filtered and grouped on any combination of columns. Indexing by date meant that a report for a year would take about 20 minutes – far too long (performance checked just before the release date of course), in fact anything more than 3 months was unacceptable. Normalising and using a view helped with the retrieval of the filter column data, but the actual report was limited by the amount of data it needed to aggregate.

Due to time constraints and policy, we were not allowed to create a cube and the report could not call a stored procedure. Oddly, there was a lot of flexibility in the query used to extract data but it had to access the single table.

The solution was to create a partitioned table. The first partition (partition value = 1) contained the old data table and would still be slow to access.

The second partition contained aggregated data, aggregated by filter columns that kept the table size less than 200K rows. All partitions have the same structure so those filter columns that were excluded were set to null. Accessing this partition was quick enough for any report.

The report application was then changed to check if the filter/grouping columns were all included in the second partition – if so it appended “and PartCol = 2” to the query otherwise it appended “and PartCol = 1”.

The reporting application could warn the users if they were about to do something that would take a long time.

We then checked how the reports were being used, and selected combinations of columns that could be used for other partitions. The partitions were added to the table (not affecting the system) and then, at a later time, the reporting application changed to use the new partitions.

This would have been easier with a stored procedure as the partitions could have been left as separate tables and the stored procedure could choose which to query, but a stored procedure call was not allowed by the reporting application.