[Comunicare] Rezumat Sql Server al tabelelor partiționate

Documentație oficială

Pași de nivel înalt pentru crearea unui tabel de partiții

1. Creează un grup de fișiere (similar cu spațiul de tabelă Oracle), desigur, nu poți să-l creezi, poți pune toate partițiile într-un singur grup de fișiere

2. Stabilirea unei funcții de partiție și distribuirea datelor în funcție de intervalul

3. Stabilește o schemă de partiții, asociază funcția de partiție și asociază și grupul de fișiere, funcția de partiție împarte datele în mai multe intervale, trebuie să asociezi mai multe grupuri de fișiere, desigur, poți pune aceste intervale de partiții în același grup de fișiere

4. Creează un tabel și asociază schema de zonare

Am întâlnit o eroare

Când dai click dreapta direct pe tabel pentru a exporta structura tabelului, nu poți exporta informațiile de partiție și poți exporta informațiile de partiție ale tabelului doar făcând clic dreapta pe scriptul database-task-generate

Câteva concluzii din tabelul de partiții:

1. Câmpurile partiționate nu trebuie neapărat indexate

2. Câmpurile partajate pot fi create ca indici clusterați sau indexuri noclustered

3. Câmpurile partiționate, fie că sunt indexuri clusterizate sau indexuri noclusterate, sunt reconstruite în indici clusterizate fără scheme de partiționare asociate, iar tabelul partiționat devine un tabel nepartiționat

4. Convertiți un tabel obișnuit într-un tabel partiționat, pur și simplu creați un index clusterizat în tabel și folosiți schema de partiționare pe indexul clusterizat. De exemplu, când câmpul 1 al unui tabel nepartiționat creează un index clusterizat și este asociat cu o schemă de partiționare, tabelul este convertit într-un tabel partiționat, iar câmpul partiționat este câmpul 1.

5. Convertiți tabelul partiționat într-un tabel normal, dacă există un câmp de partiție cu un index, reconstruiți câmpul partiționat ca un index clusterizat și nu îl asociați cu schema de partiție; dacă câmpul de partiție nu are un index, câmpul de partiție poate crea un nou index clusterizat și să nu se asocieze cu schema de partiție

6. Schimbarea unui tabel normal într-un tabel de partiții sau schimbarea unui tabel de partiții într-un tabel obișnuit poate fi realizată doar prin utilizarea unor indici clusterați, deoarece cu indici clusterați există tabele de organizare a indicilor, iar redistribuirea tabelelor se realizează prin reconstrucția indicilor clusterați. Dacă tabelul obișnuit devine un tabel partiționat, reconstruiește câmpul de partiție într-un index clusterizat și îl asociezi cu schema de partiții, iar dacă tabelul de partiție devine un tabel normal, reconstruiești câmpul de partiție într-un index clusterizat fără a asocia schema de partiție.

7. Pentru a crea un tabel de partiții, constrângerea de unicitate trebuie să includă o coloană de partiție

8. Când se creează o schemă de partiții, este necesar să se asigure că numărul grupurilor de fișiere corespunde segmentului de interval de partiții al funcției de partiție și nu contează dacă numele grupului de fișiere este duplicat.

9. Funcțiile de partiționare și schemele de partiționare sunt într-o bază de date, nu se confruntă cu întreaga instanță

10. Tabelul de partiții este prea mare pentru a ocupa mult spațiu pe disc, iar dimensiunea tot nu se schimbă după ștergerea unor câmpuri

Pași pentru a crea un tabel de partiții

1.1. Exemplu de înființare a unui grup de fișiere

Alter database test1 adaugă grupul de fișiere partea 1;

alter baza de date test1 adaugă grupul de fișiere partea 1000;

alter database test1 add filegroup part2000;

alter database test1 add filegroup part3000;

Alter baza de date test1 adaugă grupul de fișiere partea 4000;

1.2. Stabilirea unui exemplu de fișier și asocierea unui grup de fișiere

ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) la filegroup partea 1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) la filegroup part1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) la filegroup part2000;

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) la filegroup PART3000;

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) la filegroup PART4000;

2. Un exemplu de stabilire a unei funcții de partiție, funcția de partiție se numește partfun1

CREATE PARTITION FUNCTION partfun1 (int)

CA INTERVAL RĂMAS PENTRU VALORI ('1000', '2000', '3000', '4000')

--VALORILE ('1000', '2000', '3000', '4000') indică faptul că tabelul va fi împărțit în 5 zone, care sunt împărțite în funcție de dimensiunea valorilor din câmpurile tabelului, cele cinci zone fiind cele mai mici - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-cele mai mari

3. Un exemplu de stabilire a unei scheme, asocierea funcției de partiție partfun1 și asocierea grupului de fișiere

CREATE PARTITION SCHEME partschema1

CA PARTIȚIE PARTFUN1

TO (partea 1, parte1000, parte2000, parte3000, parte4000);

--Construit pe mai multe grupuri de fișiere: partea 1, partea 1000, parte2000, partea 3000, partea 4000

CREATE PARTITION SCHEME partschema2

CA PARTIȚIE PARTFUN1

