[Kommunikation] SQL Server Sammanfattning av partitionerade tabeller

Officiell dokumentation

Steg på hög nivå för att skapa en partitionstabell

1. Skapa en filgrupp (liknande oracle-tabellutrymme), självklart kan du inte skapa den, du kan lägga alla partitioner i en filgrupp

2. Etablera en partitionsfunktion och fördela data enligt vilket intervall

3. Etablera ett partitionsschema, associera partitionfunktionen och associera även filgruppen, partitionfunktionen delar upp datan i flera intervall, du behöver associera flera filgrupper, självklart kan du också lägga dessa partitionsintervall i samma filgrupp

4. Skapa en tabell och koppla till zonindelningsschemat

En bugg påträffad

När du högerklickar direkt på tabellen för att exportera tabellstrukturen kan du inte exportera partitionsinformationen, och du kan bara exportera partitionsinformationen för tabellen genom att högerklicka på database-task-generer-skriptet

Några slutsatser från partitionstabellen:

1. Partitionerade fält behöver inte nödvändigtvis indexeras

2. Partitionerade fält kan skapas som klustrade index eller noclusterade index

3. Partitionerade fält, oavsett om de är klustrade index eller icke-klustrade index, byggs om till klustrade index utan tillhörande partitioneringsscheman, och den partitionerade tabellen blir en icke-partitionerad tabell

4. Konvertera en vanlig tabell till en partitionerad tabell, skapa bara ett klustrat index i tabellen och använd partitioneringsschemat på det klustrade indexet. Till exempel, när fält 1 i en icke-partitionerad tabell skapar ett klustrat index och associeras med ett partitioneringsschema, konverteras tabellen till en partitionerad tabell, och det partitionerade fältet är fält 1.

5. Konvertera den partitionerade tabellen till en normal tabell, om det finns ett partitionsfält med ett index, bygg om det partitionerade fältet som ett klustrat index och associera inte med partitionschemat, om partitionsfältet inte har ett index kan partitionsfältet skapa ett nytt klustrat index och inte associeras med partitionsschemat

6. Att byta en normal tabell till en partitionstabell eller en partitionstabell till en vanlig tabell kan endast uppnås genom att använda klustrade index, eftersom klustrade index är indexorganisationstabeller, och omfördelningen av tabeller genomförs genom rekonstruktion av klustrade index. Om den vanliga tabellen blir en partitionerad tabell, rekonstruera partitionsfältet till ett klustrat index och associera det med partitionsschemat, och om partitionstabellen blir en normal tabell, rekonstruera partitionsfältet till ett klustrat index utan att associera partitionsschemat.

7. För att skapa en partitionstabell måste unikhetsbegränsningen inkludera en partitionskolumn

8. När man skapar ett partitionsschema är det nödvändigt att säkerställa att antalet filgrupper matchar partitionsintervallsegmentet i partitionsfunktionen, och det spelar ingen roll om filgruppsnamnet är duplicerat.

9. Partitioneringsfunktioner och partitioneringsscheman finns i en databas och vänder inte mot hela instansen

10. Partitionstabellen är för stor för att ta upp mycket diskutrymme, och storleken ändras fortfarande inte efter att vissa fält tagits bort

Steg för att skapa en partitionstabell

1.1. Exempel på att etablera en filgrupp

alter database test1 lägg till filgrupp del1;

alter database test1 lägg till filgrupp part1000;

alter database test1 Add filegroup part2000;

alter database test1 lägg till filgrupp part3000;

alter database test1 lägg till filgrupp part4000;

1.2. Etablera ett exempel på en fil och associera en filgrupp

ALTER DATABASE test1 LÄGG TILL FIL(NAMN = test1part1,FILNAMN = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) I FILGRUPP del1;

ALTER DATABASE test1 LÄGG TILL FIL(NAMN = test1part1000,FILNAMN = 'G:\test1part1000.ndf', STORLEK = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TILL FILGRUPP part1000;

ALTER DATABASE test1 LÄGG TILL FIL(NAMN = test1part2000,FILNAMN = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TILL FILGRUPP part2000;

ALTER DATABASE test1 LÄGG TILL FIL(NAMN = test1part3000,FILNAMN = 'G:\test1part3000.ndf', STORLEK = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TILL FILGRUPP part3000;

ALTER DATABASE test1 LÄGG TILL FIL(NAMN = test1part4000,FILNAMN = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TILL FILGRUPP part4000;

2. Ett exempel på att etablera en partitionsfunktion, partitionfunktionen kallas partfun1

CREATE PARTITION FUNCTION partfun1 (int)

SOM VÄNSTER INTERVALL FÖR VÄRDEN ('1000', '2000', '3000', '4000')

--VALUES ('1000', '2000', '3000', '4000') indikerar att tabellen kommer att delas in i 5 zoner, som är indelade enligt storleken på värdena i tabellfälten, de fem zonerna är de minsta - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-störst

3. Ett exempel på att etablera ett schema, associera partitionsfunktionen partfun1 och associera filgruppen

SKAPA PARTITIONSSCHEMA partschema1

SOM PARTITION partfun1

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

--Byggd på flera filgrupper av del1, del1000, del2000, del3000, del4000

SKAPA PARTITIONSSCHEMA partschema2

SOM PARTITION partfun1

TILL (DEL1,[PRIMÄR],[PRIMÄR],[PRIMÄR],[PRIMÄR],[PRIMÄR]);

--Byggt på del 1, [PRIMARY] filgrupp, är det inget problem att ersätta del 1 med [PRIMARY], så att det liknar att bygga på [PRIMARY] filgruppen

SKAPA PARTITIONSSCHEMA partschema3

SOM PARTITION partfun1

ALL TO (del 1);

--alla byggda på filgruppen part1

SKAPA PARTITIONSSCHEMA partschema4

SOM PARTITION partfun1

ALLA TILL ([PRIMÄR]);

--alla byggda på [PRIMÄR]-filgruppen

4. Exempel på att upprätta en partitionstabell

SKAPA TABELL parttable1(

[ID] [int] INTE NULL,

[IDText] [nvarchar] (max) NOLL

[Datum] [datumstid] NULL)

PÅ [partschema1](ID);

infoga i parttable1-värden (1,'1', getdate()-4);

infoga i parttable1-värden (1001,'1001',getdate()-3);

infoga i parttable1-värden (2001,'2001', getdate()-2);

infoga i parttable1-värden (3001,'3001', getdate()-1);

infoga i parttable1-värden (4001,'4001',getdate());

5. Verifiera data i partitionstabellen

VÄLJ * FRÅN deltabell1;

--Returnerar alla rader i partitionstabellen

VÄLJ DISTINKTA $PARTITION. [partfun1] (4) FRÅN deltabell1;

--Returner som delar upp raden med ID-fältvärdet 4 tillhör

VÄLJ * FRÅN parttable1 där $PARTITION. [partfun1] (ID)=2

--Returnerar alla rader i den andra partitionen, ID:t är partitionsfältets ID

Obs: Du kan inte använda SELECT * FROM parttable1 där $PARTITION. [partfun1] (ID)=2 har ett resultat, det betyder att det är en partitionerad tabell, och det sista testet 7 i denna artikel är en icke-partitionerad tabell, men SELECT * FRÅN parttable1 där $PARTITION. [partfun1] (ID)=2 har fortfarande ett resultat

Nya partitioner

1. Specificera en filgrupp som kan användas för partitionsschemat (lägg till en filgrupp för partitionsschemat).

2. Modifiera partitionsfunktionen (lägg till dataområdet för partitionsfunktionen)

ALTER PARTITION SCHEME partschema1 NÄSTA ANVÄNDNING [PRIMÄR]

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

Välj p.partition_number,p.rader från sys.indexes i inre join sys.partitions p på p.object_id = i.object_id och i.object_id = object_id('parttable2') ordning med 1

--Det första sättet, om partitionsschemat använder ALL TO ([PRIMARY]), behöver detta uttalande inte exekveras

--En ny partition läggs till i det andra uttalandet, intervallet är 4000–4500

--Det tredje påståendet verifierar om den nya partitionen existerar och om det finns rader

Delete\Merge-partition

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

Partitionen 1000-2000 togs bort och slogs ihop till 1000-3000

--Kan inte köra ALTER TABLE TABLENAME DROP PARTITION PARTITION PARTITIONNAME som oracle;

Ta bort partitionstabellen och motsvarande filgrupp

Ordningen för raderingen är: ta bort partitionstabellen, ta bort partitionschemat, ta bort partitionsfunktionen och slutligen ta bort filgruppen, och motsvarande fil raderas efter att filgruppen raderats

Exempel på att konvertera en partitionerad tabell till en normal tabell och en normal tabell till en partitionerad tabell

DROP TABLE PARTTABLE1;

SKAPA TABELL parttable1(

[id] [int] IDENTITY(1,1) NOT NULL,

[Namn] [varchar] (16) INTE NULL,

[Id2] [int] INTE NULL

) ON partschema1(Id2);

infoga i parttable1-värden ('1',1);

infoga i parttable1-värden ('1001', 1001);

infoga i parttable1-värden ('2001', 2001);

infoga i deltabell1-värden ('3001', 3001);

infoga i parttable1-värden ('4001', 4001);

1. Den unika begränsningen som skapas i partitionstabellen måste innehålla en partitionskolumn.

ALTER TABLE PARTTABLE1 LÄGG TILL BEGRÄNSNING PK_prattable1_id PRIMÄRNYCKELKLUSTRING ([ID] ASC)

Felkolumnen 'Id2' är partitioneringskolumnen i indexet 'PK_prattable1_id'. Partitionskolumner för ett unikt index måste vara en delmängd av indexnyckeln.

2. Skapa ett nytt klustrat index i partitionskolumnen id2, och parttable1 är fortfarande en partitionerad tabell

skapa klustrade index CI_prattable1_id2 på parttable1(id2);

3. Partitionskolumnen id2 skapar ett icke-klustrat index, och parttable1 är fortfarande en partitionerad tabell

droppindex CI_prattable1_id2 på parttable1;

skapa icke-klustrade index NCI_prattable1_id2 på parttable1(id2);

4. Den icke-partitionerade kolumnens id-kolumn skapar ett klustrat index, parttable1 eller en partitionerad tabell, vilket indikerar att den icke-partitionerade kolumnen kan vara en klusterindexkolumn

skapa klustrat index CI_prattable1_id på parttable1(id);

5. Partitionskolumn ID2 byggs om till ett icke-klustrat index och använder inget partitioneringsschema, parttable1 är fortfarande en partitionstabell

skapa icke-klustrade index NCI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÄR];

6. Partitionkolumnen id2 rekonstrueras till ett klustrat index utan ON-villkoret, och parttable1 är fortfarande en partitionstabell

droppindex CI_prattable1_id på deltabell1;

droppindex NCI_prattable1_id2 på parttable1;

skapa klustrade index CI_prattable1_id2 på parttable1(id2);

skapa klustrat index CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ);

7. Partitionerad kolumn id2 byggs om till ett klustrat index med ON-villkor men utan att använda partitioneringsschema, och parttable1 blir en icke-partitionerad tabell

skapa klustrade index CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÄR];

8. Partitionkolumn id2 byggs om till ett klustrat index och med hjälp av partitioneringsschemat blir parttable1 en partitionerad tabell

skapa klustrat index CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = ON) på partschema1(Id2);

9. Efter att ha raderat det klustrade indexet i 8 ovan är parttable1 fortfarande en partitionerad tabell

droppindex CI_prattable1_id2 på parttable1;

10. Partitionskolumn ID2 skapas som ett klustrat index och använder inte partitioneringsschemat, och parttable1 blir en icke-partitionerad tabell

skapa klustrade index CI_prattable1_id2 på parttable1(id2) PÅ [PRIMÄR];

11. Efter att ha raderat det klustrade indexet för ovanstående 10 är parttable1 fortfarande en icke-partitionerad tabell

droppindex CI_prattable1_id2 på parttable1;

12. Partitionskolumnen id2 skapas nyligen som ett icke-klustrat index, även om partitioneringsschemat används är det fortfarande en icke-partitionerad tabell

skapa icke-klustrade index NCI_prattable1_id2 på parttable1(id2) på partschema1(Id2);

Om den partitionerade tabellen konverteras till en normal tabell tas primärnyckelbegränsningen bort, och klusterindexet byggs om eller byggs om till primärnyckeln för fälten i den ursprungliga primärnyckeln, men det är inte relaterat till partitioneringsschemat

ALTER TABLE Table_name DROPPBEGRÄNSNING PK_NAME MED (>

SKAPA KLUSTRAT INDEX PK_NAME PÅ Table_name(KOLUMN) MED (PÅ [PRIMÄR];

eller

ÄNDRA TABELL Table_name LÄGGA TILL BEGRÄNSNING PK_NAME PRIMÄRNYCKEL KLUSTRAD (kolumn) MED (PÅ [PRIMÄR];

Om den normala tabellen omvandlas till en partitionerad tabell, om den ursprungliga primärnyckeln ska behållas, tas primärnyckelbegränsningen bort, och sedan skapas primärnyckeln utan att den sätts som ett aggregerat index, och därefter skapas ett nytt aggregerat index och partitioneringsschemat används i det aggregerade indexet

ALTER TABLE Table_name DROPPBEGRÄNSNING PK_NAME MED (>

ÄNDRA TABELLEN Table_name LÄGGA TILL BEGRÄNSNING PK_NAME PRIMÄRNYCKEL ICKE-KLUSTRAD (kolumn) MED (PÅ [PRIMÄR];

--Skapa en primärnyckel, men inte som ett klustrat index

SKAPA KLUSTRAT INDEX index_name PÅ Table_name(kolumn) I Partition Scheme (Partition Field)

--Skapa ett nytt klustrat index där ett partitioneringsschema används

Fråga om det totala antalet rader och storleken på en partitionerad tabell, till exempel är tabellen CRM. EmailLog

Exec sp_spaceused 'CRM. EmailLog';

Fråga informationen i en partitionerad tabell, hur många rader varje partition har, till exempel är tabellen CRM. EmailLog

Välj konvertering (varchar (50), ps.name

) som partition_scheme,

p.partition_number,

Konvertera(varchar(10), ds2.name

) som filgrupp,

konvertera(varchar(19), isnull(v.värde, ''), 120) som range_boundary,

str(p.rader, 9) som rader

från sys.indexes i

Gå med i sys.partition_schemes PS på i.data_space_id = ps.data_space_id

Gå med sys.destination_data_spaces DDS

on ps.data_space_id = dds.partition_scheme_id

Gå med sys.data_spaces DS2 på dds.data_space_id = ds2.data_space_id

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

och p.object_id = i.object_id och p.index_id = i.index_id

Gå med sys.partition_functions PF på ps.function_id = pf.function_id

VÄNSTER, JOINA system. Partition_Range_values v på pf.function_id = v.function_id

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

DÄR i.object_id = object_id('crm. EmailLog')

och i.index_id i (0, 1)

Beställning av p.partition_number

Frågepartitionsfunktioner

Välj * från sys.partition_functions

Visa partitionsarkitekturen

Välj * från sys.partition_schemes