{"id":47,"date":"2011-03-13T16:41:27","date_gmt":"2011-03-13T16:41:27","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=47"},"modified":"2011-04-03T16:48:40","modified_gmt":"2011-04-03T16:48:40","slug":"partitionierung-von-sql-server-tabellen","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=47","title":{"rendered":"Partitionierung von SQL Server Tabellen"},"content":{"rendered":"<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-48\" title=\"SQLServerPartitionierung\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung-e1301848983676.png\" alt=\"\" width=\"160\" height=\"93\" \/><\/a>In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft  SQL Server 2008R2 beschrieben.<br \/>\nDiese Funktion wurde mit der Version 2005  eingef\u00fchrt und erm\u00f6glicht die Verteilung von Daten einer Tabelle in verschiedene  Dateigruppen um diese bspw. Archivieren zu k\u00f6nnen indem die Daten anhand eines  Merkmals in verschiedene Dateigruppen unterteilt werden.<\/p>\n<p>Folgendes  Beispiel beschreibt die einer Buchungstabelle in der die Datens\u00e4tze anhand des  Buchungsdatums in einzelne Dateigruppen unterteilt werden k\u00f6nnen. So kann bei  anwachsen der Datenbank eine einzelne Datei, wo die Datens\u00e4tze nicht mehr  ben\u00f6tigt werden, gesichert und geleert werden. Dadurch wird die Datenbank wieder  kleiner und erh\u00f6ht gleichzeitig die Performance der Datenbank.<\/p>\n<p>Vorab  m\u00f6chte ich noch darauf hinweisen, dass die Partitionierungs-Funktion nur in der  Enterprise\/Datacenter Edition ver\u00fcgbar ist.<\/p>\n<p><strong>Ausgangssituation f\u00fcr  folgendes Beispiel:<\/strong><\/p>\n<ul>\n<li>Eine Buchungstabelle soll erstellt werden in der Daten aus einer anderen  Datenbank \u00fcbernommen werden<\/li>\n<li>Es werden Daten aus den Kalenderjahren 2010, 2009 und fr\u00fcher  \u00fcbernommen.<\/li>\n<li>Die Daten die fr\u00fcher als 2009 eingegeben wurden sind nur wenige<\/li>\n<li>Die Partitionierung soll erweitert werden k\u00f6nnen um zuk\u00fcnftige  Gesch\u00e4ftsjahre aufnehmen zu k\u00f6nnen<\/li>\n<\/ul>\n<p><strong>L\u00f6sungsm\u00f6glichkeit:<\/strong> Zuerst  werden 4 Dateigruppen in die Datenbank eingef\u00fcgt f\u00fcr die Gesch\u00e4ftsjahre 2011,  2010, 2009 und eine Datei f\u00fcr alle vorherigen Jahre: <code><br \/>\nALTER DATABASE  [Produktion] ADD FILEGROUP [Archiv2009]<br \/>\nALTER DATABASE [Produktion] ADD  FILEGROUP [Archiv2010]<br \/>\nALTER DATABASE [Produktion] ADD FILEGROUP  [Archiv2011]<br \/>\nALTER DATABASE [Produktion] ADD FILEGROUP [Archiv] <\/code>Nun  wird zu den 4 Dateigruppen jeweils eine Datendatei hinzugef\u00fcgt:<br \/>\n<code>ALTER  DATABASE [Produktion] ADD FILE ( NAME = N'2009',<br \/>\nFILENAME = N'C:\\Program  Files\\Microsoft SQL Server\\MSSQL10_50.VERTEILER\\MSSQL\\DATA\\2009.ndf' , SIZE  = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]<br \/>\nGO<br \/>\nALTER  DATABASE [Produktion] ADD FILE ( NAME = N'2010',<br \/>\nFILENAME = N'C:\\Program  Files\\Microsoft SQL Server\\MSSQL10_50.VERTEILER\\MSSQL\\DATA\\2010.ndf' ,SIZE  = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]<br \/>\nGO<br \/>\nALTER  DATABASE [Produktion] ADD FILE ( NAME = N'2011',<br \/>\nFILENAME = N'C:\\Program  Files\\Microsoft SQL Server\\MSSQL10_50.VERTEILER\\MSSQL\\DATA\\Aktuell.ndf' ,<br \/>\nSIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP  [Archiv2011]<br \/>\nGO<br \/>\nALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',<br \/>\nFILENAME = N'C:\\Program Files\\Microsoft SQL  Server\\MSSQL10_50.VERTEILER\\MSSQL\\DATA\\Archiv.ndf' ,<br \/>\nSIZE = 3072KB ,  FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]<br \/>\nGO <\/code><\/p>\n<p>Nun wird eine  Partitionierungs-Funktion erstellt, die auf ein DATETIME Feld pr\u00fcft und die  Daten entsprechend verteilt: <code><br \/>\nCREATE PARTITION FUNCTION  ArchivierungsFunktion (DATETIME) AS RANGE RIGHT<br \/>\nFOR VALUES (<br \/>\n'20090101'  -- Kleiner als 01.01.2009<br \/>\n, '20100101' -- Kleiner als 01.01.2010, gr\u00f6\u00dfer als  01.01.2009<br \/>\n, '20110101' -- Kleiner als 01.01.2011, gr\u00f6\u00dfer als 01.01.2010  geht<br \/>\n); <\/code><\/p>\n<p>Nun wird das Schema erstellt was die Funktion als Basis f\u00fcr  die Datenverteilung nutzt und diese dann in die Dateigruppen aufteilt:  <code><br \/>\nCREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION  ArchivierungsFunktion<br \/>\nTO ('Archiv', 'Archiv2009', 'Archiv2010',  'Archiv2011');<br \/>\n<\/code>Als n\u00e4chstes 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\u00e4rschl\u00fcssel aufgenommen werden:<br \/>\n<code>CREATE TABLE dbo.buchungen (<br \/>\n[uid] uniqueidentifier default NEWID() NOT  NULL,<br \/>\n[name] VARCHAR(50),<br \/>\n[datum] DATETIME DEFAULT GETDATE() NOT NULL,<br \/>\n[wert] MONEY) ON ArchivierungsSchema(datum);<br \/>\nGO<br \/>\nALTER TABLE  dbo.buchungen ADD CONSTRAINT<br \/>\nPK_buchungen PRIMARY KEY CLUSTERED<br \/>\n([uid],  [datum]);<\/code><\/p>\n<p><code> <\/code><\/p>\n<p><code>INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar  2008', '20080308', '100');<br \/>\nGO<br \/>\n-- Insert in Archiv2009<br \/>\nINSERT INTO  dbo.buchungen (name, datum, wert) VALUES ('M\u00e4rz 2009', '20090308',  '100');<br \/>\nGO<br \/>\n-- Insert in Archiv2010<br \/>\nINSERT INTO dbo.buchungen (name,  datum, wert) VALUES ('Februar 2010', '20100209', '100');<br \/>\nGO<br \/>\n-- Insert in  Archiv2011<br \/>\nINSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar  2011', '20110120', '100'); <\/code>Um zu pr\u00fcfen ob die eingetragenen Datens\u00e4tze  sich in den richtigen Partionen befinden, kann folgendes Statement verwendet  werden:<br \/>\n<code>SELECT<br \/>\nsys.filegroups.name 'Partitionsname' ,  sys.partitions.rows 'Zeilenanzahl'<br \/>\nFROM<br \/>\nsys.partitions INNER JOIN  sys.allocation_units<br \/>\nON sys.allocation_units.container_id =  sys.partitions.hobt_id<br \/>\nINNER JOIN sys.filegroups ON  sys.filegroups.data_space_id = sys.allocation_units.data_space_id<br \/>\nWHERE  sys.partitions.object_id = OBJECT_ID('dbo.buchungen') <\/code><\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung_Resultset01.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-50\" title=\"SQLServerPartitionierung_Resultset01\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung_Resultset01.png\" alt=\"\" width=\"197\" height=\"96\" \/><\/a>Hier sehen wir  dann die Datenverteilung wie die Datens\u00e4tze sich durch die Funktion in die  richtigen Dateien verteilt haben.<\/p>\n<p>&nbsp;<\/p>\n<p>Problematisch wird es dann  wenn ein neues Gesch\u00e4ftsjahr anbricht und Datens\u00e4tze f\u00fcr 2012 eingetragen  werden. Diese Datens\u00e4tze befinden sich dann in der Partition f\u00fcr 2011:  <code>INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2012',  '20120115', '300'); <\/code>Dieses Problem kann dann durch Erstellen einer neuen  Dateigruppe und dem Anpassen der Funktion gel\u00f6st werden: <code><br \/>\n-- Neue  Dateigruppe f\u00fcr 2012<br \/>\nALTER DATABASE [Produktion] ADD FILEGROUP  [Archiv2012]<br \/>\nGO<\/code><\/p>\n<p><code> <\/code><\/p>\n<p><code>-- Neue Datei f\u00fcr die Dateigruppe 2012<br \/>\nALTER  DATABASE [Produktion] ADD FILE ( NAME = N'2012', FILENAME = N'C:\\Program  Files\\Microsoft SQL Server\\MSSQL10_50.VERTEILER\\MSSQL\\DATA\\2012.ndf' ,<br \/>\nSIZE  = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012] <\/code><\/p>\n<p>Nun muss das  Schema angepasst werden um die neue Dateigruppen zu verwenden, diese wird dann  als Standard hinten dran gestellt.<br \/>\nWeiterhin wird durch die SPLIT() Funktion  eine neue Begrenzung f\u00fcr 2012 eingef\u00fcgt. Dadurch werden dann die bestehenden  Daten gepr\u00fcft und neu verteilt: <code><br \/>\nALTER PARTITION SCHEME  ArchivierungsSchema<br \/>\nNEXT USED [Archiv2012]<br \/>\nGO<br \/>\nALTER PARTITION FUNCTION  ArchivierungsFunktion ()<br \/>\nSPLIT RANGE ('20120101'); <\/code><\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung_Resultset02.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-49\" title=\"SQLServerPartitionierung_Resultset02\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2011\/04\/SQLServerPartitionierung_Resultset02.png\" alt=\"\" width=\"198\" height=\"114\" \/><\/a>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.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Download:<\/strong><\/p>\n<ul>\n<li><a href=\"download\/SQLServer_Partitionierung.pdf\" target=\"_blank\">SQL Server  Partitionierungs Script<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft SQL Server 2008R2 beschrieben. Diese Funktion wurde mit der Version 2005 eingef\u00fchrt und erm\u00f6glicht die Verteilung von Daten einer Tabelle in verschiedene Dateigruppen um diese bspw. Archivieren zu &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=47\">Continue reading <span class=\"meta-nav\">&rarr;<\/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":[9,8,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/47"}],"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=47"}],"version-history":[{"count":4,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":53,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/47\/revisions\/53"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}