Implement partitioning solutions

May include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers).

Partitioning means that you store table data in different file groups, instead of using only one file group. By default, SQL Server treats every table as if it were partitioned, but having only one partition.

Partitioning is implemented horizontally, so that you store collections of rows in different file groups, not columns. You should consider partitioning a table when it is excepted to contain lots of data, which is being used in different ways. MSDN comes up with the example of a sales table, with the current month used primarily for inserts, and the previous ones for selects. This is a good candidate for partitioning solutions, because of the different operations performed on the different parts of the table.

To create a partitioned table, you need to do the following:

  • Create a partition function
  • Create a partition scheme
  • Create the table using the scheme

A partition function defines how the table should be partitioned. It excepts the number of partitions, the column (only one) by which you plan partitioning, and the range of column values in each partition. Note that you cannot use timestamp, ntext, text, nvarchar(max), varchar(max), image, xml, varbinary(max), CLR user-defined types to partition by. Here’s some syntax:

AS RANGE LEFT FOR VALUES (‘20100101’, ‘20100201’, ‘20100301’);

You can specify RIGHT instead of LEFT. This sets which side of each boundary should belong to the partition.

After creating the partition function, you need to set up a partition scheme to use it. It simply maps the defined function to file groups that you’ll define. The syntax is pretty simple:

AS Partition MyFunction
TO (testFg1, testFg2, testFg3);

The last thing to do is to create the desired table using the partition scheme:

CREATE TABLE MyTable (TheDate datetime, TheName nvarchar(50))
ON MyScheme (TheDate);

As you can see it isn’t too complicated to implement a partitioning solution. Keep in mind the order of the tasks, and read a lot of MSDN to get deeper understanding.

Tags: , ,

One response to “Implement partitioning solutions”

  1. Conor says :

    I would be interested in learning about MERGE, SPLIT, SWITCH and see some simple examples.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: