Particionando una tabla horizontalmente

Particionando una tabla horizontalmente

Las tablas en cualquier base de datos guardan la información de los registros, una tabla que tiene muchos registros puede considerarse una tabla crítica, aquella que al consultar o buscar registros demore y cause problemas en los sistemas. Las tablas con muchos registros deben particionarse, para obtener el mejor resultado de la partición la base de datos debería tener varios archivos divididos en varios grupos de archivos en varios discos. (Ver Creación de base de datos). En este artículo mostramos como crear tablas particionadas horizontalmente donde el campo de partición es la clave primaria y donde no lo es.

Para mayor información ver
Partición horizontal de tablas
Particionando horizontalmente una tabla existente
Insertar registros
Crear tablas
Índices en SQL Server
Uso de Include en índices
Índices particionados en SQL Server
Modificar la base de datos: Grupos de archivos
Modificar la base de datos: archivos de base de datos

Usando la base de datos Northwind

use Northwind
go

Ejercicios

Ejercicio 1

Crear una tabla que permita particionar los registros por fecha, de acuerdo a lo siguiente: Registros antes del año 2016, durante el año 2016 y después del año 2016.

Set dateformat dmy
go
Creamos la función de partición.
Create partition function FuncionParticionFecha2016 (Date)
As range left for values ('31/12/2015','31/12/2016')
go

Como la base de datos Northwind solamente tienen el grupo Primary, se va a ubicar todas las particiones en los archivos de ese grupo de archivos, es conveniente creae mas grupos de archivos para obtener el beneficio de la partición de tablas.
Creamos en esquema de partición

Create partition scheme EsquemaParticionFechas2016
as partition FuncionParticionFecha2016
to ([Primary],[Primary],[Primary])
go

Creamos la tabla que luego se va a particionar por el campo de tipo fecha, que no es la clave primaria.
Create table DividiendoFechas2016
(
DividiendoFechas2016Codigo nchar(4),
DividiendoFechas2016Fecha Date,
DividiendoFechas2016Total Numeric(9,2),
constraint DividiendoFechas2016PK Primary key (DividiendoFechas2016Codigo)
)
go

Para particionar la tabla de debe crear el índice particionado (Ver Índices particionados)
Create nonclustered index DividiendoFechas2016IDX
on DividiendoFechas2016 (DividiendoFechas2016Fecha)
include (DividiendoFechas2016Codigo)
on EsquemaParticionFechas2016(DividiendoFechas2016Fecha)
go

Insertando registros para comprobar las particiones
insert into DividiendoFechas2016 values
('0001','12/08/2012',250),('0002','23/07/1966',2250),
('0003','07/10/2016',897.52),('0004','15/09/2020',10),
('0005','14/07/2020',600),('0006','30/01/2016',28)
go
Ver los datos, note que se ha usado la función $partition para mostrar el número de la partición donde se encuentra el registro.
Select
D.DividiendoFechas2016Codigo As 'Codigo',
D.DividiendoFechas2016Fecha As 'Fecha',
D.DividiendoFechas2016Total As 'Total',
$Partition.FuncionParticionFecha2016(DividiendoFechas2016Fecha)
As 'Partición'
from DividiendoFechas2016 As D
go
El resultado se muestra en la siguiente imagen

Ejercicio 2

Crear una tabla particionada con los meses de 2021

Set dateformat dmy
go
if exists
(select 1 from sys.partition_functions
where name = 'FuncionParticionMeses2021')
Begin
Drop partition Function FuncionParticionMeses2021
End
go
Create partition function FuncionParticionMeses2021 (Date)
As range right
for values ('01/02/2021','01/03/2021','01/04/2021','01/05/2021','01/06/2021','01/07/2021',
'01/08/2021','01/09/2021','01/10/2021','01/11/2021','01/12/2021')
go

Agregando mas grupos de archivos a la base de datos Northwind.

