[Communication] Résumé des tables partitionnées sur Sql Server

Documentation officielle

Étapes de haut niveau pour créer une table de partitions

1. Créer un groupe de fichiers (similaire à l’espace de table oracle), bien sûr, vous ne pouvez pas le créer, vous pouvez mettre toutes les partitions dans un seul groupe de fichiers

2. Établir une fonction de partition et distribuer les données selon la plage

3. Établir un schéma de partition, associer la fonction de partition, et aussi associer le groupe de fichiers, la fonction partition divise les données en plusieurs plages, vous devez associer plusieurs groupes de fichiers, bien sûr, vous pouvez aussi mettre ces plages de partitions dans le même groupe de fichiers

4. Créer un tableau et associer le schéma de zonage

Un bug rencontré

Lorsque vous faites un clic droit direct sur la table pour exporter la structure de la table, vous ne pouvez pas exporter les informations de partition, et vous ne pouvez exporter les informations de partition de la table qu’en cliquant droit sur le script database-task-generate

Quelques conclusions tirées du tableau de partition :

1. Les champs partitionnés n’ont pas nécessairement besoin d’être indexés

2. Les champs partitionnés peuvent être créés en index clusterés ou noclustered

3. Les champs partitionnés, qu’ils soient des index clusterés ou noclusterés, sont reconstruits en index groupés sans schémas de partitionnement associés, et la table partitionnée devient une table non partitionnée

4. Convertir une table ordinaire en table partitionnée, créer simplement un index groupé dans la table et utiliser le schéma de partitionnement sur l’index clusteré. Par exemple, lorsque le champ 1 d’une table non partitionnée crée un index regroupé et est associé à un schéma de partitionnement, la table est convertie en table partitionnée, et le champ partitionné est le champ 1.

5. Convertir la table partitionnée en table normale, s’il y a un champ de partition avec un index, reconstruire le champ partitionné en un index groupé et ne pas l’associer au schéma de partition ; si le champ de partition n’a pas d’index, le champ de partition peut créer un nouvel index regroupé et ne pas s’associer au schéma de partition

6. Changer une table normale en table de partition ou transformer une table de partition en table ordinaire ne peut être réalisé qu’en utilisant des index groupés, car avec des index groupés se trouve une organisation d’index, et la redistribution des tables se fait par la reconstruction des index clusterisés. Si la table ordinaire devient une table partitionnée, reconstruisons le champ de partition en un index clusterisé et associe-le au schéma de partition, et si la table de partition devient une table normale, reconstruis le champ de partition en un index clusterisé sans associer le schéma de partition.

7. Pour créer une table de partition, la contrainte d’unicité doit inclure une colonne de partition

8. Lors de la création d’un schéma de partition, il est nécessaire de s’assurer que le nombre de groupes de fichiers correspond au segment de plage de partitions de la fonction de partition, et que peu importe si le nom du groupe de fichiers est dupliqué.

9. Les fonctions de partitionnement et les schémas de partitionnement sont dans une base de données, sans faire face à l’instance entière

10. La table de partition est trop grande pour occuper beaucoup d’espace disque, et la taille ne change toujours pas après la suppression de certains champs

Étapes pour créer une table de partitions

1.1. Exemple d’établissement d’un groupe de fichiers

modifier la base de données test1 ajouter le groupe de fichiers partie 1 ;

modifier la base de données test1 ajouter le groupe de fichiers partie 1000 ;

modifier la base de données test1 ajouter le groupe de fichiers part2000 ;

modifier la base de données test1 ajouter le groupe de fichiers partie 3000 ;

modifier la base de données test1 ajouter le groupe de fichiers part4000 ;

1.2. Établir un exemple de fichier et associer un groupe de fichiers

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

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

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

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

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

2. Un exemple d’établissement d’une fonction de partition, la fonction de partition s’appelle partfun1

CREATE PARTITION FUNCTION partfun1 (int)

COMME PLAGE RESTANTE POUR LES VALEURS (« 1000 », « 2 000 », « 3 000 », « 4 000 »)

--VALEURS ('1000', '2000', '3000', '4000') indiquent que le tableau sera divisé en 5 zones, qui sont divisées selon la taille des valeurs dans les champs du tableau, les cinq zones étant les plus petites - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-plus

3. Un exemple d’établissement d’un schéma : associer la fonction de partition partfun1, et associer le groupe de fichiers

CRÉER SCHÉMA DE PARTITION partschema1

EN TANT QUE PARTITION PARTFUN1

TO (parties 1, partie 1000, partie 2000, partie 3000, partie 4000) ;

--Construit sur plusieurs groupes de fichiers : part1, part1000, part2000, part3000, part4000

CRÉER UN SCHÉMA DE PARTITION partschema2

EN TANT QUE PARTITION PARTFUN1

TO (PARTIE1,[PRIMAIRE], [PRINCIPAL], [PRINCIPAL]) ;

--Construit sur la partie 1, groupe de fichiers [PRIMARY], il n’est pas difficile de remplacer la partie 1 par [PRIMARY], afin qu’il soit similaire à la construction du groupe de fichiers [PRIMARY]

CRÉER UN SCHÉMA DE PARTITION partschema3

EN TANT QUE PARTITION PARTFUN1

TOUS POUR (partie 1) ;

--tous construits sur le groupe de fichiers partie 1

CRÉER UN SCHÉMA DE PARTITION partschema4

EN TANT QUE PARTITION PARTFUN1

TOUS VERS ([PRIMAIRE]) ;

--tous construits sur le groupe de fichiers [PRIMARY]

4. Exemple d’établissement d’une table de partition

CREATE TABLE PARTTABLE1(

[ID] [int] NON NULLE,

[IDText] [nvarchar] (max) ZÉRO

[date] [datetime] NULL)

ON [partschema1](ID) ;

insérer dans le parttable1 les valeurs (1,'1', getdate()-4) ;

insérer dans la table1 des valeurs (1001,'1001', getdate()-3) ;

insérer dans les valeurs de la table1 (2001, « 2001 », getdate()-2) ;

insérer dans le parttable1 des valeurs (3001, '3001', getdate()-1) ;

insérer dans les valeurs de la table1 (4001, '4001', getdate()) ;

5. Vérifier les données de la table de partition

SELECT * DEPUIS la table1 ;

--Retourne toutes les lignes de la table de partition

SELECT distinct $PARTITION. [partfun1] (4) DE la partie 1 ;

--Retourne à laquelle appartient la ligne avec la valeur du champ ID 4

SÉLECTIONNEZ * DEPUIS parttable1 où $PARTITION. [partfun1] (ID)=2

--Retourne toutes les lignes de la 2e partition, l’ID est l’ID du champ de partition

Note : Vous ne pouvez pas utiliser SELECT * FROM parttable1 là où $PARTITION. [partfun1] (ID)=2 a un résultat, cela signifie qu’il s’agit d’une table partitionnée, et le dernier test 7 de cet article est une table non partitionnée, mais SELECT * FROM parttable1 où $PARTITION. [partfun1] (ID)=2 a toujours un résultat

Nouvelles partitions

1. Spécifier un groupe de fichiers pouvant être utilisé pour le schéma de partitions (ajouter un groupe de fichiers pour le schéma de partition).

2. Modifier la fonction de partition (ajouter la plage de données de la fonction de partition)

MODIFIER PARTITION SCHEME partschema1 ENSUITE UTILISÉ [PRINCIPAL]

MODIFIER LA FONCTION DE PARTITION partfun1() PLAGE DIVISÉE ('4500')

sélectionner p.partition_number,p.rows de sys.indexes i join internes sys.partitions p on p.object_id = i.object_id et i.object_id = object_id('parttable2') ordonner par 1

--La première affirmation, si le schéma de partition utilise ALL TO ([PRIMAIRE]), cette instruction n’a pas besoin d’être exécutée

