Hola a todos. Hoy os vamos a hablar sobre una de las opciones que tenemos cuando trabajamos con las tablas, para mejorar la forma de almacenamiento y acceso a los datos. Es la opción de particionamiento de la tabla. Esta opción también nos ofrece poder trabajar y realizar ciertas labores de mantenimiento sobre conjuntos de datos, y no sobre toda la tabla.
El particionamiento de una tabla consiste en dividir una tabla grande en conjuntos más pequeños y más manejables, sin tener que crear tablas nuevas. Estos conjuntos más pequeños se utilizan a la hora de administrar y mantener dicha tabla. Estas particiones son transparentes para el usuario final, que lanzará sus querys sin tener en cuenta cómo están los datos almacenados.
El proceso de particionar una tabla no es trivial, aunque una vez entendido no es difícil de realizar. La partición de la tabla se hace por una columna, llamada columna de partición o column key. Esa columna es la que decide donde se distribuyen los datos. En SQL Server, las particiones son por rango. En otros motores de BBDD hay particiones por valores o por algoritmos Hash. Si la columna tiene valores NULL, estos van a la primera partición.
Al guardar los datos por una columna determinada, es importante que las queries a la tabla filtren por esa columna. Y así, poder aprovechar la búsqueda por conjuntos de datos más pequeños que son las particiones.
Ahora, vamos a ver como se realiza el particionamiento de una tabla en SQL Server.
Particionamiento de una tabla en SQL Server
El primer paso es preparar el almacenamiento físico para tener separadas las particiones. Este paso no es obligatorio, pero ya que se busca una separación de los datos para mejora en consulta. Se pueden separar también físicamente.
USE pruebapartition
GO
-- Add Filegroups
ALTER DATABASE [pruebapartition] ADD FILEGROUP [Data01];
ALTER DATABASE [pruebapartition] ADD FILEGROUP [Data02];
ALTER DATABASE [pruebapartition] ADD FILEGROUP [Data03];
GO
-- Add Files
ALTER DATABASE [pruebapartition] ADD FILE ( NAME = N'prupart_1', FILENAME = N'Z:\data\prupart_1.ndf') TO FILEGROUP [Data01];
ALTER DATABASE [pruebapartition] ADD FILE ( NAME = N'prupart_2', FILENAME = N'Z:\data\prupart_2.ndf') TO FILEGROUP [Data02];
ALTER DATABASE [pruebapartition] ADD FILE ( NAME = N'prupart_3', FILENAME = N'Z:\data\prupart_3.ndf') TO FILEGROUP [Data03];
GO
Partition Function
La función de partición a partition function es la encargada de definir los valores límite de las particiones de la columna. Cuando se define la partition funtion se crean siempre («numero de valores límite» + 1) particiones.
Se pueden definir dos tipos de partition functions en cuanto al rango de valores:
- LEFT: El valor LEFT indica que el valor límite se almacena en la partición izquierda, y es el último valor de esa partición.
- RIGHT: El valor RIGHT indica que el valor límite se almacena en la partición derecha, y es el primer valor de esa partición.
USE pruebapartition
GO
CREATE PARTITION FUNCTION [PF_dates](datetime)
AS RANGE LEFT FOR VALUES
(N'2017-12-31T23:59:59',
N'2018-12-31T23:59:59')
Este ejemplo crea 3 particiones (Se han especificado dos valores límite), y se ha definido como LEFT, con lo que los datos se almacenarán en la tabla según la columna de tipo datetime que ya se definirá en la tabla, según el cuadro siguiente:
| Partition # | Partition 1 | Partition 2 | Partition 3 |
| Data Range | <= 31/12/2017 | <= 31/12/2018 | >= 01/01/2019 |
Partition schema
La partition schema mapea los rangos de valores especificados en la partition function con los filegroups creados. Una partición, se puede mapear por filegroup, o varias particiones en un filegroup. Esto tiene la ventaja de que los filegroup se gestionan individualmente, con lo que se puede decidir sobre que filegroup se quiere hacer backup, se pueden poner ciertos filegroup en Read Only, etc.
CREATE PARTITION SCHEME [PS_dates]
AS PARTITION [PF_dates] TO
([Data01],[Data02],[Data03])
Con esto se han definido las particiones, ahora hay que crear una tabla especificando la column key.
CREATE TABLE [dbo].[facturas] ( [idfact] [bigint] IDENTITY(1,1) NOT NULL, [fechafact] [datetime] NOT NULL, [cliente] varchar(100) NOT NULL ) ON [PS_dates]([fechafact])
Una vez creada la tabla, se pueden insertar unos datos de prueba con diferentes fechas. De esta forma podemos comprobar que cada uno de ellos entra en su partición correspondiente.
Insert Into dbo.facturas([fechafact],[cliente]) values (N'2017-11-06T12:00:59','Client01'); Insert Into dbo.facturas([fechafact],[cliente]) values (N'2018-05-31T12:10:30','Client02'); Insert Into dbo.facturas([fechafact],[cliente]) values (N'2019-02-21T11:20:59','Client01'); Insert Into dbo.facturas([fechafact],[cliente]) values (N'2018-01-01T16:21:59','Client03'); Insert Into dbo.facturas([fechafact],[cliente]) values (N'2017-12-31T19:00:59','Client04');
También, se puede comprobar el número de filas que se han almacenado en cada partición con la siguiente consulta:
Select $PARTITION.[PF_dates]([fechafact]) as PartionNum, COUNT(*) as CountRows
From [dbo].[facturas]
Group by $PARTITION.[PF_dates]([fechafact])
Y se comprueba que se han almacenado los datos separados cada uno en su partición según la columna fecha.
| PartionNum | CountRows |
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
Se puede acceder a la sintaxis completa para el particionamiento de una tabla o un índice en la documentación oficial de Microsoft en los siguientes link:
En posteriores post hablaremos de cómo acceder a los datos en las tablas con particiones.
Esperamos que os sea de utilidad.
Un saludo,
Equipo de Base de datos