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.