TO (partea 1,[PRIMARY],[PRIMARY],[PRIMARY]);

--Construit pe grupul de fișiere [PRIMARY] parte 1, nu este o problemă să înlocuiești partea 1 cu [PRIMARY], astfel încât să fie similar cu construirea pe grupul de fișiere [PRIMARY]

CREEAZĂ SCHEMA DE PARTIȚIE partschema3

CA PARTIȚIE PARTFUN1

ALL TO (partea 1);

--toate construite pe grupul de fișiere parte 1

CREATE PARTITION SCHEME partschema4

CA PARTIȚIE PARTFUN1

TOȚI CĂTRE ([PRIMAR]);

--toate construite pe grupul de fișiere [PRIMARY]

4. Exemplu de stabilire a unui tabel de partiții

CREATE TABLE PARTTABLE1(

[ID] [int] NU NUL,

[IDText] [nvarchar] (max) ZERO

[Data] [ora întâlnirii] NULL)

ON [partschema1](ID);

inserați în parttable1 valorile (1,'1', getdate()-4);

inserați în Parttable1 valorile (1001,'1001', getdate()-3);

inserați în părțile table1 valori (2001, '2001', getdate()-2);

inserați în parttable1 valorile (3001,'3001', getdate()-1);

inserați în părțile tabelă1 valori (4001,'4001', getdate());

5. Verificarea datelor tabelului de partiții

SELECT * DIN parttable1;

--Returnează toate rândurile tabelului de partiții

SELECT $PARTITION distincte. [partfun1] (4) DIN tabelul părții1;

--Returnări care împart rândul cu valoarea câmpului ID 4 la care aparține

SELECTEAZĂ * DIN PARTTABLE1 unde $PARTITION. [partfun1] (ID)=2

--Returnează toate rândurile din a doua partiție, ID-ul este ID-ul câmpului de partiție

Notă: Nu poți folosi SELECT * FROM parttable1 unde $PARTITION. [partfun1] (ID)=2 are un rezultat, înseamnă că este un tabel partiționat, iar ultimul test 7 al acestei lucrări este un tabel nepartiționat, dar SELECT * FROM parttable1 unde $PARTITION. [partfun1] (ID)=2 are totuși un rezultat

Noi partiții

1. Specifică un grup de fișiere care poate fi folosit pentru schema de partiții (adaugă un grup de fișiere pentru schema de partiții).

2. Modifică funcția de partiție (adaugă intervalul de date al funcției de partiție)

ALTER PARTITION SCHEME parteschema1 URMĂTOAREA UTILIZARE [PRIMARY]

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

selectează p.partition_number,p.rows din sys.indexes i inner join sys.partitions p on p.object_id = i.object_id și i.object_id = object_id('parttable2') ordine cu 1

--Prima instrucțiune, dacă schema de partiție folosește ALL TO ([PRIMARY]), această afirmație nu trebuie executată

--Se adaugă o nouă partiție la a doua afirmație, intervalul este 4000-4500

--A treia afirmație verifică dacă noua partiție există și dacă există rânduri

Șterge\Îmbină partiția

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

Partiția 1000-2000 a fost ștearsă și comasată în 1000-3000

--Imposibil de executat ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME ca Oracle;

Șterge tabelul de partiții și grupul de fișiere corespunzător

Ordinea ștergerii este: șterge tabelul de partiții, șterge schema de partiții, șterge funcția de partiție și, în final, șterge grupul de fișiere, iar fișierul corespunzător este șters după ce grupul de fișiere este șters

Exemplu de conversie a unui tabel partiționat într-un tabel normal și al unui tabel normal într-un tabel partiționat

TABELĂ DE TIP DROP TABLE1;

CREATE TABLE PARTTABLE1(

[Id] [int] IDENTITATE(1,1) NU NULĂ,

[Nume] [varchar] (16) NU NUL,

[Id2] [int] NU NUL

) ON partschema1(Id2);

inserați în parttable1 valorile ('1',1);

inserați în părțile tabelă1 valori ('1001', 1001);

inserați în valorile parttable1 ('2001', 2001);

inserați în valorile parttable1 ('3001', 3001);

inserați în părțile tabelă1 valori ('4001', 4001);

1. Constrângerea unică creată pe tabelul de partiții trebuie să conțină o coloană de partiție.

ALTER TABLE PARTTABLE1 ADAUGĂ CONSTRÂNGERE PK_prattable1_id CLUSTER DE CHEI PRIMARE ([ID] ASC)

Coloana de eroare 'Id2' este coloana de partiționare a indexului 'PK_prattable1_id'. Coloanele de partiție pentru un index unic trebuie să fie un subset al cheii index.

2. Creează un nou index clusterizat în coloana partiției id2, iar parttable1 rămâne un tabel partiționat

creează CI_prattable1_id2 de index clusterizat pe parttable1(id2);

3. Coloana de partiție id2 creează un index neclusterat, iar parttable1 rămâne un tabel partiționat

drop index CI_prattable1_id2 pe parttable1;

creează NCI_prattable1_id2 index neclusterat pe parttable1(id2);