--Une nouvelle partition est ajoutée à la deuxième affirmation, la plage est de 4000 à 4500

--La troisième affirmation vérifie si la nouvelle partition existe et s’il y a des lignes

Suppression/Fusion de partition

MODIFIER LA FONCTION DE PARTITION partfun1() PLAGE DE FUSION ('2000')

La partition de 1000-2000 a été supprimée et fusionnée en 1000-3000

--Impossible d’exécuter ALTER TABLE TABLENAME DROP PARTITIONNAME PARTITIONNAME comme Oracle ;

Supprimez la table de partitions et le groupe de fichiers correspondant

L’ordre de suppression est le suivant : supprimer la table de partitions, supprimer le schéma de partitions, supprimer la fonction de partition, et enfin supprimer le groupe de fichiers, et le fichier correspondant est supprimé après la suppression du groupe de fichiers

Exemple de conversion d’une table partitionnée en table normale et d’une table normale en table partitionnée

TABLE DÉVERSIBLE1 ;

CREATE TABLE PARTTABLE1(

[Id] [int] IDENTITY(1,1) NON NULLE,

[Nom] [varchar] (16) NON NULLE,

[Id2] [int] NON NULL

) ON partschema1(Id2) ;

insérer dans le tableau partiel les valeurs ('1',1) ;

insérer dans les valeurs de la table1 ('1001', 1001) ;

insérer dans les valeurs du parttable1 (« 2001 », 2001) ;

insérer dans les valeurs de la tableau1 ('3001', 3001) ;

insérer dans le tableau partiel les valeurs ('4001', 4001) ;

1. La contrainte unique créée sur la table de partition doit contenir une colonne de partition.

MODIFIER LA TABLE PARTTABLE1 AJOUTER UNE CONTRAINTE PK_prattable1_id CLUSTER DE CLÉ PRIMAIRE ([ID] ASC)

La colonne d’erreur 'Id2' est la colonne de partitionnement de l’index 'PK_prattable1_id'. Les colonnes de partition pour un index unique doivent être un sous-ensemble de la clé d’index.

2. Créer un nouvel index regroupé dans la colonne de partition id2, et parttable1 reste une table partitionnée

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) ;

3. La colonne de partition id2 crée un index non regroupé, et parttable1 reste une table partitionnée

indice de chute CI_prattable1_id2 sur parttable1 ;

créer des NCI_prattable1_id2 d’index non regroupés sur parttable1(id2) ;

4. La colonne d’identification de colonne non partitionnée crée un index clusteré, un parttable1 ou une table partitionnée, indiquant que la colonne non partitionnée peut être une colonne d’index de cluster

créer un index regroupé CI_prattable1_id sur parttable1(id) ;

5. La colonne de partition ID2 est reconstruite en un index non clusteré et n’utilise pas de schéma de partitionnement, parttable1 reste une table de partition

Créer un index non regroupé NCI_prattable1_id2 sur parttable1(id2) AVEC (DROP_EXISTING = ON) SUR [PRIMAIRE] ;

6. La colonne de partition id2 est reconstruite en un index clusterisé sans la condition ON, et parttable1 reste une table de partition

indice de baisse CI_prattable1_id sur parttable1 ;

Drop index NCI_prattable1_id2 sur la partition1 ;

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) ;

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) AVEC (DROP_EXISTING = ON ) ;

7. La colonne partitionnée id2 est reconstruite en un index clusterisé avec la condition ON mais sans utiliser le schéma de partitionnement, et parttable1 devient une table non partitionnée

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) AVEC (DROP_EXISTING = ON) SUR [PRIMAIRE] ;

8. La colonne de partition id2 est reconstruite en un index groupé et, en utilisant le schéma de partitionnement, parttable1 devient une table partitionnée

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) AVEC (DROP_EXISTING = ON) sur partschema1(Id2) ;

