[Kommunikation] SQL Server Zusammenfassung partitionierter Tabellen

Offizielle Dokumentation

Hochrangige Schritte zur Erstellung einer Partitionstabelle

1. Erstelle eine Dateigruppe (ähnlich wie Oracle-Tabellenraum), natürlich kannst du sie nicht erstellen, du kannst alle Partitionen in eine Dateigruppe packen

2. Einrichtung einer Partitionsfunktion und Verteilung der Daten entsprechend welchem Bereich

3. Richte ein Partitionsschema ein, verknüpfe die Partitionsfunktion und auch die Dateigruppe. Die Partitionsfunktion teilt die Daten in mehrere Bereiche, du musst mehrere Dateigruppen zuordnen, natürlich kannst du diese Partitionsbereiche auch in dieselbe Dateigruppe einordnen

4. Erstelle eine Tabelle und verknüpfe das Zonierungsschema

Ein Bug entdeckt

Wenn du direkt mit der rechten Maustaste auf die Tabelle klickst, um die Tabellenstruktur zu exportieren, kannst du die Partitionsinformationen nicht exportieren, und du kannst die Partitionsinformationen der Tabelle nur exportieren, indem du mit der rechten Maustaste auf das Datenbank-Task-Generier-Skript klickst

Einige Schlussfolgerungen aus der Partitionstabelle:

1. Partitionierte Felder müssen nicht unbedingt indexiert werden

2. Partitionierte Felder können als geclusterte oder ohne Cluster erstellte Indizes erstellt werden

3. Partitionierte Felder, egal ob Cluster-Indizes oder Nicht-Cluster-Indizes, werden zu Cluster-Indizes ohne zugehörige Partitionierungsschemata neu aufgebaut, und die partitionierte Tabelle wird zu einer nicht-partitionierten Tabelle

4. Wandeln Sie eine gewöhnliche Tabelle in eine partitionierte Tabelle um, erstellen Sie einfach einen Cluster-Index in der Tabelle und verwenden Sie das Partitionierungsschema auf dem Cluster-Index. Wenn zum Beispiel Feld 1 einer nicht partitionierten Tabelle einen Clusterindex erzeugt und mit einem Partitionierungsschema verknüpft ist, wird die Tabelle in eine partitionierte Tabelle umgewandelt, und das partitionierte Feld ist Feld 1.

5. Konvertiere die partitionierte Tabelle in eine normale Tabelle; wenn es ein Partitionsfeld mit einem Index gibt, baue das partitionierte Feld als clusternden Index neu auf und verknüpfe es nicht mit dem Partitionsschema. Wenn das Partitionsfeld keinen Index hat, kann das Partitionsfeld einen neuen Cluster-Index erstellen und nicht mit dem Partitionsschema assoziiert werden

6. Die Umwandlung einer normalen Tabelle in eine Partitionstabelle oder die Umwandlung einer Partitionstabelle in eine gewöhnliche Tabelle kann nur durch die Verwendung von Cluster-Indizes erreicht werden, da bei Cluster-Indizes Index-Organisationstabellen Indexe verwendet werden und die Neuverteilung der Tabellen durch die Rekonstruktion der Cluster-Indizes realisiert wird. Wenn die gewöhnliche Tabelle zu einer partitionierten Tabelle wird, rekonstruiere das Partitionsfeld zu einem clusterierten Index und verknüpfe es mit dem Partitionsschema, und wenn die Partitionstabelle zu einer normalen Tabelle wird, rekonstruiere das Partitionsfeld zu einem clusterierten Index, ohne das Partitionsschema zuzuordnen.

7. Um eine Partitionstabelle zu erstellen, muss die Eindeutigkeitsbedingung eine Spaltspalte enthalten

8. Beim Erstellen eines Partitionsschemas ist es notwendig sicherzustellen, dass die Anzahl der Dateigruppen mit dem Partitionsbereich der Partitionsfunktion übereinstimmt, und es ist egal, ob der Dateigruppenname dupliziert wird.

9. Partitionierungsfunktionen und Partitionierungsschemata befinden sich in einer Datenbank und zeigen nicht die gesamte Instanz

10. Die Partitionstabelle ist zu groß, um viel Speicherplatz zu beanspruchen, und die Größe ändert sich nach dem Löschen einiger Felder immer noch nicht

Schritte zur Erstellung einer Partitionstabelle

1.1. Beispiel für die Einrichtung einer Dateigruppe

Alter Datenbanktest1 Dateigruppe Teil 1 hinzufügen;

alter database test1 Dateigruppe part1000 hinzufügen;

alter database test1 add filegroup part2000;

alter database test1 Dateigruppe part3000 hinzufügen;

alter database test1 add filegroup part4000;

1.2. Ein Beispiel einer Datei erstellen und eine Dateigruppe zuordnen

