Table partitioning in SQL Server

Now a day’s data is more important than any other thing so we do need to keep a backup of all our hard works at somewhere safe everyday. Think about the growing organizations like healthcare, banking sectors etc, their activity tables or so called transaction tables grows with the organization. These kind of organization does extensive analysis on historical data and they want it to reside with the current data. As a database professional, we know how tough it is to keep the historical data with the current? Database management and maintenance are also challenging, so we think of implementing concept like table portioning to reduce the maintenance headache. This concept is also known as Horizontal partitioning. PARTITIONED_TABLE_01

  1. File groups
  2. Secondary data files
  3. Partition Function
  4. Partition Scheme
  5. Table

Download the sample OrderHistory table Here we will be playing around with OrderHistory table through out the session. We have 4 years data inside that table (2011, 2012, 2013 & 2014). So the best idea to get the table partitioning done on OrderDate column. File Groups & Data Files:

  • We can do the table partitioning with just a single file group but it’s always advised to keep these types data at different drives, like most recent data on faster drive and historical data on a slower or drive respectively. So lets create the file groups and data files first

 Partition Function :

  • Partition function sets datatype and range values.
  • Partition function are configured as LEFT and RIGHT. (LEFT means the exact match goes to LEFT PARTITION and vice versa)
  • Let’s take an example of apple, to cut the apple we need a knife (i.e Partition Function which will be used to cut the table into parts.)

Partition Scheme:

  • PARTITION SCHEME maps the partitions to the file group and manages the data flow; which data should go into which partition
  • PARTITION SCHEME is like the handle of a knife, operates PARTITION FUNCTION

Partitioned Table:

  • This is not like those normal table we create, this table creation syntax is little different, we add partition scheme name in place of file group name.

PARTITIONED_TABLE_02Note: I have only 4 partition and are for 2011, 2012, 2013 & 2014 respectively.  Altering Partition Tables:

  • We are done so far by partitioning the historical data into multiple file groups. Now what about next year data?
  • It’s usual that the next year data will also come into the system after couple of months, now we should be ready with the room to keep the next year’s data in a managed way like we have for previous years.

PARTITIONED_TABLE_03

  • Above pic states that the new data for 2015 went into the current partition that’s for 2014.
  • Now we need to be prepared for 2015 data insertion. We have already created the file group and a data file for 2015, but our partition function and scheme can accommodate only up to 2014 data so they both needs be altered before we really allow new data to come into the system.

PARTITIONED_TABLE_04 Retiring Partitions (SWITCHING OUT):

  • Suppose the management decided to preserve the 2011 year data and archive them into another disk. then in that case, we need to switch out the particular partition from current partitioned table.

PARTITIONED_TABLE_05 Removing Partitioned Table:

  • Make sure you preserved the real time data before removing the partition table.
  • We need to remove the dependency before removing the partition thing completely from the database.
  1. DROP THE TABLE
  2. DROP PARTITION SCHEME
  3. DROP PARTITION FUNCTION
  4. REMOVE THE DATA FILE
  5. REMOVE THE FILE GROUP
  6. Done!

Limitations:

  • Available only in Enterprise edition
  • Can not have a primary clustered key though you can have non-clustered index on it
  • A partition can be switched into a table if it’s exists in the same filegroup only
  • Index of the partitioned table must reside the the same filegroup
  • Computed column can not participate in partitioning until unless they are persisted.
  • No full text and xml indexes are allowed
  • All DDL changes related to partitioning does schema lock exclusively

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: