Partager via

  • Article

Transfert efficace de données à l'aide du commutateur de partitionnement

Le partitionnement des données permet de gérer des sous-ensembles de données et d'y accéder facilement et efficacement, tout en conservant l'intégrité de la collecte de données au complet. Vous pouvez utiliser l'instruction Transact-SQL ALTER TABLE...SWITCH pour transférer rapidement et efficacement des sous-ensembles de vos données des manières suivantes :

  • affectation d'une table en tant que partition à une table partitionnée existante ;

  • basculement d'une partition d'une table partitionnée vers une autre ;

  • réaffectation d'une partition pour constituer une table unique.

Pour plus d'informations sur les concepts liés au basculement de partition et pour obtenir un exemple de code mettant en œuvre le basculement de partition, consultez Readme_SlidingWindow.

Exigences générales relatives à la commutation de partitions

Lorsqu'une partition est transférée, les données ne sont pas physiquement déplacées ; seules changent les métadonnées sur l'emplacement des données. Avant de pouvoir basculer des partitions, plusieurs exigences générales doivent être satisfaites :

  • Les deux tables doivent exister préalablement à l'opération SWITCH. La table à partir de laquelle la partition est déplacée (table source) et la table qui reçoit la partition (table cible) doivent toutes les deux exister dans la base de données avant que l'opération de basculement ne soit effectuée.

  • La partition de réception doit exister et doit être vide. Que vous ajoutiez une table en tant que partition à une table partitionnée existante, ou que vous déplaciez une partition d'une table partitionnée vers une autre, la partition qui reçoit la nouvelle doit exister et être vide.

  • La table non partitionnée de réception doit exister et doit être vide. Si vous réaffectez une partition pour constituer une table unique non partitionnée, la table qui reçoit la nouvelle partition doit exister, être vide et ne pas être partitionnée.

  • Les partitions doivent être sur la même colonne. Si vous basculez une partition d'une table partitionnée vers une autre, les deux tables doivent être partitionnées sur la même colonne.

  • Les tables source et cible doivent partager le même groupe de fichiers. Les tables source et cible de l'instruction ALTER TABLE...SWITCH doivent résider dans le même groupe de fichiers et leurs colonnes de grandes valeurs doivent être stockées dans ce même groupe. Tout index, partition d'index ou partition de vue indexée correspondant doit également résider dans le même groupe de fichiers. Cependant, le groupe de fichiers peut être différent de celui des tables correspondantes ou d'autres index correspondants.

Pour plus d'informations sur le basculement de partition lorsque des vues indexées sont définies, consultez Commutation de partitions lors de la définition de vues indexées.

Exigences relatives à la structure des tables et des index

