Comment ajouter une colonne NOT NULL à une grande table dans SQL Server?

Question

Pour ajouter une Colonne not NULL à une table avec de nombreux dossiers, une contrainte par DÉFAUT doit être appliquée. Cette contrainte entraîne l'ensemble de la commande ALTER TABLE pour prendre du temps à s'exécuter si la table est très importante. C'est parce que:

Hypothèses:

1. La contrainte par DÉFAUT modifie les enregistrements existants. Cela signifie que la db a besoin d'augmenter la taille de chaque enregistrement, ce qui cause le changement d'enregistrements de données complètes-pages à d'autres données-pages et qui prend du temps.
2. Le DÉFAUT de mise à jour s'exécute comme une transaction atomique. Cela signifie que le journal des transactions devront être développé ainsi qu'un roll-back ne peut être exécuté si nécessaire.
3. Le journal des transactions garde la trace de l'ensemble de l'enregistrement. Par conséquent, même si un seul champ est modifié, l'espace requis par le journal sera basée sur la taille de l'ensemble de l'enregistrement, multiplié par le nombre de dossiers existants. Cela signifie que l'ajout d'une colonne à une table avec des petits dossiers sera plus rapide que d'ajouter une colonne à une table avec les grands dossiers, même si le nombre total de dossiers sont les mêmes pour les deux tables.

Solutions possibles:

1. Sucer et attendre la fin du processus. Assurez-vous de définir le délai d'attente est très longue. Le problème, c'est que cela peut prendre des heures ou des jours à faire en fonction du nombre de dossiers.
2. Ajouter la colonne, mais autoriser les valeurs NULL. Par la suite, exécuter une requête mise à JOUR pour définir la valeur par DÉFAUT pour les lignes existantes. Ne pas faire de mise à JOUR *. Mise à jour des lots de documents à un moment ou vous vous retrouverez avec le même problème que la solution n ° 1. Le problème avec cette approche est que vous vous retrouvez avec une colonne qui permet NULL lorsque l'on sait que c'est inutile option. Je crois qu'il y a quelques documents sur les pratiques exemplaires qui dit que vous ne devriez pas avoir des colonnes qui permettent NULL sauf si c'est nécessaire.
3. Créer une nouvelle table avec le même schéma. Ajouter la colonne à ce schéma. Transférer les données de la table d'origine. Chute de la table d'origine et renommez la nouvelle table. Je ne suis pas certain que c'est mieux que le #1.

Questions:

1. Sont mes hypothèses correctes?
2. Sont mes solutions? Si oui, lequel est le meilleur? I f pas de ce que je pourrais faire d'autre?

Answer 1

J'ai rencontré ce problème pour mon travail aussi. Et ma solution est le long de #2.

Voici mes étapes (je suis à l'aide de SQL Server 2005):

1) Ajouter la colonne à la table avec une valeur par défaut:

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('')

2) Ajouter un NOT NULL de la contrainte à l' NOCHECK option. L' NOCHECK ne pas appliquer sur les valeurs existantes:

ALTER TABLE MyTable WITH NOCHECK
ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL)

3) mettre à Jour les valeurs progressivement dans le tableau:

GO
UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL
GO 1000

• L'instruction de mise à jour met à jour uniquement maximale de 3000 enregistrements. Cela permet d'enregistrer un bloc de données à la fois. J'ai utiliser "Macolonne EST NULLE" parce que mon tableau n'est pas une séquence de clé primaire.

• GO 1000 exécutera l'instruction précédente 1000 fois. Ceci mettra à jour 3 millions de disques, si vous avez besoin de plus il suffit d'augmenter ce nombre. Il continuera à exécuter jusqu'à ce que SQL Server renvoie 0 enregistrements pour l'instruction de mise à JOUR.

Answer 2

Voici ce que je voudrais essayer:

• Faire une sauvegarde complète de la base de données.
• Ajout de la nouvelle colonne, permettant null - ne pas définir une valeur par défaut.
• Set de récupération SIMPLE, qui tronque la tran journal dès que chaque lot est engagé.
• Le SQL est: ALTER DATABASE XXX SET de RÉCUPÉRATION SIMPLE
• Exécutez la mise à jour dans les lots que vous avez discuté ci-dessus, de commettre après chacun d'eux.
• Réinitialisation de la nouvelle colonne de ne plus autoriser les valeurs null.
• Revenir à la normale de la récupération.
• Le SQL est: ALTER DATABASE XXX SET de RÉCUPÉRATION COMPLÈTE
• Sauvegarde de la base de données à nouveau.

L'utilisation du modèle de récupération SIMPLE de ne pas arrêter l'enregistrement, mais il réduit considérablement son impact. C'est parce que le serveur ignore les informations de récupération après chaque commit.

Answer 3

Vous pouvez:

1. Démarrer une transaction.
2. Prenez un verrou en écriture sur votre table d'origine donc pas un écrit.
3. Créer une ombre de la table avec le nouveau schéma.
4. Transférer toutes les données de la table d'origine.
5. exécuter sp_rename de renommer l'ancien tableau.
6. exécuter sp_rename pour renommer la nouvelle table.
7. Enfin, vous validez la transaction.

L'avantage de cette approche est que vos lecteurs seront en mesure d'accéder à la table au cours du long processus et que vous pouvez effectuer tout type de modification de schéma dans l'arrière-plan.

Answer 4

Juste pour mettre à jour ce avec les informations les plus récentes.

Dans SQL Server 2012 cela peut maintenant être effectuée comme une opération en ligne dans les circonstances suivantes

1. Enterprise Edition
2. La valeur par défaut doit être une constante d'exécution

Pour la deuxième exigence des exemples pourrait être une constante littérale ou une fonction telle que GETDATE() qui donne la même valeur pour toutes les lignes. Par défaut NEWID() serait de ne pas se qualifier, et finiront par la mise à jour de toutes les lignes, puis il.

Pour les valeurs par défaut qui sont admissibles à SQL Server évalue et stocke le résultat comme valeur par défaut dans la colonne de métadonnées c'est donc indépendante de la contrainte par défaut qui est créé (qui peut même être supprimé s'il n'est plus nécessaire). C'est visible en sys.system_internals_partition_columns. La valeur n'a pas obtenir de l'écrit dans les lignes jusqu'à la prochaine fois ils arrivent à le mettre à jour.

Plus de détails à ce sujet ici: en ligne non nulle avec les valeurs de la colonne ajouter dans sql server 2012

Answer 5

Je pense que cela dépend de la variante SQL que vous utilisez, mais que se passerait-il si vous utilisiez l'option 2, mais à la toute fin, modifiez la table pour qu'elle ne soit pas nulle avec la valeur par défaut?

Serait-ce rapide, puisqu'il voit que toutes les valeurs ne sont pas nulles?