Alter database Northwind add filegroup Ventas
Alter database Northwind add filegroup Recursos
Alter database Northwind add filegroup Logistica
go

Agregando archivos a los grupos creados

xp_create_subdir 'C:\North'
go
Alter database Northwind add file (name = 'Viento01', filename='C:\North\Viento01.ndf')
to filegroup Ventas
go
Alter database Northwind add file (name = 'Viento02', filename='C:\North\Viento02.ndf')
to filegroup Recursos
go
Alter database Northwind add file (name = 'Viento03', filename='C:\North\Viento03.ndf')
to filegroup Logistica
go

Esquema de partición para la tabla de los meses del 2021
Create partition scheme EsquemaParticionMeses2021
as partition FuncionParticionMeses2021
to ([Primary],[Primary],[Primary],Ventas,Ventas,Ventas,
Recursos,Recursos,Recursos,Logistica,Logistica,Logistica)
go

Tabla particionada por meses del 2021
Create table Meses2021
(
Meses2021Codigo nchar(4),
Meses2021Descripción nvarchar(50),
Meses2021Fecha Date,
constraint DividiendoMeses2021PK Primary key (Meses2021Codigo)
)
go

Crear el índice particionado para la tabla particionada por fechas 2021
Create nonclustered index Meses2021FechaIDX
on Meses2021 (Meses2021Fecha)
include (Meses2021Codigo)
on EsquemaParticionMeses2021(Meses2021Fecha)
go

Insertando registros para comprobar las particiones
insert into Meses2021 values
('0001','Capacitación SQL Server','12/08/2021'),('0002','Certificación DBA','23/07/2021'),
('0003','Optimización server','07/10/2021'),('0004','Socialización','15/09/2021'),
('0005','Actualización','14/07/2021'),('0006','Licenciamiento organizacional','30/01/2021')
go

Ver los datos, incluye una columna para ver la partición donde se encuentra el registro.

Select
M.Meses2021Codigo As 'Codigo',
M.Meses2021Descripción As 'Descripción',
M.Meses2021Fecha As 'Fecha',
$Partition.FuncionParticionMeses2021(Meses2021Fecha)
As 'Partición'
from Meses2021 As M
go
El resultado se muestra en la siguiente imagen

Ejercicio 3

Particionar la tabla de Clientes (Customers)
Como la tabla Clientes ya existe, debemos crear su Función de partición, luego el esquema de partición y cambiar el índice agrupado por indice agrupado particionado. Es importante anotar que la tabla Customers tiene dos relaciones con otras tablas a través de clave foránea (Foreign Key).

La función de partición para los clientes.
Create partition function FuncionParticionClientes (nchar(5))
As range left
for values ('E', 'J', 'O', 'T')
go
Esquema de partición para la tabla Clientes
Create partition scheme EsquemaParticionClientes
as partition FuncionParticionClientes
to ([Primary],Ventas,Recursos,Recursos,Logistica)
go
Ver la estructura de la tabla Customers
sp_help customers
go
El resultado se muestra en la siguiente imagen

También se puede desplegar desde el Explorador de objetos el nodo Keys de la tabla.
En la imagen se puede ver el nombre: PK_Customers

Como ya existe una índice agrupado para la tabla Clientes (Customers), se debe crear un índice no agrupado para el campo CustomerId por el cual se va a particionar la tabla.
Crear el indice agrupado particionado

Create nonclustered index PK_CustomersIDX
on Customers(CustomerID)
on EsquemaParticionClientes(CustomerID)
go
Ver los clientes, se incluye la función $Partition para ver la partición en la que se encuentran los registros.
select
C.CustomerID As 'Código',
C.CompanyName As 'Cliente',
$Partition.FuncionParticionClientes(CustomerId)
As 'Partición'
from Customers As C
go
El resultado se muestra en la siguiente imagen.

