CREATE PARTITION FUNCTION (Transact-SQL)

Article
09/10/2014

Crée une fonction, dans la base de données active, qui mappe les lignes d'une table ou d'un index avec des partitions à partir des valeurs d'une colonne spécifiée. L'utilisation de CREATE PARTITION FUNCTION est la première étape de la création d'une table ou d'un index partitionné. Dans SQL Server 2012, une table ou un index peut comprendre au maximum 15 000 partitions.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

Arguments

partition_function_name
Nom de la fonction de partition. Les noms des fonctions de partition doivent être uniques dans la base de données et respecter les règles applicables aux identificateurs.
input_parameter_type
Type de données de la colonne utilisée pour le partitionnement. Tous les types de données sont utilisables comme colonnes de partitionnement, à l'exception de text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), des types de données d'alias ou des types de données CLR définis par l'utilisateur.

La colonne effectivement utilisée, appelée « colonne de partitionnement », est spécifiée dans l'instruction CREATE TABLE ou CREATE INDEX.
boundary_value
Spécifie les valeurs limites de chaque partition d'une table ou d'un index partitionné qui utilise partition_function_name. Si boundary_value est vide, la fonction de partition mappe la totalité de la table ou de l'index en une seule partition à l'aide de partition_function_name. Une seule colonne de partitionnement, spécifiée dans une instruction CREATE TABLE ou CREATE INDEX, peut être utilisée.

boundary_value est une expression de constante qui peut référencer des variables. Il peut s'agir de variables de type définies par l'utilisateur, de fonctions et de fonctions définies par l'utilisateur. Elle ne peut pas référencer des expressions Transact-SQL. boundary_value doit correspondre au type de données fourni dans input_parameter_type, ou être implicitement convertible dans ce dernier, et ne peut pas être tronquée pendant une conversion implicite au point que la taille et l'échelle de la valeur ne correspondent pas à celles de son input_parameter_type.

[!REMARQUE]

Si boundary_value est composé de littéraux datetime ou smalldatetime, ces littéraux sont évalués en partant du principe que l'anglais est la langue de session. Ce comportement est déconseillé. Pour vous assurer que la définition de la fonction de partition fonctionne comme prévu pour toutes les langues de session, nous vous recommandons d'utiliser des constantes qui sont interprétées de la même manière quels que soient vos paramètres linguistiques, comme le format aaaammjj, ou bien de convertir explicitement les littéraux dans un style spécifique. Pour déterminer la langue de session de votre serveur, exécutez SELECT @@LANGUAGE.
...n
Spécifie le nombre de valeurs fournies par boundary_value, dans la limite de 14 999. Le nombre de partitions créées est égal à n + 1. Il n'est pas nécessaire que les valeurs soient recensées dans l'ordre. Si les valeurs ne sont pas dans l'ordre, le Moteur de base de données les trie, crée la fonction et affiche un message d'avertissement indiquant qu'elles ne sont pas fournies dans l'ordre. Le moteur de base de données retourne une erreur si n comprend des valeurs dupliquées.
LEFT | RIGHT
Spécifie à quel côté de chaque intervalle de valeur limite, gauche ou droite, appartient boundary_value [ ,...n ] lorsque les valeurs de l'intervalle sont triées par le Moteur de base de données dans l'ordre croissant, de la gauche vers la droite. Si cet argument n'est pas spécifié, la valeur par défaut est LEFT.

Notes

L'étendue d'une fonction de partition est limitée à la base de données dans laquelle elle est créée. Dans la base de données, les fonctions de partition résident dans un espace de noms indépendant des autres fonctions.

Toutes les lignes dont la colonne de partitionnement possède des valeurs NULL sont placées dans la partition située le plus à gauche, sauf si NULL est spécifié comme valeur limite et que RIGHT est indiqué. Dans ce cas, la partition située le plus à gauche est une partition vide et les valeurs NULL sont placées dans la partition suivante.

Autorisations

N'importe laquelle des autorisations suivantes permet d'exécuter CREATE PARTITION FUNCTION :

Autorisation ALTER ANY DATASPACE. Cette autorisation est attribuée par défaut aux membres du rôle de serveur fixe sysadmin et des rôles de base de données fixes db_owner et db_ddladmin.
Autorisation CONTROL ou ALTER sur la base de données dans laquelle la fonction de partition est créée.
Autorisation CONTROL SERVER ou ALTER ANY DATABASE sur le serveur de la base de données dans laquelle la fonction de partition est créée.

Exemples

A.Création d'une fonction de partition RANGE LEFT sur une colonne de type int

La fonction de partition suivante partitionne une table ou un index en quatre partitions.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

Le tableau suivant illustre le partitionnement d'une table dans laquelle cette fonction de partition est appliquée à la colonne de partitionnement col1.

Partition	1	2	3	4
Valeurs	col1 <= 1	col1 > 1 AND col1 <= 100	col1 > 100 AND col1 <=1000	col1 > 1000

B.Création d'une fonction de partition RANGE RIGHT sur une colonne de type int

La fonction de partition suivante utilise pour boundary_value [ ,...n ] les mêmes valeurs que l'exemple précédent, mais elle spécifie RANGE RIGHT.

CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

Le tableau suivant illustre le partitionnement d'une table dans laquelle cette fonction de partition est appliquée à la colonne de partitionnement col1.

Partition	1	2	3	4
Valeurs	col1 < 1	col1 >= 1 AND col1 < 100	col1 >= 100 AND col1 < 1000	col1 >= 1000

C.Création d'une fonction de partition RANGE RIGHT sur une colonne de type datetime

La fonction de partition suivante partitionne une table ou un index en 12 partitions, à raison d'une partition pour chaque mois de valeurs d'une année dans une colonne datetime.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
               '20030501', '20030601', '20030701', '20030801', 
               '20030901', '20031001', '20031101', '20031201');

Le tableau suivant illustre le partitionnement d'une table ou d'un index dans lequel cette fonction de partition est appliquée à la colonne de partitionnement datecol.

Partition	1	2	...	11	12
Valeurs	datecol < February 1, 2003	datecol >= February 1, 2003 AND datecol < March 1, 2003		datecol >= November 1, 2003 AND col1 < December 1, 2003	datecol >= December 1, 2003

D.Création d'une fonction de partition sur une colonne de type char

La fonction de partition suivante partitionne une table ou un index en quatre partitions.

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

Le tableau suivant illustre le partitionnement d'une table dans laquelle cette fonction de partition est appliquée à la colonne de partitionnement col1.

Partition	1	2	3	4
Valeurs	col1 < EX...	col1 >= EX AND col1 < RXE...	col1 >= RXE AND col1 < XR...	col1 >= XR

E.Création de 15 000 partitions

La fonction de partition suivante partitionne une table ou un index en 15 000 partitions.

--Create integer partition function for 15,000 partitions.
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
    SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
    SET @i += 1;    
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO

F.Création de partitions pour plusieurs années

La fonction de partition suivante partitionne une table ou un index en 50 partitions sur une colonne datetime2. Il y a une partition pour chaque mois entre janvier 2007 et janvier 2011.

--Create date partition function with increment by month.
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '20070101';
WHILE @i < '20110101'
BEGIN
    SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';
    SET @i = DATEADD(MM, 1, @i);    
END
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';
EXEC sp_executesql @DatePartitionFunction;
GO