Partitioning was a feature added with Microsoft SQL 2005 to allow users to split up large tables across multiple storage locations.  Partitioning can be applied to tables, indexes and indexed views.  By partitioning the data across multiple locations you can speed up query times, reduce contention between queries, and improve overall performance under certain conditions.

Partition Functions

In order to partition your object, you must first define how you want to split the data.  A partition function will allow you to define the boundary points for the different partitions of data.  Consider the following table:

ItemID ManufacturerID Name
1 10 Arkanian Heavy Pistol
42 12 Casus Fett’s Heavy Blaster
13 31 Genoharadran Blaster

Let’s say your table held all the items from Knights of the Old Republic, and you were having speed issues with look-ups.  Your solution: partition the table by ManufacturerID, that way each manufacturer would be in a separate file group, and you should speed look-ups dramatically (for now this is an assumption, we can look at the actual performance differences in a later article).

An example of a partition function we could use for this table would be:

CREATE PARTITION FUNCTION myPartitionFunction(int)
FOR VALUES (10, 20, 30, 40, 50)

Every partition function you will define requires a name, a data type, and at least one boundary point.  I’d like to point out one thing: you can’t define a partition function on a text, ntext, image, varbinary(max), timestamp, xml, varchar(max), or user-defined datatypes.  Oh, and if you use a computed column,  that column will need to be persisted in order to partition on it.  Since the computed column has to be persisted, that means it has to be deterministic too (no variable values allowed).

Think about it, if you’re not storing the values, how can you partition on them?

Notice the Range can be LEFT or RIGHT, Basically this identifies which partition holds the partitioning value, IE which partition would hold 10, 20,  etc.  If you want the lesser partition to hold the value, then you choose RANGE LEFT, otherwise you choose RANGE RIGHT.  The difference will come into play when you try tweaking the performance of your partitioned object, and how many records are stored in each partition.

In our example, by choosing RANGE LEFT, each of the rows you see in the table will appear in a separate partition.  If we had chosen RANGE RIGHT Arkanian Heavy Pistol and Casus Fett’s Heavy Blaster would both appear in Partition 2.

There are three points I’d like to make about Partition Functions now:

  • Notice the partition function doesn’t have a clause that identifies a specific database object.  That means you can reuse this partition function across multiple database objects.  That can be really useful if you have multiple tables that could benefit from partitioning (if you have one that could benefit, chances are there are more!)
  • You can only have 1000 partitions for a single object.  What are the odds you’d need more than that?  If you do, check into SQL 2008 Parallel Data Warehouse… you may want to read up on that.
  • As a result of having only 1000 partitions, you can only have 999 boundary points defined.

Next time we visit Partitions, I’m going to make sure you are familiar with creating a database with multiple filegroups, and then move on to Creating a Partition Scheme.

Do you have any questions so far?  If so, send them in… I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *