[Kommunikation] Sql Server Summary of Partitioned Tables

Officiel dokumentation

Højniveau-trin til at oprette en partitiontabel

1. Opret en filgruppe (lignende oracle tablespace), selvfølgelig kan du ikke oprette den, du kan samle alle partitioner i én filgruppe

2. Etabler en partitionsfunktion og distribuer data efter hvilket interval

3. Opret et partitionsskema, associer partitionfunktionen og associer også filgruppen, partitionfunktionen opdeler dataene i flere intervaller, du skal tilknytte flere filgrupper, selvfølgelig kan du også placere disse partitionsintervaller i samme filgruppe

4. Opret en tabel og tilknyt zoneskemaet

En fejl stødt på

Når du direkte højreklikker på tabellen for at eksportere tabelstrukturen, kan du ikke eksportere partitionsinformationen, og du kan kun eksportere partitionsoplysningerne fra tabellen ved at højreklikke på database-task-generer-scriptet

Nogle konklusioner fra opdelingstabellen:

1. Opdelte felter behøver ikke nødvendigvis at være indekserede

2. Partitionerede felter kan oprettes som klyngede indekser eller no-klyngede indekser

3. Partitionerede felter, uanset om de er klyngede indekser eller ikke-klyngede indekser, genopbygges til klyngede indekser uden tilknyttede opdelingsskemaer, og den opdelte tabel bliver en ikke-opdelt tabel

4. Konverter en almindelig tabel til en opdelt tabel, opret blot et klyngeindeks i tabellen og brug partitioneringsskemaet på det klyngede indeks. For eksempel, når felt 1 i en ikke-partitioneret tabel opretter et klyngeindeks og er forbundet med en partitioneringsordning, konverteres tabellen til en partitioneret tabel, og det opdelte felt er felt 1.

5. Konverter den opdelte tabel til en normal tabel; hvis der er et partitionsfelt med et indeks, genopbyg det opdelte felt som et klyngeindeks og ikke associer med partitionsskemaet; hvis partitionsfeltet ikke har et indeks, kan partitionsfeltet oprette et nyt klyngeindeks og ikke associeres med partitionskemaet

6. At ændre en normal tabel til en partitiontabel eller en partitiontabel til en almindelig tabel kan kun opnås ved brug af klyngeindekser, fordi med klyngeindekser er der indeksorganiseringstabeller, og omfordelingen af tabeller realiseres gennem rekonstruktion af klyngeindekser. Hvis den almindelige tabel bliver en partitioneret tabel, rekonstruer partitionsfeltet til et klynget indeks og associer det med partitionskemaet, og hvis partitionstabellen bliver en normal tabel, rekonstruer partitionsfeltet til et klynget indeks uden at tilknytte partitionsskemaet.

7. For at oprette en partitiontabel skal entydighedsbegrænsningen inkludere en partitionskolonne

8. Når man opretter et partitionsskema, er det nødvendigt at sikre, at antallet af filgrupper matcher partitionsområdets segment i partitionfunktionen, og det er ligegyldigt, om filgruppenavnet er duplikeret.

9. Partitioneringsfunktioner og partitioneringsskemaer er i en database og vender ikke mod hele instansen

10. Partitionstabellen er for stor til at optage meget diskplads, og størrelsen ændrer sig stadig ikke efter at have slettet nogle felter

Trin til at oprette en partitionstabel

1.1. Eksempel på oprettelse af en filgruppe

Alter database test1 tilføj filgruppe del1;

alter database test1 tilføj filgruppe part1000;

alter database test1 tilføj filgruppe part2000;

Alter database test1 tilføj filgruppe part3000;

alter database test1 tilføj filgruppe part4000;

1.2. Etabler et eksempel på en fil og tilknyt en filgruppe