9. Après suppression de l’index regroupé dans le 8 ci-dessus, parttable1 reste une table partitionnée

indice de chute CI_prattable1_id2 sur parttable1 ;

10. La colonne de partition ID2 est créée comme un index en cluster et n’utilise pas le schéma de partitionnement, et parttable1 devient une table non partitionnée

créer un index regroupé CI_prattable1_id2 sur parttable1(id2) SUR [PRINCIPALY] ;

11. Après suppression de l’index regroupé des 10 ci-dessus, parttable1 reste une table non partitionnée

indice de chute CI_prattable1_id2 sur parttable1 ;

12. La colonne de partition id2 est nouvellement créée comme un index non clusteré, bien que le schéma de partitionnement soit utilisé, il s’agit toujours d’une table non partitionnée

créer un index non regroupé NCI_prattable1_id2 sur parttable1(id2) sur partschema1(Id2) ;

Si la table partitionnée est convertie en table normale, la contrainte de clé primaire est supprimée, et l’index du cluster est reconstruit ou reconstruit en clé primaire pour les champs de la clé primaire d’origine, mais cela n’est pas lié au schéma de partitionnement

MODIFIER TABLE Table_name DROP CONTRAINTE PK_NAME AVEC (>

CRÉEZ PK_NAME INDEX REGROUPÉ SUR Table_name(COLONNE) AVEC (SUR [PRINCIPAL] ;

ou

MODIFIER TABLE Table_name AJOUTER CONTRAINTE PK_NAME CLÉ PRIMAIRE CLUSTERED(colonne) AVEC (SUR [PRINCIPAL] ;

Si la table normale est convertie en table partitionnée, si la clé primaire d’origine doit être conservée, la contrainte de clé primaire est supprimée, puis la clé primaire est créée sans la définir comme indice agrégé, puis un nouvel index agrégé est créé, et le schéma de partitionnement est utilisé dans l’index agrégé

MODIFIER TABLE Table_name DROP CONTRAINTE PK_NAME AVEC (>

MODIFIER TABLE Table_name AJOUTER CONTRAINTE PK_NAME CLÉ PRIMAIRE NONCLUSTERED(colonne) AVEC (SUR [PRIMAIRE] ;

--Créer une clé primaire, mais ne pas être défini comme un index regroupé

CRÉER UN INDEX REGROUPÉ index_name SUR Table_name(colonne) SUR le schéma de partition (champ de partition)

--Créer un nouvel index regroupé dans lequel un schéma de partitionnement est utilisé

Interrogez le nombre total de lignes et la taille d’une table partitionnée, par exemple, la table est un CRM. EmailLog

Exécutif sp_spaceused 'CRM. EmailLog ;

Interrogez les informations d’une table partitionnée, combien de lignes chaque partition a, par exemple, la table est un CRM. EmailLog

Select convert (varchar(50), ps.name

) comme partition_scheme,

p.partition_number,

convertir (varchar(10), ds2.name

) en tant que groupe de fichiers,

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

str(p.rows, 9) en tant que rows

À partir de sys.indexes i

joindre sys.partition_schemes ps sur i.data_space_id = ps.data_space_id

Rejoignez sys.destination_data_spaces DDS

sur ps.data_space_id = dds.partition_scheme_id

rejoindre sys.data_spaces DS2 sur dds.data_space_id = ds2.data_space_id

rejoindre sys.partitions p sur dds.destination_id = p.partition_number

et p.object_id = i.object_id et p.index_id = i.index_id

rejoindre sys.partition_functions PF sur ps.function_id = pf.function_id

À GAUCHE DE REJOINDRE SYS. Partition_Range_values v sur pf.function_id = v.function_id

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

OÙ i.object_id = object_id('crm. E-mailLog')

et i.index_id dans (0, 1)

Commande par p.partition_number

Fonctions de partition d’interrogation

sélectionner * depuis sys.partition_functions

Voir l’architecture de partition

sélectionner * depuis sys.partition_schemes