In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft SQL Server 2008R2 beschrieben.
Diese Funktion wurde mit der Version 2005 eingeführt und ermöglicht die Verteilung von Daten einer Tabelle in verschiedene Dateigruppen um diese bspw. Archivieren zu können indem die Daten anhand eines Merkmals in verschiedene Dateigruppen unterteilt werden.

Folgendes Beispiel beschreibt die einer Buchungstabelle in der die Datensätze anhand des Buchungsdatums in einzelne Dateigruppen unterteilt werden können. So kann bei anwachsen der Datenbank eine einzelne Datei, wo die Datensätze nicht mehr benötigt werden, gesichert und geleert werden. Dadurch wird die Datenbank wieder kleiner und erhöht gleichzeitig die Performance der Datenbank.

Vorab möchte ich noch darauf hinweisen, dass die Partitionierungs-Funktion nur in der Enterprise/Datacenter Edition verügbar ist.

Ausgangssituation für folgendes Beispiel:

• Eine Buchungstabelle soll erstellt werden in der Daten aus einer anderen Datenbank übernommen werden
• Es werden Daten aus den Kalenderjahren 2010, 2009 und früher übernommen.
• Die Daten die früher als 2009 eingegeben wurden sind nur wenige
• Die Partitionierung soll erweitert werden können um zukünftige Geschäftsjahre aufnehmen zu können

Lösungsmöglichkeit: Zuerst werden 4 Dateigruppen in die Datenbank eingefügt für die Geschäftsjahre 2011, 2010, 2009 und eine Datei für alle vorherigen Jahre:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv] Nun wird zu den 4 Dateigruppen jeweils eine Datendatei hinzugefügt:
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2009.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2010.ndf' ,SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Aktuell.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Archiv.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird eine Partitionierungs-Funktion erstellt, die auf ein DATETIME Feld prüft und die Daten entsprechend verteilt:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010 geht
);

Nun wird das Schema erstellt was die Funktion als Basis für die Datenverteilung nutzt und diese dann in die Dateigruppen aufteilt:
CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011');
Als nächstes wird die Buchungstabelle erstellt die dem Schema zugeordnet wird. Das Datumsfeld, welches die Daten an die Funktion zur Verteilung weitergibt, muss mit in den Primärschlüssel aufgenommen werden:
CREATE TABLE dbo.buchungen (
[uid] uniqueidentifier default NEWID() NOT NULL,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY) ON ArchivierungsSchema(datum);
GO
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]);

INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2008', '20080308', '100');
GO
-- Insert in Archiv2009
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('März 2009', '20090308', '100');
GO
-- Insert in Archiv2010
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Februar 2010', '20100209', '100');
GO
-- Insert in Archiv2011
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2011', '20110120', '100'); Um zu prüfen ob die eingetragenen Datensätze sich in den richtigen Partionen befinden, kann folgendes Statement verwendet werden:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Hier sehen wir dann die Datenverteilung wie die Datensätze sich durch die Funktion in die richtigen Dateien verteilt haben.

Problematisch wird es dann wenn ein neues Geschäftsjahr anbricht und Datensätze für 2012 eingetragen werden. Diese Datensätze befinden sich dann in der Partition für 2011: INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2012', '20120115', '300'); Dieses Problem kann dann durch Erstellen einer neuen Dateigruppe und dem Anpassen der Funktion gelöst werden:
-- Neue Dateigruppe für 2012
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
GO

-- Neue Datei für die Dateigruppe 2012
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]

Nun muss das Schema angepasst werden um die neue Dateigruppen zu verwenden, diese wird dann als Standard hinten dran gestellt.
Weiterhin wird durch die SPLIT() Funktion eine neue Begrenzung für 2012 eingefügt. Dadurch werden dann die bestehenden Daten geprüft und neu verteilt:
ALTER PARTITION SCHEME ArchivierungsSchema
NEXT USED [Archiv2012]
GO
ALTER PARTITION FUNCTION ArchivierungsFunktion ()
SPLIT RANGE ('20120101');

Hier ist dann zu sehen das die neue Dateigruppe 2012 verwendet wird, und der Datensatz aus der Partition 2011 in die Partition 2012 verschoben worden ist.

Download:

• SQL Server Partitionierungs Script