Partitioned Tables and Indexes

Ok, at this point you should know how to set up a PARTITION FUNCTION and a PARTITION SCHEME.  Now, we’re going to move on and apply this SCHEME (and in turn the FUNCTION) to a table and an Index.  I’m going to include a little recap, just in case you’ve missed anything so far.  All of this code should be run in the KOTOR database we defined in the last article.

First, we define the PARTITION FUNCTION:

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

Then, we define the PARTITION SCHEME (assuming the filegroups are already defined for the database):

AS PARTITION myPartitionFunction
TO (fg1, fg2, fg3, fg4, fg5)

Now, we create a table using the PARTITION SCHEME.

CREATE Table items (
     ItemID INT IDENTITY(1,1)
   , ManufacturerID INT -- we're not going to define the FK in this example
   , [Name] VARCHAR(255)
   -- other columns can be defined, but I'm not covering those in this example
ON myPartitionScheme(ManufacturerID)

The change here is we define our table on a PARTITION SCHEME, rather than a filegroup.  Since our scheme already covers several filegroups, it’s an indirect reference to filegroup.  When you specify the scheme, that references a PARTITION FUNCTION, so you’ll have to identify the key for which the function will be applied.  Remember datatypes are important.  If your function is defined for a datatype that your column cannot be implicitly converted, you’re going to get an error.

Hey, a quick side note: once you’ve defined a partition for your table, you could restore parts of your table without affecting the other parts.  Think about it… you could restore a single .ndf file, and you’d be restoring a part of the table without affecting the other parts.  You can use this same technique to back up parts of the database, without locking other parts.  This can become very useful in high volume/high availability databases.

Partitioning an INDEX

You can apply a similar DDL statement to create a partitioned index.

   ON items(name) ON myPartScheme(ManufacturerID)

Did you notice I’m partitioning the index on a column not in my index?  If you remember, indexes can contain extra columns using the INCLUDE clause.  When you create an index on a partitioned table, the server will automatically include the partitioning key in the index created for that table.  That way you can partition the index the same way you partition the table.  Pretty slick, right?

Now that you have all the basics for creating partitions on tables and indexes, take a look at some of your queries.  Do you see any that have a high level of blocking?  Do you see some that respond to lookups a bit slower than you’d like?  Try some partitioning in your development environment, see if you can come up with a solution to your problems through partitioning.  If not, give me a shout… I’ll give it a once over and see what I can do to help.

Now that you have the basics, I’m going to have to move into some administrative tasks with you.  You’re going to need to learn how to manage and alter your partitioning.  Since we all know that today’s solution probably isn’t the forever solution, right?

Stay tuned!

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 *