4. Coloana de identificare a columnei nepartiționată creează un index clusterizat, un parttable1 sau un tabel partiționat, indicând că coloana nepartiționată poate fi o coloană de index cluster

creează CI_prattable1_id de index clusterizat pe parttable1(id);

5. Coloana de partiție ID2 este reconstruită într-un index neclusterizat și nu folosește o schemă de partiționare, parttable1 rămâne un tabel de partiții

Creează NCI_prattable1_id2 de index neclusterat pe parttable1(id2) CU (DROP_EXISTING = PORNIT) PE [PRIMAR];

6. Coloana de partiție id2 este reconstruită într-un index clusterizat fără condiția ON, iar parttable1 rămâne un tabel de partiții

drop index CI_prattable1_id pe parttable1;

drop index NCI_prattable1_id2 pe parttable1;

creează CI_prattable1_id2 de index clusterizat pe parttable1(id2);

creează un index clusterizat CI_prattable1_id2 pe parttable1(id2) CU (DROP_EXISTING = ON);

7. Coloana partiționată id2 este reconstruită într-un index clusterizat cu condiția ON, dar fără a folosi schema de partiționare, iar parttable1 devine un tabel nepartiționat

creează un index CI_prattable1_id2 clusterizat pe parttable1(id2) CU (DROP_EXISTING = PORNIT) PE [PRIMAR];

8. Coloana de partiție id2 este reconstruită într-un index clusterizat și, folosind schema de partiționare, parttable1 devine un tabel partiționat

creează CI_prattable1_id2 indexate clusterizate pe parttable1(id2) CU (DROP_EXISTING = ON) pe partschema1(Id2);

9. După ștergerea indexului clusterizat din 8 de mai sus, parttable1 rămâne un tabel partiționat

drop index CI_prattable1_id2 pe parttable1;

10. Coloana de partiție ID2 este creată ca un index clusterizat și nu folosește schema de partiționare, iar parttable1 devine un tabel nepartiționat

creează CI_prattable1_id2 de index clusterizat pe parttable1(id2) PE [PRIMARY];

11. După ștergerea indexului grupat al celor 10 de mai sus, parttable1 rămâne un tabel nepartiționat

drop index CI_prattable1_id2 pe parttable1;

12. Coloana de partiție id2 este creată recent ca un index neclusterat, deși se folosește schema de partiționare, este tot un tabel nepartiționat

creează NCI_prattable1_id2 index neclusterat pe parttable1(id2) pe partschema1(Id2);

Dacă tabelul partiționat este convertit într-un tabel normal, constrângerea cheii primare este eliminată, iar indexul clusterului este reconstruit sau reconstruit în cheia primară pentru câmpurile cheii primare originale, dar acest lucru nu este legat de schema de partiționare

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME CU (>

CREEAZĂ PK_NAME INDEXATE CLUSTERIZATE PE Table_name(COLOANĂ) CU (PE [PRIMAR];

sau

ALTER TABLE Table_name ADUGĂ CONSTRÂNGERE PK_NAME PRIMARY KEY CLUSTERED(coloana) CU (PE [PRIMARY];

Dacă tabelul normal este convertit într-un tabel partiționat, dacă cheia primară originală trebuie păstrată, constrângerea cheii primare este eliminată, apoi cheia primară este creată fără a fi setată ca indice agregat, apoi se creează un nou index agregat, iar schema de partiționare este folosită în indexul agregat

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME CU (>

ALTER TABLE Table_name ADUGĂ CONSTRÂNGERE PK_NAME CHEIE PRIMARĂ NONCLUSTERED(coloana) CU (PE [PRIMARY];

--Creează o cheie primară, dar nu este setată ca un index clusterizat

CREEAZĂ UN INDEX CLUSTERIZAT index_name PE Table_name(coloană) PE schema de partiție (câmp de partiție)

--Creează un nou index clusterizat în care se folosește o schemă de partiționare

Interogați numărul total de rânduri și dimensiunea unui tabel partiționat, de exemplu, tabelul este CRM. EmailLog

Executiv sp_spaceused 'CRM. EmailLog';

Interogați informațiile dintr-un tabel partiționat, câte rânduri are fiecare partiție, de exemplu, tabelul este CRM. EmailLog

selectează convert(varchar(50), ps.name

) ca partition_scheme,

p.partition_number,

convert(varchar(10), ds2.name

) ca grup de fișiere,

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

str(p.rows, 9) ca rânduri

de la sys.indexes i

Join sys.partition_schemes ps pe i.data_space_id = ps.data_space_id

Alătură-te sys.destination_data_spaces DDS

pe ps.data_space_id = dds.partition_scheme_id

Alătură-te sys.data_spaces DS2 pe dds.data_space_id = ds2.data_space_id

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

iar p.object_id = i.object_id și p.index_id = i.index_id

Alătură sys.partition_functions pf pe ps.function_id = pf.function_id

JOIN SYS-UL LA STÂNGA. Partition_Range_values v pe pf.function_id = v.function_id

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

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

și i.index_id în (0, 1)

Comandă de p.partition_number

Funcții de interogare a partiției

selectează * din sys.partition_functions

Vizualizați arhitectura partițiilor

selectează * din sys.partition_schemes