En plus des exigences générales précitées, la table source et la table cible doivent posséder la même structure. Les exigences en termes de structure sont les suivantes :

  • Les tables source et cible doivent présenter la même structure et le même ordre de colonnes. Elles doivent posséder les mêmes colonnes, portant les mêmes noms, ainsi qu'un type de données, une longueur, un classement, une précision, une échelle, une possibilité de valeur NULL et (le cas échéant) des contraintes PRIMARY KEY identiques. Elles doivent également posséder les mêmes valeurs pour ANSI_NULLS et QUOTED IDENTIFIER. En outre, les colonnes doivent être définies dans le même ordre. La propriété IDENTITY n'est pas prise en compte.

    AttentionAttention

    Le basculement de partition peut entraîner l'introduction de doublons dans les colonnes IDENTITY de la table cible, ainsi que des écarts dans les valeurs des colonnes IDENTITY de la table source. Utilisez DBCC CHECKIDENT pour vérifier les valeurs d'identité de vos tables et les corriger le cas échéant.

  • La possibilité de valeur NULL de leurs colonnes de partitionnement doit être identique. Les tables source et cible doivent ou être NULL ou NOT NULL. Si l'une des tables n'est pas partitionnée, la possibilité de valeur NULL de la colonne correspondant à la colonne de partitionnement de l'autre table doit correspondre à celle de la colonne de la table partitionnée.

    Important

    Nous vous recommandons de spécifier NOT NULL sur la colonne de partitionnement de tables partitionnées. Nous vous conseillons aussi de spécifier NOT NULL sur les tables non partitionnées qui constituent les sources ou les cibles des opérations ALTER TABLE...SWITCH. Lorsque des colonnes partitionnées sont NOT NULL, les contraintes CHECK sur les colonnes de partitionnement ne sont pas appliquées pour rechercher la présence de valeurs Null. Les valeurs Null sont généralement placées dans la partition située à l'extrême gauche d'une table partitionnée. L'absence de contrainte NOT NULL sur les tables source et cible peut interférer avec les contraintes CHECK qui sont également définies sur la colonne de partitionnement lorsque vous basculez une partition autre que la partition située à l'extrême gauche et lorsque l'option de base de données ANSI_NULLS est définie à ON.

  • Les colonnes calculées doivent avoir la même syntaxe. Si leurs clés de partition correspondantes sont des colonnes calculées, la syntaxe des expressions définissant leurs colonnes calculées sont identiques et les deux colonnes calculées sont conservées.

  • Les propriétés ROWGUID doivent être les mêmes. Toute colonne définie à l'aide de la propriété ROWGUID doit correspondre à une colonne contenue dans l'autre table, également définie avec la propriété ROWGUID.

  • Les colonnes XML doivent avoir le même schéma. Toute colonne xml doit être de même type que la collection de schémas XML.

  • Les paramètres des lignes de toute colonne text, ntext ou image doivent être identiques. Pour plus d'informations sur ce paramètre, consultez Données de ligne.

  • Les tables doivent posséder les mêmes index cluster. Les tables source et cible doivent posséder les mêmes index cluster, et les index ne peuvent pas être désactivés avant de basculer les partitions.

  • Les index non cluster doivent être définis et identiques. Tout index non cluster défini sur la table cible est également défini sur la table source et est structuré de manière identique en termes d'unicité, de sous-clés et de sens de tri (ASC ou DESC) pour chaque colonne de clé d'index. Les index non-cluster désactivés ne sont pas concernés par cette exigence.

Exigences relatives aux contraintes

