{"id":129,"date":"2011-06-25T16:34:00","date_gmt":"2011-06-25T14:34:00","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=129"},"modified":"2011-06-26T21:17:28","modified_gmt":"2011-06-26T19:17:28","slug":"patritionierung-von-bestehenden-tabellen","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=129","title":{"rendered":"Partitionierung von bestehenden Tabellen"},"content":{"rendered":"

\"\"<\/a>In dem Artikel Partitionierung von SQL Server Tabellen<\/a> habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis f\u00fchrt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle doch gr\u00f6\u00dfer als erwartet wird. Daher stellt sich die Frage: Wie partitioniere ich eine bestehende Tabelle?<\/p>\n

Gr\u00fcnds\u00e4tzlich k\u00f6nnte man die bestehende Tabelle auslagern, neu erstellen und dann wieder alle Daten reinkopieren. Das ist u.U nicht immer ganz einfach, besonders dann, wenn die Tabelle referentielle Integrit\u00e4ten besitzt (Fremdschl\u00fcssel, bzw. „Verlinkungen in andere Tabellen“) und man durch ein L\u00f6schen und Neuerstellen doch mehr Arbeit produziert, als notwendig.<\/p>\n

Zuerst erstellen wir uns die Umgebung (auf Basis des Beispiels von dem Artikel: Partitionierung von SQL Server Tabellen<\/a>):
\nCREATE TABLE dbo.buchungen (
\n[uid] INT NOT NULL PRIMARY KEY,
\n[name] VARCHAR(50),
\n[datum] DATETIME DEFAULT GETDATE() NOT NULL,
\n[wert] MONEY);
\nGO<\/code><\/p>\n

CREATE TABLE dbo.buchungen_Log (
\n[uid] INT NOT NULL PRIMARY KEY,
\n[buchungen_uid] INT NOT NULL);
\nGO<\/code><\/p>\n

ALTER TABLE dbo.buchungen_Log ADD CONSTRAINT FK_buchungen_Log_buchungen FOREIGN KEY
\n(buchungen_uid) REFERENCES dbo.buchungen
\n(uid)
\nGO<\/code><\/code><\/p>\n

<\/code><\/p>\n

INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (1, 'Januar 2008', '20080308', '100');
\nINSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (2, 'M\u00e4rz 2009', '20090308', '100');
\nINSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (3, 'Februar 2010', '20100209', '100');
\nINSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (4, 'Januar 2011', '20110120', '100');
\nINSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (5, 'Januar 2012', '20120115', '300');
\nGO
\nINSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (1, 1);
\nINSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (2, 2);
\nINSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (3, 3);
\nINSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (4, 4);
\nINSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (5, 5);
\nGO<\/code><\/p>\n

Nun haben wir die Tabelle „Buchungen“ die die zu partitionierenden Daten enth\u00e4lt und auch den Fremschl\u00fcssel f\u00fcr die Tabelle „buchungen_log“ stellt.
\nAnsich geht man nun wir bei der Partitionierung einer neuen Tabelle vor, indem man zuerst die Dateigruppen und die jeweiligen Dateien erstellt:<\/p>\n

ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
\nGO<\/code><\/p>\n

<\/code><\/p>\n

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2009.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2010.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2011.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2012.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\Archiv.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
\nGO <\/code><\/p>\n

Nun ben\u00f6tigt man die Partitionierungs-Funktion und das Schema f\u00fcr die Datenverteilung:
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
\nGO<\/code><\/p>\n

<\/code><\/p>\n

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2009.ndf' ,
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2010.ndf' ,
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2011.ndf' ,
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\2012.ndf' ,
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
\nGO
\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQL1\\MSSQL\\DATA\\Archiv.ndf',
\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
\nGO <\/code><\/p>\n

Nun wird die Partionierungsfunktion wie auch das Schema erstellt, um die Datentrennung zu regeln:
\nCREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
\nFOR VALUES (
\n'20090101' -- Kleiner als 01.01.2009
\n, '20100101' -- Kleiner als 01.01.2010, gr\u00f6\u00dfer als 01.01.2009
\n, '20110101' -- Kleiner als 01.01.2011, gr\u00f6\u00dfer als 01.01.2010
\n, '20120101' -- Kleiner als 01.01.2012, gr\u00f6\u00dfer als 01.01.2011
\n); <\/code><\/p>\n

<\/code><\/p>\n

CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
\nTO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011', 'Archiv2012');<\/code><\/p>\n

Nun ben\u00f6tigt man einen Prim\u00e4r Schl\u00fcssel, wo jedoch auch das Datums Feld (Kennzeichen f\u00fcr die Partionierung) enthalten ist. Daf\u00fcr muss jedoch der Schl\u00fcssel erstmal entfernt werden, wie auch die Fremschl\u00fcssel Verbindung:
\nALTER TABLE [dbo].[buchungen_Log] DROP CONSTRAINT [FK_buchungen_Log_buchungen]
\nGO
\nALTER TABLE [dbo].[buchungen] DROP CONSTRAINT [PK__buchunge__DD7012647F60ED59]
\nGO<\/code><\/p>\n

Nun kann der Prim\u00e4rschl\u00fcssel erstellt werden, der dann an die Partionierungsfunktion geh\u00e4ngt wird (dieser muss ein Gruppierter Index sein, eine HEAP Tabelle (ohne Gruppierten Index) wird hier nicht unterst\u00fctzt, da die Daten sonst nicht verschoben werden k\u00f6nnen):
\nALTER TABLE dbo.buchungen ADD CONSTRAINT
\nPK_buchungen PRIMARY KEY CLUSTERED
\n([uid], [datum]) on ArchivierungsSchema(datum);<\/code><\/p>\n

Kontrolliert werden kann dies folgenderma\u00dfen:
\nSELECT
\nsys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
\nFROM
\nsys.partitions INNER JOIN sys.allocation_units
\nON sys.allocation_units.container_id = sys.partitions.hobt_id
\nINNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
\nWHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen') <\/code><\/p>\n

Das wars.<\/p>\n","protected":false},"excerpt":{"rendered":"

In dem Artikel Partitionierung von SQL Server Tabellen habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis f\u00fchrt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[18,9,10,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/129"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=129"}],"version-history":[{"count":4,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/129\/revisions"}],"predecessor-version":[{"id":143,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/129\/revisions\/143"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}