Para visualizar cuantos clientes están en cada partición.
Select
T.name As 'Tabla', I.name As 'Índice',
P.partition_number As 'Nº Partición',
R.value As 'Límite',
F.name As 'Función de partición',
S.name As 'Esquema de partición',
P.rows As 'Cantidad registros'
from sys.tables As T
join sys.indexes as I on T.object_id = I.object_id
join sys.partitions As P on I.index_id = P.index_id and I.object_id = P.object_id
join sys.partition_schemes As S on I.data_space_id = S.data_space_id
join sys.partition_functions As F on S.function_id = F.function_id
left join sys.partition_range_values As R on F.function_id = R.function_id
and R.boundary_id = P.partition_number
where T.name = 'Customers' and I.type = 1
go
El resultado se muestra en la siguiente imagen.

Ejercicio 4

Particionar la tabla productos por el campo de la descripción (ProductName)
La función de partición para la tabla Productos.
Create partition function FuncionParticionProductosDescripcion (nvarchar(40))
As range left
for values ('E', 'J', 'O', 'T')
go
El esquema de partición para la tabla productos
Create partition scheme EsquemaParticionProductosDescripcion
as partition FuncionParticionProductosDescripcion
to ([Primary],Ventas,Recursos,Recursos,Logistica)
go
El índice no agrupado particionado para el campo ProductName
Create nonclustered index ProductosDescripcionIDXParticionado
on Products(ProductName)
include (ProductId)
on EsquemaParticionProductosDescripcion(ProductName)
go
Ver los datos con la columna que indica en que partición se encuentra el registro
select
P.ProductID As 'Código',
P.ProductName As 'Descripción',
$Partition.FuncionParticionProductosDescripcion(ProductName)
As 'Partición'
from Products As P
go
El resultado se muestra en la siguiente imagen.

Ejercicio 5

Particionar la tabla Orders por el campo OrderID

La función de partición para la tabla Orders
Create partition function FuncionParticionOrdenesNumero (Int)
As range left
for values (10200, 10400, 10800, 11200)
go
El esquema de partición para la tabla Orders
Create partition scheme EsquemaParticionOrdenesNumero
as partition FuncionParticionOrdenesNumero
to ([Primary],Ventas,Recursos,Recursos,Logistica)
go
El índice no agrupado particionado para el campo OrderId
Create nonclustered index OrdenesNumeroParticionadoIDX
on Orders(OrderId)
on EsquemaParticionOrdenesNumero(OrderID)
go
Alter index OrdenesNumeroParticionadoIDX on Orders rebuild
partition = all

Ver los datos de las órdenes
select
O.OrderId As 'Nº Orden',
Format(O.OrderDate,'dd/MM/yyyy') As 'Fecha',
$Partition.FuncionParticionOrdenesNumero(OrderId)
As 'Partición'
from Orders As O
go
El resultado se muestra en la siguiente imagen, se puede ver registros de la partición 2 y la partición 3.

Para poder listar la cantidad de registros en cada partición usaremos las vistas del sistema siguientes:
sys.Tables que contiene las tablas.
sys.indexes que contiene los índices.
sys.partitions que contiene las particiones.
sys.partition_schemes que contiene los esquemas de partición.
sys.partition_functions que contiene las funciones de partición.
sys.partition_range_values que contiene los límites de las funciones de partición.

Select
T.name As 'Tabla', I.name As 'Índice',
P.partition_number As 'Nº Partición',
R.value As 'Límite',
F.name As 'Función de partición',
S.name As 'Esquema de partición',
P.rows As 'Cantidad registros'
from sys.tables As T
join sys.indexes as I on T.object_id = I.object_id
join sys.partitions As P on I.index_id = P.index_id and I.object_id = P.object_id
join sys.partition_schemes As S on I.data_space_id = S.data_space_id
join sys.partition_functions As F on S.function_id = F.function_id
left join sys.partition_range_values As R on F.function_id = R.function_id
and R.boundary_id = P.partition_number
where T.name = 'Orders'
go
El resultado se muestra en la siguiente imagen