Creare tabelle partizionate

Il partizionamento delle tabelle consente di distribuirne il contenuto su più file, riducendo l'impatto delle operazioni Insert, Update, delete e Select sul sistema dischi, grosso collo di bottiglia per le performance di un database.

Come funzionano le tabelle partizionate in SQL Server ?
Di seguito un esempio pratico.

La prima cosa da fare è creare il Db, i Filegroup necessari al contenimento delle varie partizioni e i file associati ai filegroup.

Use master
Go

CREATE DATABASE DB_Test_Partition
Go

ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneUno]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneDue]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneTre]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneQuattro]
GO

ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\p1.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PartizioneUno]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\p2.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PartizioneDue]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\p3.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PartizioneTre]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\p4.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PartizioneQuattro]
GO

Il passaggio successivo è creare la partitioning function che definisce il range di valori e il partitioning scheme che associa i valori al filegroup desiderato  :

USE DB_TEST_PARTITION
Go

CREATE PARTITION FUNCTION MYPARTITIONFUNCTION (int)
    AS RANGE LEFT FOR VALUES (1, 10000, 100000) ;
GO

CREATE PARTITION SCHEME MYPARTITIONSCHEME
    AS PARTITION MYPARTITIONFUNCTION
    TO (PartizioneUno, PartizioneDue, PartizioneTre, PartizioneQuattro) ;
GO

I range risultanti sono  quindi 4 :

Range 1 associato al Filegroup [PartizioneUno] che conterrà i valori < 1
Range 2 associato al Filegroup [PartizioneDue] che conterrà i valori >=1 e < 10000
Range 3 associato al Filegroup [PartizioneTre] che conterrà i valori >=10000 e < 100000
Range 4 associato al Filegroup [PartizioneTre] che conterrà i valori >=100000

A questo punto è necessario creare la tabella associandola al partitioning scheme passando come colonna discriminante la partezione la colonna col1. Come potete vedere MYPARTITIONFUNCTION ha un parametro di input di tipo int, col1 è di tipo int e conterrà i dati su cui voglio implementare il partizionamento.
Passerò quindi col1 come input dello schema MYPARTITIONSCHEME

CREATE TABLE MYPARTITIONTABLE(col1 int, col2 char(1000))
    ON MYPARTITIONSCHEME (col1) ;
GO

Ora è necessario popolare la tabella.
Aprite una finestra sul path C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA o sulla folder contenente i vostri file del database ed eseguite uno alla volta i cicli while dello script seguente :

Set nocount On
Declare @i int = -2500
While @i < 1
begin
 --Inserisco un valore nella partizione 1
 Insert into MYPARTITIONTABLE(col1,col2) values (@i,@i)
 set @i = @i + 1
end
GO

Set nocount On
Declare @i int = 1
While @i < 10000
begin
 --Inserisco un valore nella partizione 2
 Insert into MYPARTITIONTABLE(col1,col2) values (@i,@i)
 set @i = @i + 1
end
GO

Set nocount On
Declare @i int = 10000
While @i < 100000
begin
 --Inserisco un valore nella partizione 3
 Insert into MYPARTITIONTABLE(col1,col2) values (@i,@i)
 set @i = @i + 1
end
GO

Set nocount On
Declare @i int = 100000
While @i < 150000
begin
 --Inserisco un valore nella partizione 4
 Insert into MYPARTITIONTABLE(col1,col2) values (@i,@i)
 set @i = @i + 1
end
GO

Cosa accade ai nostri file ?
Il primo While inserisce i valori da -2500 a 0 nella tabella. Ma la tabella è associata a MYPARTITIONSCHEME che a sua volta utilizza la MYPARTITIONFUNCTION per discriminare in quale partizione inserire i dati del ciclo.
I dati sono compresi tra -2500 e 0 (estremi compresi) quindi saranno inseriti nella prima partizione cioè nel file contenuto nel filegroup [PartizioneUno] .
Tornate ora sulla folder contenente i file,  vedrete che solo un file alla volta cresce.
Mentre viene eseguito il primo while da -2500 e 0 viene popolato il file P1 del filegroup [PartizioneUno] , mentre gira il ciclo da 1 a 9999 viene popolato il file P2 del filegroup [PartizioneDue] , il terzo ciclo da 10000 a 99999 popola P3 del filegroup [PartizioneTre] e l'ultimo da 100000 in su popola P4 del filegroup [PartizioneQuattro].
Ogni operazione è quindi indirizzata solo ed esclusivamente sulla partizione di competenza diminuendo drasticamente le operazioni di I/O.
Se i file del database sono disposti su quattro hard disk separati il beneficio in ogni fase della vita della tabella sarà enorme. Tutte le operazioni CRUD (Create Read Update Delete) sui dati della nostra tabella beneficeranno di più risorse e avranno migliori performance. Perchè ? Perchè avremo file più piccoli, avremo file distribuiti su più hard disk, in altre parole diminuiamo drasticamente le operazioni di I/O sul disco, collo di bottiglia per ogni RDBMS.

La fase di selezione dei dati verrà implementata dall'utente come per ogni altra tabella, non è necessario specificare all'engine su che partizione cercare i dati.

--Lavoro su tutte le partizioni
Select * from MYPARTITIONTABLE
--Lavoro sul filegroup [PartizioneUno]
SELECT * FROM MYPARTITIONTABLE WHERE col1 = -1000
--Lavoro sul filegroup [PartizioneDue]
SELECT * FROM MYPARTITIONTABLE WHERE col1 = 1000
--Lavoro sul filegroup [PartizioneTre]
SELECT * FROM MYPARTITIONTABLE WHERE col1 = 11000
--Lavoro sul filegroup [PartizioneQuattro]
SELECT * FROM MYPARTITIONTABLE WHERE col1 = 120000

Facile vero ?

P.S. SELECT * è da evitare, era solo a scopo dimostrativo !!!