Les exigences supplémentaires suivantes, relatives aux contraintes, doivent également être respectées lors du déplacement de partitions :

  • Les contraintes CHECK doivent être strictement identiques sur la source et la cible ou être applicables à la source comme à la cible. Toute contrainte CHECK définie sur la table cible doit aussi être définie sur la table source, soit en tant que correspondance exacte, soit d'une manière applicable (en tant que sous-ensemble, par exemple) aux contraintes CHECK de la table cible.

  • Les contraintes sur les colonnes int doivent être identiques ou constituer un sous-ensemble. Toute contrainte CHECK sur les colonnes int de la table source doit correspondre ou exister en tant que sous-ensemble de contraintes sur la colonne int de la table cible. Par exemple, si la table cible a une contrainte sur une colonne int Colonne1 qui spécifie Colonne1 < 100, la colonne Colonne1 correspondante de la table source doit avoir la même contrainte ou un sous-ensemble de la contrainte appliqué aux valeurs dans la table cible, par exemple, Colonne1 < 90 de la table source. Les contraintes CHECK qui spécifient plusieurs colonnes doivent être définies à l'aide de la même syntaxe.

  • Les tables non partitionnées doivent avoir les mêmes contraintes que la partition cible. Si vous ajoutez une table non partitionnée en tant que partition à une table partitionnée existante, il doit exister une contrainte définie sur la colonne de la table source qui correspond à la clé de partition de la table cible. Cela permet de garantir que la plage de valeurs sera comprise dans les valeurs limites de la partition cible.

  • Les valeurs limites de la partition source doivent être dans la limite de la partition cible. Si vous basculez une partition d'une table partitionnée vers une autre table partitionnée, les valeurs limites de la partition source doivent être comprises dans celles de la partition cible. Si ce n'est pas le cas, il doit exister une contrainte définie sur la clé de partition de la table source afin de s'assurer que toutes les données de la table sont comprises dans les valeurs limites de la partition cible.

    AttentionAttention

    Évitez toute conversion de type de données dans les définitions de contraintes. Les contraintes assorties d'une conversion de type de données implicite ou explicite qui sont définies sur les tables à la source du basculement de partition peuvent entraîner l'échec de l'instruction ALTER TABLE...SWITCH.

  • Les tables source et cible doivent avoir les mêmes contraintes FOREIGN KEY. Si la table cible possède des contraintes FOREIGN KEY, la table source doit disposer des mêmes clés étrangères définies dans les colonnes correspondantes, et ces clés étrangères doivent faire référence à la même clé primaire que celles de la table cible. Les clés étrangères de la table source ne peuvent pas être marquées is_not_trusted (visibles dans l'affichage catalogue sys.foreign_keys), à moins que la clé étrangère correspondante de la table cible soit également marquée is_not_trusted. Pour plus d'informations sur ce paramètre, consultez Recommandations pour la désactivation des index. SQL Server applique toute règle CASCADE définie sur les clés étrangères de la table cible à la partition que vous venez de déplacer.

Exigences supplémentaires relatives au déplacement de partitions

Les exigences supplémentaires suivantes doivent également être respectées lors du déplacement de partitions :

  • Les index doivent être alignés sur les partitions de table. Tout index de la table source doit être aligné sur la table source et tout index de la table cible doit être aligné sur la table cible. La table source et la table cible peuvent être partitionnées toutes les deux, non partitionnées toutes les deux, mais il est aussi possible qu'une seule des deux soit partitionnée. Pour plus d'informations sur l'alignement des index, consultez Consignes spéciales pour les index partitionnés.

  • Des contraintes et exigences supplémentaires s'appliquent aux tables sources avec vues indexées. Si une vue indexée est définie dans la table cible de l'instruction ALTER TABLE … SWITCH, consultez Commutation de partitions lors de la définition de vues indexées pour connaître les contraintes et obtenir des exemples.

  • Aucun index de texte intégral n'est autorisé. Il ne peut exister aucun index de texte intégral, ni sur la table source, ni sur la table cible.

  • Aucun index XML n'est autorisé sur la table cible. Il ne peut exister aucun index XML sur la table cible.

  • Aucune relation clé primaire/clé étrangère ne doit être définie si la table source comprend la clé primaire. Il ne peut exister aucune relation de clé primaire/étrangère active entre la table source et la table cible dans laquelle la table source contient la clé primaire.

  • Aucune relation clé primaire/clé étrangère ne doit être définie si la table cible comprend la clé étrangère. Il ne peut exister aucune relation de clé primaire/étrangère active entre la table source et la table cible dans laquelle la table cible contient la clé étrangère.

  • Aucune clé étrangère d'une autre table ne peut faire référence à la table source. La table source ne peut pas être référencée par une clé étrangère contenue dans une autre table.

  • Aucune règle n'est autorisée sur les tables source ou cible. Il ne peut exister aucune règle définie sur la table source ou sur la table cible. Des contraintes CHECK peuvent être utilisées sur les tables source et cible.

    [!REMARQUE]

    Les règles constituent une fonction de compatibilité ascendante. L'implémentation par défaut consiste à utiliser des contraintes CHECK. Pour connaître les restrictions applicables dans le cadre des contraintes CHECK, consultez la section Exigences relatives aux contraintes plus haut dans cette rubrique.

  • Les tables source et cible ne doivent pas être répliquées. Ni la table source, ni la table cible ne peuvent être des sources de réplication.

  • Vous devez disposer des autorisations de base de données requises avant le basculement de partition. Le basculement de partition utilisant une instruction ALTER TABLE, vous devez disposer des autorisations de base de données requises associées à cette instruction. Le jeu d'autorisations de la table source et de la table cible ne doivent pas être identiques.

  • Les déclencheurs ne doivent pas s'activer lors du déplacement des partitions. Aucun déclencheur INSERT, UPDATE ou DELETE ou encore aucune action en cascade n'est activée en déplaçant les partitions de table, et les tables source ou cible ne doivent pas nécessairement posséder des déclencheurs définis de la même manière pour déplacer des partitions.

    [!REMARQUE]

    Au cours d'une opération ALTER TABLE…SWITCH, un verrou de modification du schéma est acquis sur les tables source et cible pour s'assurer qu'aucune autre connexion ne fait référence aux tables lors de la modification. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.

Pour déplacer des partitions de table