Comment puis-je ajouter une colonne à une grande table sql server

La seule vraie solution pour un temps de disponibilité continu est redondance.

je reconnais la réponse de @Nestor que l'ajout d'une nouvelle colonne ne devrait pas prendre longtemps dans SQL Server, mais néanmoins, il pourrait encore s'agir d'une panne qui n'est pas acceptable sur un système de production. Une autre solution consiste à effectuer le changement dans un système parallèle, puis, une fois l'opération terminée, à remplacer l'ancien par le nouveau.

Par exemple, si vous avez besoin d'ajouter une colonne, vous pouvez créer une copie de le tableau, puis ajouter la colonne à la copie, et ensuite utiliser sp_rename() pour déplacer l'ancienne table de côté et la nouvelle table en place.

si vous avez des contraintes d'intégrité référentielle pointant vers cette table, cela peut rendre le swap encore plus délicat. Vous devez probablement laisser tomber les contraintes brièvement pendant que vous échangez les tables.

pour certains types de mises à jour complexes, vous pouvez complètement dupliquer la base de données sur un serveur hôte séparé. Une fois que c'est prêt, il suffit d'échanger les entrées DNS pour les deux serveurs et voilà!

j'ai soutenu une société de bourse dans les années 1990 qui a couru trois dupliqué serveurs de bases de données en tout temps. Que comment ils pourraient mettre en œuvre des un serveur, tout en conservant un serveur de production et un basculement serveur. Leurs opérations ont eu un procédure standard de rotation des trois machines en production, de basculement, d'entretien et de rôles tous les jour. Quand ils avaient besoin d' mise matériel, logiciel, ou modifier le schéma de base de données, il a fallu trois jours pour propagent le changement à travers leurs serveurs, mais ils pourraient le faire sans interruption de service. Merci tous pour la redondance.

" ajouter la colonne et ensuite effectuer des mises à jour relativement petites lots pour remplir la colonne avec une valeur par défaut. Cela devrait empêcher tout ralentissement perceptible"

et après cela vous devez mettre la colonne à NOT NULL qui va se déclencher dans une grosse transaction. Donc tout va fonctionner vraiment vite jusqu'à ce que vous le faites donc vous avez probablement gagné très peu vraiment. Je ne sais par expérience de première main.

vous pourriez vouloir renommer le table courante de X à Y. Vous pouvez le faire avec cette commande sp_RENAME '[OldTableName]' , '[NewTableName]'.

Recréer la table en tant que X avec le nouveau jeu de colonnes NON NULLES et puis insertion de lot à partir de Y à X et comprennent une valeur par défaut dans votre insertion de la nouvelle colonne ou de placer une valeur par défaut sur la nouvelle colonne lorsque vous recréer la table X.

j'ai fait ce type de changement sur une table avec des centaines de millions de lignes. Il a fallu plus d'une heure, mais il n'a en rien gâché notre trans journal. Quand j'ai essayé de changer la colonne de pas NULL avec toutes les données dans la table, il a fallu plus de 20 heures avant que je tue le processus.

avez-vous testé l'ajout d'une colonne la remplissant avec des données et en mettant la colonne à NOT NULL?

donc à la fin je ne pense pas qu'il y ait une balle magique.

Je ne voulais pas que la colonne autorise les nulls, ce qui voudrait dire que j'aurais besoin d'une valeur par défaut.

Ajouter un NOT NULL colonne DEFAULT contrainte à une table de n'importe quel nombre de lignes (même des milliards) est devenu beaucoup démarrage plus facile dans SQL Server 2012 (mais seulement pour Enterprise Edition) car ils ont permis qu'il soit une opération en ligne (dans la plupart des cas) où, pour les lignes existantes, la valeur sera lue à partir de méta-données et pas réellement stockée dans le ligne jusqu'à ce que la ligne soit mise à jour, ou l'index groupé est reconstruit. Plutôt que de paraphraser davantage, voici la section pertinente de la page MSDN pour MODIFIER LA TABLE:

ajouter des colonnes non nulles comme une opération en ligne

à partir de SQL Server 2012 Enterprise Edition, ajouter une colonne NOT NULL avec une valeur par défaut est une opération en ligne lorsque la valeur par défaut est un constante d'exécution. Cela signifie que l' l'opération est effectuée presque instantanément quel que soit le nombre de lignes dans le tableau. Cela est dû au fait que les lignes existantes dans la table ne sont pas mises à jour pendant l'opération; à la place, la valeur par défaut est stockée uniquement dans les métadonnées de la table et la valeur est recherchée au besoin dans les requêtes qui accèdent à ces lignes. Ce comportement est automatique; aucune syntaxe supplémentaire n'est requise pour mettre en œuvre l'opération en ligne au-delà de la syntaxe ADD COLUMN. Une constante d'exécution est une expression qui produit la même valeur à l'exécution pour chaque ligne du tableau quel que soit son déterminisme. Par exemple, l'expression constante "mes données temporaires", ou la fonction système GETUTCDATETIME() sont des constantes d'exécution. En revanche, les fonctions NEWID() ou NEWSEQUENTIALID () ne sont pas des constantes d'exécution car une valeur unique est produite pour chaque ligne du tableau. L'ajout D'une colonne NOT NULL avec une valeur par défaut qui n'est pas une constante d'exécution est toujours effectué hors ligne et une serrure exclusive (SCH-M) est acquise pour la durée de l'opération.

alors que les lignes existantes font référence à la valeur stockée dans les métadonnées, la valeur par défaut est stockée sur la ligne pour toute nouvelle ligne qui est insérée et ne spécifie pas une autre valeur pour la colonne. La valeur par défaut stockée dans les métadonnées est déplacée vers une ligne existante lorsque la ligne est mise à jour (même si la colonne actuelle n'est pas spécifiée dans L'instruction UPDATE), ou si la table ou l'index groupé est reconstruit.

colonnes de type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, la géométrie, la géographie, ou CLR UDTS, ne peut pas être ajouté dans une opération en ligne. Une colonne ne peut pas être ajoutée en ligne si, ce faisant, la taille maximale possible de la rangée dépasse la limite de 8 060 octets. La colonne est ajoutée comme opération hors ligne dans ce cas.

sélectionnez dans une nouvelle table et renommez. Exemple, ajout de la colonne i Au Tableau A:

select *, 1 as i
into A_tmp
from A_tbl

//Add any indexes here

exec sp_rename 'A_tbl', 'A_old'
exec sp_rename 'A_tmp', 'A_tbl'

devrait être rapide et ne touchera pas votre journal des transactions comme l'insertion dans les lots pourrait. (Je viens de le faire aujourd'hui avec une table de rang de 70 millions en moins de 2 min).

Vous pouvez l'envelopper dans une transaction si vous avez besoin d'une opération en ligne (quelque chose pourrait changer dans le tableau entre le select et le renomme).

une autre technique consiste à ajouter la colonne à une nouvelle table associée (supposons une relation un-à-un que vous pouvez appliquer en donnant au FK un index unique). Vous pouvez ensuite renseigner en lots et ensuite vous pouvez ajouter la jointure de la table où vous voulez que les données apparaissent. Remarque je tiens seulement à être pris en compte pour une colonne que je ne voudrais pas utiliser dans chaque requête sur la table d'origine ou si l'enregistrement de la largeur de ma table d'origine était trop grande ou si j'étais en ajoutant plusieurs colonnes.