Partitioned Tables

  • Article

On one of my chats with my fellow SQL Server PFE’s, we started talking about objects that have millions and millions of records, how accessing them can be really slow, and of course its administration can be a real pain. With these in mind we starts talking about Partitioned Tables, that in simple words is broke a millions records table on many parts.

This feature is enabled on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2. To generate the partitioned tables you will need to identify the key (column) that you are going to use to partition the table. To create the partitioned table you need to execute these steps:

  • Create a partition function (PARTITION FUNCTION); this will define the range for every partition. You can use two methods on the ranges, Left or Right; this will define how the ranges are analyzed. Review the following example:  

CREATE PARTITION FUNCTION pfAnualR(int) AS RANGE RIGHT FOR VALUES(2008,2009,2010,2011)

CREATE PARTITION FUNCTION pfAnualL(int) AS RANGE LEFT FOR VALUES(2008,2009,2010,2011)

Primera Partición < 2008

Primera Partición <= 2008

Segunda Partición >= 2008

Segunda Partición > 2008

Tercera Partición >= 2009

Tercera Particion > 2009

Cuarta Partición >= 2010

Cuarta Particion > 2010

Quinta Partición >= 2011

Quinta Particion > 2011
  • Create a partition scheme (PARTITION SCHEME), this will define the filegroups where the partitions will be placed, you will need to create the filegroups prior to create the partition scheme, review the following example (for the successful execution of this script, you’ll need to create 5 filegroups named FG01, FG02, FG03, FG04, FG05):

CREATE PARTITION SCHEME psAnualR AS PARTITION pfAnualR TO ([FG01], [FG02], [FG03], [FG04], [FG05])

CREATE PARTITION SCHEME psAnualL AS PARTITION pfAnualL TO ([FG01], [FG02], [FG03], [FG04], [FG05])
  • When creating the table, the only difference is than instead of defining a filegroup where the object will be placed, you’ll define a partition function and the key column, Example:

CREATE TABLE tblEstadosR (Ano int, Mes int, Dia int, Tipo varchar(30), Importe float) ON psAnualR(Ano)

CREATE TABLE tblEstadosL (Ano int, Mes int, Dia int, Tipo varchar(30), Importe float) ON psanualL(Ano)
  • Now that you have your table, put the data on it:

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2007,08,22,'Cargo',13.00) INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2008,08,22,'Cargo',34.00)INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2009,08,22,'Cargo',23.00)INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2010,08,22,'Cargo',78.00)INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2011,08,22,'Cargo',98.00)INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2012,08,22,'Cargo',14.00) GO

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2007,08,22,'Cargo',13.00)INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2008,08,22,'Cargo',34.00)INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2009,08,22,'Cargo',23.00)INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2010,08,22,'Cargo',78.00)INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2011,08,22,'Cargo',98.00)INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2012,08,22,'Cargo',14.00) GO

If you want to know on which partition a piece of data reside, use the following TSQL Command $PARTITION, this command returns the partition number on which the data is placed. Review the following example:

SELECT Ano, BDPrueba.$Partition.pfAnualR(Ano) FROM tblEstadosR

SELECT Ano, BDPrueba.$Partition.pfAnualL(Ano) FROM tblEstadosL

This Query results enhanced the RIGHT and LEFT range explanation.

Primera Partición < 2008 Segunda Partición >= 2008 Tercera Partición >= 2009 Cuarta Partición >= 2010 Quinta Partición >= 2011 Quita Particion >= 2011

Primera Partición <= 2008 Primera Partición > 2008 Segunda Partición > 2009 Tercera Partición > 2010 Cuarta Partición > 2011 Quinta Particion > 2011

On this post you have learned the basic concept son table partitioning, some of its advantages are:

-        Sliding Windows scenario

-        Enhance Administrative activities, like:

  • Index Maintenance
  • Partition Compression
  • Storage Management

Because of the parallelism some queries can perform slower using partitioned tables than using regular tables

On another posts we will discuss topics like:

-        Advantage and disadvantage on partitioned tables.

-        Considerations on implementing Sliding Windows.

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”