ALTER DATABASE TEST1 DATEI(NAME = test1part1,DATEINAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ZUR FILEGROUP part1 hinzufügen;

ALTER DATABASE TEST1 DATEI(NAME = test1part1000,DATEINAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ZUR FILEGROUP part1000 hinzufügen;

ALTER DATABASE test1 DATEINAME = test1part2000, DATEINAME = 'G:\test1part2000.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) ZUR FILEGROUP part2000 hinzufügen;

ALTER DATABASE TEST1 DATEI(NAME = test1part3000,DATEINAME = 'G:\test1part3000.ndf', SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ZUR FILEGROUP part3000 hinzufügen;

ALTER DATABASE TEST1 DATEI(NAME = test1part4000,DATEINAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ZUR FILEGROUP part4000 hinzufügen;

2. Ein Beispiel für die Einrichtung einer Partitionsfunktion: Die Partitionfunktion heißt partfun1

CREATE PARTITION FUNCTION partfun1 (int)

ALS LINKSBEREICH FÜR WERTE ('1000', '2000', '3000', '4000')

--VALUES ('1000', '2000', '3000', '4000') zeigt an, dass die Tabelle in 5 Zonen unterteilt wird, die entsprechend der Größe der Werte in den Tabellenfeldern unterteilt sind; die fünf Zonen sind die kleinsten – 1000, 1000-2000, 2000-3000, 3000-4000, 4000-größte

3. Ein Beispiel für das Einrichten eines Schemas: Assoziiert die Partitionsfunktion partfun1 und assoziiert die Dateigruppe

CREATE PARTITION SCHEME partschema1

ALS PARTITION partfun1

TO (Teil1, Teil1000, Teil2000, Teil3000, Teil4000);

--Aufgebaut auf mehreren Dateigruppen von part1, part1000, part2000, part3000, part4000

CREATE PARTITION SCHEME partschema2

ALS PARTITION partfun1

TO (TEIL 1,[PRIMÄR],[PRIMÄR],[PRIMÄR],[PRIMÄR],[PRIMÄR]);

--Basierend auf Teil 1, [PRIMARY] Dateigruppe, ist es kein Problem, Teil 1 durch [PRIMARY] zu ersetzen, sodass es ähnlich ist wie der Aufbau auf der [PRIMARY]-Dateigruppe

CREATE PARTITION SCHEME partschema3

ALS PARTITION partfun1

ALL TO (Teil 1);

--alle auf der Part1-Dateigruppe aufgebaut

CREATE PARTITION SCHEME partschema4

ALS PARTITION partfun1

ALLE ZU ([PRIMÄR]);

--alle auf der [PRIMARY]-Dateigruppe aufgebaut

4. Beispiel für die Einrichtung einer Partitionstabelle

TABELLE ERSTELLEN: PARTTABLE1(

[ID] [int] NICHT NULL,

[IDText] [nvarchar] (max) NULL

[Date] [Datezeit] NULL)

ON [partschema1](ID);

Fügen Sie in parttable1 die Werte ein (1,'1', getdate()-4);

Werte in parttable1 einfügen (1001,'1001', getdate()-3);

Werte in parttable1 einfügen (2001,'2001', getdate()-2);

Werte in parttable1 einfügen (3001,'3001', getdate()-1);

Fügen Sie Werte in parttable1 ein (4001,'4001', getdate());

5. Verifizieren Sie die Daten der Partitionstabelle

SELECT * AUS der Teiltabelle1;

--Gibt alle Zeilen der Partitionstabelle zurück

WÄHLEN SIE UNTERSCHIEDLICHE $PARTITION. [partfun1] (4) AUS der Teiltabelle1;

--Rückgaben, die die Zeile mit dem ID-Feldwert 4 aufteilen, gehören zu

WÄHLE * AUS der Teiltabelle1 aus, wo $PARTITION. [partfun1] (ID)=2

--Gibt alle Zeilen in der zweiten Partition zurück, die ID ist die Partitionsfeld-ID

Hinweis: Du kannst SELECT * FROM parttable1 nicht verwenden, wo $PARTITION. [partfun1] (ID)=2 hat ein Ergebnis, das bedeutet, dass es sich um eine partitionierte Tabelle handelt, und der letzte Test 7 dieser Arbeit ist eine nicht partitionierte Tabelle, aber SELECT * FROM parttable1, wobei $PARTITION. [partfun1] (ID)=2 hat immer noch ein Ergebnis

Neue Partitionen

1. Geben Sie eine Dateigruppe an, die für das Partitionsschema verwendet werden kann (fügen Sie eine Dateigruppe für das Partitionsschema hinzu).

2. Die Partitionsfunktion modifizieren (den Datenbereich der Partitionsfunktion hinzufügen)

ALTER PARTITION SCHEME partschema1 NÄCHST VERWENDET [PRIMÄR]

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ('4500')

Wählen Sie p.partition_number,p.rows aus sys.indexes i inner join sys.partitions p auf p.object_id = i.object_id und i.object_id = object_id('parttable2') durch 1 aus

--Die erste Anweisung, wenn das Partitionsschema ALL TO ([PRIMARY]) verwendet, muss diese Anweisung nicht ausgeführt werden

--Eine neue Partition wird der zweiten Anweisung hinzugefügt, der Bereich beträgt 4000–4500

--Die dritte Aussage überprüft, ob die neue Trennung existiert und ob es Zeilen gibt

Partition löschen/zusammenführen

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')

Die Partition von 1000-2000 wurde gelöscht und zu 1000-3000 zusammengeführt

--Kann ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME wie Oracle nicht ausführen;

Lösche die Partitionstabelle und die entsprechende Dateigruppe

Die Reihenfolge der Löschung lautet: die Partitionstabelle löschen, das Partitionsschema löschen, die Partitionsfunktion löschen und schließlich die Dateigruppe löschen, und die entsprechende Datei wird gelöscht, nachdem die Dateigruppe gelöscht wurde

Beispiel für die Umwandlung einer partitionierten Tabelle in eine normale Tabelle und einer normalen Tabelle in eine partitionierte Tabelle

DROP TABLE PARTTABLE1;

TABELLE ERSTELLEN: PARTTABLE1(

[Id] [int] IDENTITY(1,1) NICHT NULL,

[Name] [Varchar] (16) NICHT NULL,

[Id2] [int] NICHT NULL

) ON partschema1(Id2);

Werte in parttable1 einfügen ('1',1);

Werte in parttable1 einfügen ('1001', 1001);

Werte in parttable1 einfügen ('2001', 2001);

in die Teiltabelle1-Werte einfügen ('3001', 3001);

Werte in Teiltabelle 1 einfügen ('4001', 4001);

1. Die auf der Partitionstabelle erstellte eindeutige Constraint muss eine Spaltspalte enthalten.

VARIABLE TABLE PARTTABLE1 FÜGEN CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC) HINZU

Die Fehlerspalte 'Id2' ist die Spaltungsspalte des Index 'PK_prattable1_id'. Partitionsspalten für einen eindeutigen Index müssen eine Teilmenge des Indexschlüssels sein.

2. Erstelle einen neuen Cluster-Index in der Spaltungsspalte id2, und parttable1 ist weiterhin eine partitionierte Tabelle

Clustered Index CI_prattable1_id2 auf parttable1(id2) erstellen;

3. Die Spaltspalte id2 erzeugt einen nicht-clusterten Index, und parttable1 ist weiterhin eine partitionierte Tabelle

Drop Index CI_prattable1_id2 auf parttable1;

Nicht-geclusterte Index-NCI_prattable1_id2 auf parttable1(id2) erstellen;

4. Die nicht-partitionierte Spalten-id-Spalte erzeugt einen Cluster-Index, parttable1 oder eine partitionierte Tabelle, was darauf hinweist, dass die nicht-partitionierte Spalte eine Cluster-Index-Spalte sein kann

Clustered Index CI_prattable1_id auf parttable1(id) erstellen;

5. Partitionsspalte ID2 wird zu einem nicht-clusterten Index umgebaut und verwendet kein Partitionierungsschema, parttable1 ist weiterhin eine Partitionstabelle

Erstelle einen nicht-clusterten Index NCI_prattable1_id2 auf parttable1(id2) MIT (DROP_EXISTING = ON) AUF [PRIMÄR];

6. Die Partitionsspalte id2 wird ohne die ON-Bedingung in einen Clusterindex rekonstruiert, und parttable1 ist weiterhin eine Partitionstabelle

Drop Index CI_prattable1_id auf Teiltabelle1;

Drop Index NCI_prattable1_id2 auf parttable1;

Clustered Index CI_prattable1_id2 auf parttable1(id2) erstellen;

Erzeugen Sie einen Cluster-Index CI_prattable1_id2 auf parttable1(id2) MIT (DROP_EXISTING = ON);

7. Die aufgeteilte Spalte id2 wird zu einem Cluster-Index mit ON-Bedingung umgebaut, jedoch ohne das Partitionierungsschema, und parttable1 wird zu einer nicht-partitionierten Tabelle

Erzeugen Sie einen Cluster-Index CI_prattable1_id2 auf parttable1(id2) MIT (DROP_EXISTING = AN) AUF [PRIMÄR];

8. Die Partitionsspalte id2 wird zu einem Cluster-Index neu aufgebaut und mit dem Partitionierungsschema wird parttable1 zu einer partitionierten Tabelle

einen geclusterten Index CI_prattable1_id2 auf parttable1(id2) MIT (DROP_EXISTING = ON) auf partschema1(Id2) erstellen;

9. Nach dem Löschen des clusterierten Index in 8 oben ist parttable1 immer noch eine partitionierte Tabelle

Drop Index CI_prattable1_id2 auf parttable1;

10. Die Spaltspalte ID2 wird als Cluster-Index erstellt und verwendet das Partitionierungsschema nicht, und parttable1 wird zu einer nicht-partitionierten Tabelle

einen clusterten Index CI_prattable1_id2 auf parttable1(id2) AUF [PRIMÄR];

11. Nach dem Löschen des Cluster-Index der oben genannten 10 ist parttable1 weiterhin eine nicht partitionierte Tabelle

Drop Index CI_prattable1_id2 auf parttable1;

12. Die Spaltspalte id2 wird neu als nicht-clusterter Index erstellt, obwohl das Partitionierungsschema verwendet wird, ist es weiterhin eine nicht-partitionierte Tabelle

Nicht-geclusterte Index-NCI_prattable1_id2 auf parttable1(id2) auf partschema1(Id2) erstellen;

Wenn die partitionierte Tabelle in eine normale Tabelle umgewandelt wird, wird die Primärschlüssel-Constraint gelöscht und der Cluster-Index wird für die Felder des ursprünglichen Primärschlüssels neu aufgebaut oder zum Primärschlüssel umgewandelt, aber er steht nicht im Zusammenhang mit dem Partitionierungsschema

ALTER TABLE Table_name DROP-CONSTRAINT PK_NAME MIT (>

ERSTELLE EINEN CLUSTER-INDEX PK_NAME AUF Table_name(Spalte) MIT (AUF [PRIMÄR];

oder

ALTER TABLE Table_name NEBENBEDINGUNGEN HINZUFÜGEN PK_NAME PRIMÄRSCHLÜSSEL CLUSTERED (spalte) MIT (AUF [PRIMÄR];

Wenn die normale Tabelle in eine partitionierte Tabelle umgewandelt wird, wird der ursprüngliche Primärschlüssel behalten, die Primärschlüssel-Einschränkung gelöscht, dann wird der Primärschlüssel erstellt, ohne ihn als aggregierten Index zu setzen, und dann wird ein neuer aggregierter Index erstellt, und das Partitionierungsschema wird im aggregierten Index verwendet

ALTER TABLE Table_name DROP-CONSTRAINT PK_NAME MIT (>

ALTER TABLE Table_name NEBENBEDINGUNGEN HINZUFÜGEN PK_NAME PRIMÄRSCHLÜSSEL NICHT-CLUSTERED(spalte) MIT (AUF [PRIMÄR];

--Einen Primärschlüssel erstellen, aber nicht als geclusterter Index gesetzt werden

CLUSTERED INDEX ERSTELLEN index_name AUF Table_name(Spalte) IM Partition Scheme (Partition Field)

--Erstellen Sie einen neuen Cluster-Index, in dem ein Partitionierungsschema verwendet wird

Abfrage der Gesamtzahl der Zeilen und der Größe einer partitionierten Tabelle, zum Beispiel ist die Tabelle CRM. EmailLog

Executive sp_spaceused 'CRM. EmailLog';

Abfrage der Informationen einer partitionierten Tabelle, wie viele Zeilen jede Partition hat, zum Beispiel ist die Tabelle CRM. EmailLog

Wähle konvertiert (Varchar(50)) ps.name

) als partition_scheme,

p.partition_number,

Konvertier(varchar(10), ds2.name

) als Dateigruppe,

convert(varchar(19), isnull(v.value, ''), 120) als range_boundary,

str(p.rows, 9) als Reihen

aus sys.indexes i

sys.partition_schemes PS auf i.data_space_id = ps.data_space_id beitreten

Tritt sys.destination_data_spaces DDS bei

auf ps.data_space_id = dds.partition_scheme_id

Tritt sys.data_spaces DS2 bei dds.data_space_id = ds2.data_space_id

join sys.partitions p auf dds.destination_id = p.partition_number

und p.object_id = i.object_id und p.index_id = i.index_id

Tritt sys.partition_functions PF bei ps.function_id = pf.function_id bei

LINKS JOINE SYS. Partition_Range_values v auf pf.function_id = v.function_id

und v.boundary_id = p.partition_number - pf.boundary_value_on_right

WOBEI i.object_id = object_id('crm. EmailLog')

und i.index_id in (0, 1)

Reihenfolge nach p.partition_number

Abfrage-Partitionsfunktionen

Wählen Sie * aus sys.partition_functions

Betrachten Sie die Partitionsarchitektur

Wählen Sie * aus sys.partition_schemes