Partition Schemes

In my last article I started covering partitioning.  I’m going to pick that up and continue with how to create your database to use multiple filegroups.  Without multiple filegroups, you aren’t going to see how partitioning can really improve your database’s performance.  Let’s set up the database that’s going to house our table of items from Knights of the Old Republic.

CREATE DATABASE KOTOR
ON PRIMARY
    (NAME = primary_data, FILENAME = 'd:\mssql\data\kotor_primary_data.mdf', SIZE = 10MB),
FILEGROUP fg1
    (NAME = fg1_data, FILENAME = 'd:\mssql\data\kotor_fg1.ndf', SIZE = 10MB),
FILEGROUP fg2
    (NAME = fg2_data, FILENAME = 'd:\mssql\data\kotor_fg2.ndf', SIZE = 10MB),
FILEGROUP fg3
    (NAME = fg3_data, FILENAME = 'd:\mssql\data\kotor_fg3.ndf', SIZE = 10MB),
FILEGROUP fg4
    (NAME = fg4_data, FILENAME = 'd:\mssql\data\kotor_fg4.ndf', SIZE = 10MB),
FILEGROUP fg5
    (NAME = fg5_data, FILENAME = 'd:\mssql\data\kotor_fg5.ndf', SIZE = 10MB)
LOG ON
    (NAME = db_log, FILENAME = 'd:\mssql\logs\kotor_log.ndf', SIZE = 10MB, FILEGROWTH = 10MB);

OK, you should already recognize the parts around CREATE DATABASE, ON PRIMARY, and LOG ON. What you may not recognize is the FILEGROUP clauses. These allow you to set up additional files that will house data from the database. Just like the PRIMARY and LOG ON clauses, you have to identify a NAME, FILENAME, and SIZE. FILEGROWTH is optional, if omitted, the server will default to the server default growth, which is probably 10%. I prefer growing by a set size, since percent growths can really come back to haunt you when your database grows to a more serious size (100+ GB).

Now that we have our filegroups defined, let’s move on to..

Partition Schemes

A partition scheme defines the colection of filegroups you want to use for a given partition function. Let’s look at the general syntax.

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

You have to provide the name for the PARTITION SCHEME, and you have to reference a PARTITION FUNCTION that already exists.
In my command I referenced the function we created in the last post.

CREATE PARTITION FUNCTION myPartitionFunction(int)
AS RANGE LEFT|RIGHT
FOR VALUES (10, 20, 30, 40, 50)

Finally you have to enter a filegroup for each partition defined in your partition function. The filegroups you reference must already exist. IF you want to create the filegroups in the same script as the partition scheme, you’ll have to separate the filegroup definition from the CREATE PARTITION SCHEME command with a GO. You can reuse filegroups if you wanted two partitions to share the same filegroup.

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunction
TO (fg1, fg1, fg2, fg3, fg4)

Or you can put all the partitions in the same filegroup… I question the benefits of doing this, since most of the time you’re going to want to split these partitions into separate files (and maybe even split the disks these files are written to) in order to boost read/write efficiency. But SQL does support the ability to put all the partitions into the same file group.

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunction
ALL TO (fg1)

Needless to say, if you use ALL, then you can only identify ONE filegroup.

Now you know how to create filegroups, and define a PARTITION SCHEME. These are only building blocks. Next time, we’re going to cover how to use this PARTITION SCHEME with a table and indexes! Then you’re really going to see the benefits of partitioning. Any questions so far? Just let me know, and I’ll do my best to help you out.

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.

4 comments

  1. greetings, I just wanted to comment and say that I was really impressed with your blog. Keep up the good work! You are a really talented writer and it shows

  2. There is a small typo error in the query to create the database. Should read FILEGROUP instead of FILEFROUP.

    The query should look like this:

    CREATE DATABASE KOTOR
    ON PRIMARY
    (NAME = primary_data, FILENAME = ‘d:\mssql\data\kotor_primary_data.mdf’, SIZE = 10MB),
    FILEGROUP fg1
    (NAME = fg1_data, FILENAME = ‘d:\mssql\data\kotor_fg1.ndf’, SIZE = 10MB),
    FILEGROUP fg2
    (NAME = fg2_data, FILENAME = ‘d:\mssql\data\kotor_fg2.ndf’, SIZE = 10MB),
    FILEGROUP fg3
    (NAME = fg3_data, FILENAME = ‘d:\mssql\data\kotor_fg3.ndf’, SIZE = 10MB),
    FILEGROUP fg4
    (NAME = fg4_data, FILENAME = ‘d:\mssql\data\kotor_fg4.ndf’, SIZE = 10MB),
    FILEGROUP fg5
    (NAME = fg5_data, FILENAME = ‘d:\mssql\data\kotor_fg5.ndf’, SIZE = 10MB)
    LOG ON
    (NAME = db_log, FILENAME = ‘d:\mssql\logs\kotor_log.ndf’, SIZE = 10MB, FILEGROWTH = 10MB);

Leave a comment

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