IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

Utilisation de la clause MOVE TO


Sujet :

MS SQL Server

  1. #1
    Membre confirmé Avatar de dream_rachid
    Homme Profil pro
    DBA & Responsable BI
    Inscrit en
    Mars 2006
    Messages
    278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Tunisie

    Informations professionnelles :
    Activité : DBA & Responsable BI
    Secteur : Distribution

    Informations forums :
    Inscription : Mars 2006
    Messages : 278
    Points : 474
    Points
    474
    Par défaut Utilisation de la clause MOVE TO
    Bonjour ,

    A partir de SQL Server 2005, une nouvelle clause MOVE TO a été ajoutée à la commande ALTER TABLE pour permettre le déplacement d'une table sur un autre groupe de fichiers. L’instruction MOVE TO est utilisée avec la clause DROP CONSTRAINT dans le ALTER TABLE syntaxe. Quand une contrainte de clé primaire ou une contrainte qui a créé un index cluster est supprimé, la feuille de données au niveau des lignes de l'index cluster sont placés dans la table en « non cluster ».
    Lorsqu’un index cluster est supprimé (En laissant tomber la contrainte qui a créé index cluster), on peut déplacer la table au nouveau groupe de fichiers ou schéma de partition dans une même opération en utilisant l'option de cette nouvelle «aller». Voyons cela avec l'aide d'un exemple.

    Nous allons créer dans la base de données de test, deux groupes de fichiers et une table et le remplir avec quelques données.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
     
    USE master
    GO
    CREATE DATABASE TEST
    GO
    ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
    GO
    ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
    GO
    ALTER DATABASE TEST
    ADD FILE
    ( NAME = TEST1,
    FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_1.ndf’,
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB)
    TO FILEGROUP TEST_DATA_1
    GO
    ALTER DATABASE TEST
    ADD FILE
    ( NAME = TEST2,
    FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_2.ndf’,
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB)
    TO FILEGROUP Test_DATA_2
    GO
    USE TEST
    GO
    L’exécution de la procédure stockée système sp_help pour la table TAB1 indique que le groupe de fichiers pour la table est TEST_DATA_1.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    sp_help TAB1
    Data_located_on_filegroup
    ---------
    TEST_DATA_1
    ---- -------------------
    PK_TAB1 clustered, unique, primary key located on TEST_DATA_1
    constraint_name CONSTRAINT_TYPE
    --------- ------
    PRIMARY KEY (cluster) PK_TAB1
    Le résultat ci-dessus indique que la contrainte PK_TAB1 est utilisée pour créer l'index cluster sur la table TAB1. Veuillez noter qu'à chaque fois qu'une contrainte de clé primaire est définie, et l’index cluster n'existe pas sur la table, SQL Server crée un index clustérisé pour la contrainte de clé primaire. Ce n'est pas la même pour une contrainte UNIQUE Contrainte.
    Unique constraint/ index sera toujours non-cluster sauf qu’il est indiqué lors de la création de contrainte ou index.

    Pour déplacer la table à un groupe de fichiers différents, nous avons besoin d'utiliser la commande drop constraint indiqué ci-dessous. Une fois la table est déplacée vers un nouveau filegroup nous pouvons recréer la contrainte de clé primaire.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    ALTER TABLE DROP CONSTRAINT TAB1 PK_TAB1 AVEC (MOVE TO TEST_DATA_2)
    GO
    ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY (TAB1_ID)
    GO
    Après avoir exécuté la commande ci-dessus, table TAB1 va maintenant résider sur un groupe de fichiers TEST_DATA_2. Si vous ré-exécuter la procédure stockée sp_help, il affiche le groupe de fichiers changement pour la table TAB1.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    sp_help TAB1
    Data_located_on_filegroup
    ---------
    TEST_DATA_2

    Nous devons garder à l'esprit les restrictions suivantes lorsque l'option MOVE TO est utilisé:

    • MOVE TO n'est pas valide pour les vues indexées ou les index non-cluster.
    • Le schéma de partition ou groupe de fichiers doit déjà exister.
    • Si MOVE TO n'est pas spécifié, la table sera situé dans le même schéma de partition ou groupe de fichiers qui a été défini pour l'index cluster.

    Cela devient très pratique quand on veut déplacer des grandes tables à leur propre groupe de fichiers pour des raisons de performance ou pour la sauvegarde des tables par l'intermédiaire de groupes de fichiers de sauvegarde.

    Merci

  2. #2
    Membre chevronné

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Points : 1 758
    Points
    1 758
    Par défaut
    Vous pouvez simplement recreer l'index cluster sur un autre filegroup:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    CREATE CLUSTERED INDEX ... With Drop_existing = ON ON filegroup

  3. #3
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    La méthode proposée par Ptit_Dje est à mon sens la meilleure surtout lorsqu'il s'agit de tables volumineuses.

    Je m'explique :

    La méthode que vous proposez (dream_rachid) permet en effet de déplacer les données d'une table vers un groupe de fichiers différent. Cependant la suppression et le recréation de l'index cluster entrainera une mise à jour des index non cluster 2 fois (Mise à jour des ROW LOCATORS des pages du niveau feuille lorsque l'index cluster est supprimé + 2ème mise à jour une fois que l'index cluster est recréé). Une opération qui peut être très couteuse en terme d'IO et de journalisation.

    De plus, avec TO MOVE, il me semble que les éventuelles clés étrangères dépendantes doivent être recréées également. Ce qui signifie qu' un coût supplémentaire d'analyse sera engendré par la vérification des valeurs des foreign key à la création

    ++

  4. #4
    Membre confirmé Avatar de dream_rachid
    Homme Profil pro
    DBA & Responsable BI
    Inscrit en
    Mars 2006
    Messages
    278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Tunisie

    Informations professionnelles :
    Activité : DBA & Responsable BI
    Secteur : Distribution

    Informations forums :
    Inscription : Mars 2006
    Messages : 278
    Points : 474
    Points
    474
    Par défaut
    Je vous remercies pour vos remarques très intéressantes :

    bon en fait afin d'optimiser ma base de données (du DWH) j'ai crée d'autres groupes de fichiers pour alléger la partition principale et j'ai rencontré un problème lors de déplacement des tables (très volumineuses ) après mes recherches j'ai trouvé la clause MOVE TO et j'ai aimé voir si elle est la plus adéquate pour ce genre de traitement .

    et en lisant vos commentaires vraiment j'ai trouver d'autres solutions que je vais les essayer et comparer leurs temps d'exécution pour affirmer laquelle la plus performante

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Utilisation de la clause SQL HAVING COUNT
    Par nicolasline dans le forum Designer
    Réponses: 4
    Dernier message: 17/02/2011, 23h57
  2. Utilisation de la clause group by
    Par kroma23 dans le forum Bases de données
    Réponses: 4
    Dernier message: 17/09/2008, 13h53
  3. Utilisation de la clause UNIQUE en SQLITE3
    Par santuD dans le forum SQLite
    Réponses: 1
    Dernier message: 18/04/2008, 20h41
  4. [9ir2] Utilisation de la clause WITH
    Par in dans le forum SQL
    Réponses: 9
    Dernier message: 19/09/2007, 11h27
  5. [ODBC] Problème d'utilisation de la clause LIMIT
    Par Thierry8 dans le forum PHP & Base de données
    Réponses: 7
    Dernier message: 14/10/2005, 10h55

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo