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)
AS RANGE LEFT|RIGHT
FOR VALUES (10, 20, 30, 40, 50)
GO

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

CREATE PARTITION SCHEME myPartitionScheme
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)
GO

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.

CREATE NONCLUSTERED INDEX ix_items__name
   ON items(name) ON myPartScheme(ManufacturerID)
GO

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!