ALTER DATABASE test1 TILFØJ FIL(NAVN = test1part1,FILNAVN = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TIL FILEGROUP del1;

ALTER DATABASE test1 TILFØJ FIL(NAVN = test1part1000,FILNAVN = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TIL FILEGROUP part1000;

ALTER DATABASE test1 TILFØJ FIL(NAVN = test1part2000,FILNAVN = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TIL FILEGROUP part2000;

ALTER DATABASE test1 TILFØJ FIL(NAVN = test1part3000,FILNAVN = 'G:\test1part3000.ndf', STØRRELSE = 5MB,MAXSIZE = 100MB,FILVÆKST = 5MB) TIL FILGRUPPE part3000;

ALTER DATABASE test1 TILFØJ FIL(NAVN = test1part4000,FILNAVN = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TIL FILEGROUP part4000;

2. Et eksempel på etablering af en partitionsfunktion, partitionfunktionen kaldes partfun1

CREATE PARTITION FUNCTION partfun1 (int)

SOM VENSTRE INTERVAL FOR VÆRDIER ('1000', '2000', '3000', '4000')

--VALUES ('1000', '2000', '3000', '4000') angiver, at tabellen vil blive opdelt i 5 zoner, som er opdelt efter størrelsen af værdierne i tabelfelterne, de fem zoner er de mindste - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-største

3. Et eksempel på etablering af et skema, associer partitionfunktionen partfun1 og associer filgruppen

OPRET PARTITIONSKEMA partschema1

AS PARTITION partfun1

TO (del1, del1000, del2000, del3000, del4000);

--Bygget på flere filgrupper af del1, del1000, del2000, del3000, del4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

TO (DEL1,[PRIMÆR],[PRIMÆR],[PRIMÆR],[PRIMÆR]]);

--Bygget på del 1, [PRIMARY] filgruppen, er det ikke noget problem at erstatte del1 med [PRIMARY], så det ligner at bygge på [PRIMARY] filgruppen

OPRET PARTITIONSSKEMA partschema3

AS PARTITION partfun1

ALL TO (del 1);

--alle bygget på part1-filgruppen

OPRETTE PARTITIONSKEMA partschema4

AS PARTITION partfun1

ALLE TIL ([PRIMÆR]);

--alle bygget på [PRIMARY] filgruppen

4. Eksempel på oprettelse af en partitionstabel

OPRET TABEL parttable1(

[ID] [int] IKKE NULL,

[IDText] [nvarchar] (max) NUL

[Dato] [datotid] NULL)

PÅ [partschema1](ID);

indsæt værdier i parttable1 (1,'1', getdate()-4);

indsæt værdier i parttable1 (1001,'1001', getdate()-3);

indsæt i parttable1-værdier (2001,'2001', getdate()-2);

indsæt værdier i parttable1 (3001,'3001', getdate()-1);

indsæt værdier i parttable1 (4001,'4001',getdate());

5. Verificér dataene i partitionstabellen

VÆLG * FRA deltabel1;

--Returnerer alle rækker i partitionstabellen

VÆLG FORSKELLIGE $PARTITION. [partfun1] (4) FRA deltabel1;

--Returner, som opdeler rækken med ID-feltværdien 4, tilhører til

VÆLG * FRA parttable1, hvor $PARTITION. [partfun1] (ID)=2

--Returnerer alle rækker i den anden partition, ID er partitionsfeltets ID

Bemærk: Du kan ikke bruge SELECT * FROM parttable1, hvor $PARTITION. [partfun1] (ID)=2 har et resultat, det betyder, at det er en opdelt tabel, og den sidste test 7 i denne artikel er en ikke-partitioneret tabel, men VÆLG * FRA parttable1, hvor $PARTITION. [partfun1] (ID)=2 har stadig et resultat

Nye opdelinger

1. Angiv en filgruppe, der kan bruges til partitionsskemaet (tilføj en filgruppe til partitionsskemaet).

2. Ændr partitionfunktionen (tilføj dataområdet for partitionfunktionen)

ALTER PARTITION SCHEME partschema1 NÆSTE BRUGES [PRIMÆR]

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

Vælg p.partition_number,p.rækker fra sys.indexes i indre join sys.partitions p på p.object_id = i.object_id og i.object_id = object_id('parttable2') orden med 1

--Den første sætning, hvis partitionsskemaet bruger ALL TO ([PRIMARY]), behøver denne sætning ikke at blive udført

--En ny partition tilføjes til den anden sætning, intervallet er 4000-4500

--Den tredje sætning verificerer, om den nye partition eksisterer, og om der er rækker

Slet/Merge-partition

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

Partitionen 1000-2000 blev slettet og slået sammen til 1000-3000

--Kan ikke udføre ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME som oracle;

Slet partitionstabellen og den tilsvarende filgruppe

Rækkefølgen for sletning er: slet partitiontabellen, slet partitionskemaet, slet partitionfunktionen og til sidst slet filgruppen, og den tilsvarende fil slettes efter filgruppen er slettet

Eksempel på at konvertere en partitioneret tabel til en normal tabel og en normal tabel til en partitioneret tabel

DROP TABLE PARTTABLE1;

OPRET TABEL parttable1(

[id] [int] IDENTITET(1,1) IKKE NULL,

[Navn] [varchar] (16) IKKE NULL,

[Id2] [int] IKKE NULL

) ON partschema1(Id2);

indsæt værdier i parttable1 ('1',1);

indsæt i parttable1-værdier ('1001', 1001);

indsæt i parttable1-værdier ('2001', 2001);

indsæt værdier i parttable1 ('3001', 3001);

indsæt i parttable1-værdier ('4001', 4001);

1. Den unikke begrænsning, der oprettes i partitionstabellen, skal indeholde en partitionskolonne.

ALTER TABLE parttable1 TILFØJ BEGRÆNSNING PK_prattable1_id PRIMÆRNØGLE CLUSTERED ([ID] ASC)

Fejlkolonne 'Id2' er opdelingskolonnen for indekset 'PK_prattable1_id'. Partitionskolonner for et unikt indeks skal være en delmængde af indeksnøglen.

2. Opret et nyt klyngeindeks i partitionkolonnen id2, og parttable1 er stadig en partitioneret tabel

oprette klyngeindeks CI_prattable1_id2 på parttable1(id2);

3. Partitionskolonne id2 opretter et ikke-klynget indeks, og parttable1 er stadig en partitioneret tabel

drop index CI_prattable1_id2 på parttable1;

oprette ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2);

4. Den ikke-opdelte kolonne-id-kolonne opretter et klyngeindeks, parttable1 eller en opdelt tabel, hvilket angiver, at den ikke-opdelte kolonne kan være en klyngeindekskolonne

oprette klyngeindeks CI_prattable1_id på parttable1(id);

5. Partitionkolonne ID2 genopbygges til et ikke-klynget indeks og bruger ikke en partitioneringsmetode, parttable1 er stadig en partitiontabel

Opret ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÆR];

6. Partitionkolonnen id2 rekonstrueres til et klyngeindeks uden ON-betingelsen, og parttable1 er stadig en partitiontabel

drop index CI_prattable1_id på parttable1;

drop index NCI_prattable1_id2 på parttable1;

oprette klyngeindeks CI_prattable1_id2 på parttable1(id2);

oprette klyngeindeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = ON);

7. Partitioneret kolonne id2 genopbygges til et klyngeindeks med ON-betingelse, men uden brug af partitioneringsskema, og parttable1 bliver en ikke-partitioneret tabel

Opret klyngeindeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÆR];

8. Partitionskolonne id2 genopbygges til et klyngeindeks, og ved hjælp af partitioneringsskemaet bliver parttable1 en partitioneret tabel

oprette klyngeindeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = ON) på partschema1(Id2);

9. Efter at have slettet det klyngede indeks i 8 ovenfor, er parttable1 stadig en partitioneret tabel

drop index CI_prattable1_id2 på parttable1;

10. Partitionkolonne ID2 oprettes som et klyngeindeks og bruger ikke partitioneringsskemaet, og parttable1 bliver en ikke-partitioneret tabel

oprette klyngeindeks CI_prattable1_id2 på parttable1(id2) PÅ [PRIMÆR];

11. Efter at have slettet det klyngede indeks for ovenstående 10, er parttable1 stadig en ikke-partitioneret tabel

drop index CI_prattable1_id2 på parttable1;

12. Partitionkolonnen id2 oprettes nyligt som et ikke-klynget indeks, selvom partitioneringsskemaet bruges, er det stadig en ikke-partitioneret tabel

oprette ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2) på partschema1(Id2);

Hvis den partitionerede tabel konverteres til en normal tabel, slettes primærnøglebegrænsningen, og klyngeindekset genopbygges eller genopbygges til primærnøglen for felterne i den oprindelige primærnøgle, men det er ikke relateret til partitioneringsskemaet

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME MED (>

OPRET KLYNGEINDEKS PK_NAME PÅ Table_name(kolonne) MED (PÅ [PRIMÆR];

eller

ALTER TABLE Table_name TILFØJ BEGRÆNSNING PK_NAME PRIMÆR NØGLE CLUSTERED(kolonne) MED (PÅ [PRIMÆR];

Hvis den normale tabel konverteres til en partitioneret tabel, hvis den oprindelige primærnøgle skal bevares, slettes primærnøglebegrænsningen, og derefter oprettes primærnøglen uden at sætte den som et aggregeret indeks, hvorefter et nyt aggregeret indeks oprettes, og partitioneringsmetoden bruges i det aggregerede indeks

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME MED (>

ALTER TABLE Table_name TILFØJ CONSTRAINT PK_NAME PRIMÆRNØGLE NONCLUSTERED(column) MED (PÅ [PRIMÆR];

--Opret en primærnøgle, men sæt ikke som et klynget indeks

OPRET KLYNGEINDEKS index_name PÅ Table_name(kolonne) I Partitionskema (Partitionsfelt)

--Opret et nyt klyngeindeks, hvor et partitioneringsskema anvendes

Forespørg det samlede antal rækker og størrelsen af en partitioneret tabel, for eksempel er tabellen CRM. EmailLog

Executive sp_spaceused 'CRM. EmailLog';

Forespørg informationen i en opdelt tabel, hvor mange rækker hver partition har, for eksempel er tabellen CRM. EmailLog

Vælg Convert(Varchar(50), ps.name

) som partition_scheme,

p.partition_number,

Konverter(varchar(10), ds2.name

) som filgruppe,

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

str(p.rækker, 9) som rækker

fra sys.indexes i

Tilmeld dig sys.partition_schemes PS på i.data_space_id = ps.data_space_id

Bliv medlem af sys.destination_data_spaces DDS

på ps.data_space_id = dds.partition_scheme_id

Tilmeld dig sys.data_spaces DS2 på dds.data_space_id = ds2.data_space_id

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

og p.object_id = i.object_id og p.index_id = i.index_id

Tilmeld dig sys.partition_functions PF på ps.function_id = pf.function_id

VENSTRE, JOIN sys. Partition_Range_values v på pf.function_id = v.function_id

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

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

og i.index_id i (0, 1)

Bestilling efter p.partition_number

Forespørgselspartitionsfunktioner

Vælg * fra sys.partition_functions

Se partitionsarkitekturen

Vælg * fra sys.partition_schemes