Modifie la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes et des contraintes, en réaffectant des partitions, en désactivant ou en activant des contraintes et des déclencheurs.
Pour ajouter de nouvelles lignes de données, utilisez l'instruction INSERT. Pour supprimer des lignes de données, utilisez les instructions DELETE ou TRUNCATE TABLE. Pour modifier des valeurs dans des lignes existantes, utilisez l'instruction UPDATE.
Si le cache de la procédure contient des plans d'exécution qui référencent la table, ALTER TABLE les marque de façon à les recompiler lors de leur prochaine exécution.
Modification de la taille d'une colonne
Vous pouvez modifier la longueur, l'échelle ou la précision d'une colonne en spécifiant une nouvelle taille pour le type de données de la colonne dans la clause ALTER COLUMN. Si des données existent dans la colonne, la nouvelle taille ne peut pas être inférieure à la taille maximale des données. En outre, la colonne ne peut pas être définie dans un index, sauf si le type de données de la colonne est varchar, nvarchar ou varbinary et si l'index n'est pas le résultat d'une contrainte PRIMARY KEY. Voir l'exemple P.
Verrous et ALTER TABLE
Les modifications spécifiées dans l'instruction ALTER TABLE sont implémentées immédiatement. Si elles nécessitent une modification des lignes de la table, ALTER TABLE met les lignes à jour. ALTER TABLE acquiert un verrou de modification du schéma sur la table pour garantir qu'aucune autre connexion ne référence même les métadonnées de la table pendant la modification, à l'exception des opérations d'index en ligne qui nécessitent un verrouillage de type SCH-M à la fin. Dans une opération ALTER TABLE…SWITCH, le verrou est acquis à la fois sur la table source et sur la table cible. Les modifications effectuées sur la table sont consignées dans un journal et peuvent être récupérées entièrement. Les modifications qui affectent toutes les lignes de tables de dimension importante, telles que la suppression d'une colonne ou l'ajout d'une colonne NOT NULL avec une valeur par défaut, peuvent demander beaucoup de temps, aussi bien pour s'exécuter que pour générer un grand nombre d'enregistrements de journal. Ces instructions ALTER TABLE doivent être exécutées avec le même soin que toute instruction INSERT, UPDATE ou DELETE qui affectent un grand nombre de lignes.
Exécution d'un plan en parallèle
Dans SQL Server 2008 Enterprise, le nombre de processeurs utilisés pour exécuter une instruction ALTER TABLE ADD (basée sur un index) CONSTRAINT ou DROP (index cluster) CONSTRAINT est déterminé par l'option de configuration max degree of parallelism et par la charge de travail en cours. Si le moteur de base de données détecte que le système est occupé, le degré de parallélisme de l'opération est automatiquement diminué avant le démarrage de l'exécution de l'instruction. Vous pouvez configurer manuellement le nombre de processeurs utilisés pour exécuter l'instruction en spécifiant l'option MAXDOP.
Tables partitionnées
Outre les opérations SWITCH qui mettent en œuvre des tables partitionnées, ALTER TABLE peut être utilisée pour modifier l'état des colonnes, des contraintes et des déclencheurs d'une table partitionnée, de la même manière que pour les tables non partitionnées. Cependant, cette instruction n'est pas utilisable pour modifier la façon dont la table elle-même est partitionnée. Pour repartitionner une table partitionnée, utilisez les instructions ALTER PARTITION SCHEME et ALTER PARTITION FUNCTION. De plus, vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.
Restrictions sur les tables comportant des vues liées au schéma
Les restrictions applicables aux instructions ALTER TABLE dans les tables comportant des vues liées au schéma sont identiques à celles qui s'appliquent à la modification de tables comportant un index simple. L'ajout d'une colonne est autorisé. Cependant, la suppression ou la modification d'une colonne intervenant dans une vue associée à un schéma n'est pas autorisée. Si l'instruction ALTER TABLE requiert la modification d'une colonne utilisée dans une vue liée au schéma, ALTER TABLE échoue et le moteur de base de données génère un message d'erreur. Pour plus d'informations sur la liaison de schéma et sur les vues indexées, consultez CREATE VIEW (Transact-SQL).
L'ajout ou la suppression de déclencheurs sur les tables de base n'est pas affecté par la création d'une vue liée au schéma comportant des références aux tables.
Index et ALTER TABLE
Tout index créé dans le cadre d'une contrainte est supprimé lorsque cette dernière est supprimée. Un index créé au moyen de l'instruction CREATE INDEX doit être supprimé à l'aide de l'instruction DROP INDEX. L'instruction ALTER INDEX peut être utilisée pour reconstruire un index faisant partie de la définition d'une contrainte ; il n'est pas nécessaire de supprimer la contrainte et de l'ajouter à nouveau à l'aide de l'instruction ALTER TABLE.
Tous les index et contraintes basés sur une colonne doivent être supprimés avant que la colonne puisse être supprimée.
Lorsqu'une contrainte qui a créé un index cluster est supprimée, les lignes de données stockées au niveau feuille de l'index cluster sont stockées dans une table non cluster. Vous pouvez supprimer l'index cluster et déplacer la table résultante vers un autre groupe de fichiers ou schéma de partition dans une transaction unique en spécifiant l'option MOVE TO. Cette option est soumise aux restrictions suivantes :
-
MOVE TO n'est pas valide pour les vues non indexées ou les index non cluster.
-
Le schéma de partition ou le groupe de fichiers doit déjà exister.
-
Si MOVE TO n'est pas spécifié, la table est placée dans le même schéma de partition ou groupe de fichiers qui a été défini pour l'index cluster.
Lorsque vous supprimez un index cluster, vous pouvez spécifier l'option ONLINE = ON de sorte que la transaction DROP INDEX ne bloque pas les requêtes ni les modifications des données sous-jacentes et des index non cluster associés.
L'option ONLINE = ON est soumise aux restrictions suivantes :
-
ONLINE = ON n'est pas valide pour les index cluster qui sont également désactivés. Les index désactivés doivent être supprimés au moyen de ONLINE = OFF.
-
Un seul index peut être supprimé à la fois.
-
ONLINE = ON n'est pas valide pour les vues indexées, les index non cluster ou les index sur des tables temporaires locales.
Pour supprimer un index cluster, l'espace disque temporaire doit être égal à la taille de l'index cluster existant. Cet espace supplémentaire est libéré dès que l'opération est terminée.
.gif) Remarque : |
|---|
|
Les options <drop_clustered_constraint_option> s'appliquent aux index cluster sur des tables ; elles ne s'appliquent pas aux index cluster sur des vues ni aux index non cluster.
|
Réplication des modifications de schéma
Par défaut, lorsque vous exécutez l'instruction ALTER TABLE sur une table publiée d'un serveur de publication SQL Server, cette modification est propagée à tous les Abonnés SQL Server. Cette fonctionnalité comporte des restrictions et peut être désactivée. Pour plus d'informations, consultez Modification du schéma dans les bases de données de publication.
Compression de données
Les tables système ne peuvent pas être activées pour la compression. Si la table est un segment, l'opération de reconstruction sera mono-thread. Pour plus d'informations sur la compression de données, consultez Création de tables et d'index compressés.
Pour évaluer la façon dont la modification de l'état de compression affecte une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings.
Les restrictions suivantes s'appliquent aux tables partitionnées :
-
Vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table possède des index non alignés.
-
La syntaxe ALTER TABLE <table> REBUILD PARTITION ... reconstruit la partition spécifiée.
-
La syntaxe ALTER TABLE <table> REBUILD WITH ... reconstruit toutes les partitions.
A. Ajout d'une nouvelle colonne
L'exemple suivant ajoute une colonne qui accepte les valeurs Null et pour laquelle aucune valeur n'est spécifiée via une définition de DEFAULT. Dans la nouvelle colonne, chaque ligne aura la valeur NULL.
B. Suppression d'une colonne
L'exemple suivant supprime une colonne dans une table.
C. Modification du type de données d'une colonne
L'exemple suivant modifie le type d'une colonne d'une table de INT en DECIMAL.
D. Ajout d'une colonne avec une contrainte
L'exemple suivant ajoute une nouvelle colonne avec une contrainte UNIQUE.
E. Ajout d'une contrainte CHECK non vérifiée à une colonne existante
L'exemple suivant ajoute une contrainte à une colonne existante de la table. La colonne comporte une valeur qui ne respecte pas la contrainte. Par conséquent, WITH NOCHECK empêche la validation de la contrainte par rapport aux lignes existantes et permet l'ajout de la contrainte.
F. Ajout d'une contrainte DEFAULT à une colonne existante
L'exemple suivant crée une table de deux colonnes et insère une valeur dans la première ; l'autre colonne conserve la valeur NULL. Une contrainte DEFAULT est alors ajoutée à la deuxième colonne. Pour vérifier si la valeur par défaut est appliquée, une autre valeur est insérée dans la première colonne, puis la table fait l'objet d'une requête.
G. Ajout de plusieurs colonnes avec des contraintes
L'exemple suivant ajoute plusieurs colonnes avec des contraintes définies. La première nouvelle colonne a une propriété IDENTITY. Chaque ligne de la table a de nouvelles valeurs incrémentielles dans la colonne d'identité.
H. Ajout d'une colonne acceptant les valeurs NULL, avec des valeurs par défaut
L'exemple suivant ajoute une colonne qui accepte les valeurs Null, avec une définition de DEFAULT. Il utilise l'option WITH VALUES pour spécifier des valeurs pour chaque ligne existante de la table. Si l'option WITH VALUES n'est pas utilisée, chaque ligne a la valeur Null dans la nouvelle colonne.
I. Désactivation et réactivation d'une contrainte
L'exemple suivant désactive une contrainte qui limite les salaires acceptés dans les données. NOCHECK CONSTRAINT est utilisé avec ALTER TABLE pour désactiver la contrainte et autoriser une insertion qui ne respecte normalement pas la contrainte. CHECK CONSTRAINT réactive la contrainte.
J. Suppression d'une contrainte
L'exemple suivant supprime une contrainte UNIQUE d'une table.
K. Basculement de partitions entre des tables
L'exemple suivant crée une table partitionnée, en partant du principe que le schéma de partition myRangePS1 est déjà créé dans la base de données. Une table non partitionnée est ensuite créée avec la même structure que la table partitionnée et dans le même groupe de fichiers que PARTITION 2 de la table PartitionTable. Les données de PARTITION 2 de la table PartitionTable sont ensuite basculées dans la table NonPartitionTable.
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO
L. Désactivation et réactivation d'un déclencheur
L'exemple suivant utilise l'option DISABLE TRIGGER de l'instruction ALTER TABLE pour désactiver le déclencheur et autoriser une insertion qui ne respecte normalement pas le déclencheur. ENABLE TRIGGER est ensuite utilisé pour réactiver le déclencheur.
M. Création d'une contrainte PRIMARY KEY avec des options d'index
L'exemple suivant crée la contrainte PRIMARY KEY PK_TransactionHistoryArchive_TransactionID et définit les options FILLFACTOR, ONLINE et PAD_INDEX. L'index cluster généré portera le même nom que la contrainte.
N. Suppression d'une contrainte PRIMARY KEY en mode ONLINE
L'exemple suivant supprime une contrainte PRIMARY KEY avec l'option ONLINE qui a la valeur ON.
O. Ajout et suppression d'une contrainte FOREIGN KEY
L'exemple suivant crée la table ContactBackup, puis la modifie en ajoutant d'abord une contrainte FOREIGN KEY qui référence la table Contact, puis en supprimant la contrainte FOREIGN KEY.
P. Modification de la taille d'une colonne
L'exemple suivant augmente la taille d'une colonne varchar ainsi que la précision et l'échelle d'une colonne decimal. Dans la mesure où les colonnes contiennent des données, la taille de colonne peut uniquement être augmentée. Remarquez aussi que col_a est défini dans un index unique. La taille de col_a peut encore être augmentée, car le type de données est varchar et l'index n'est pas le résultat d'une contrainte PRIMARY KEY.
IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL
DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
Q. Autorisation de l'escalade de verrous sur les tables partitionnées
L'exemple suivant autorise l'escalade de verrous au niveau de la partition sur une table partitionnée. Si la table n'est pas partitionnée, l'escalade de verrous continue jusqu'au niveau TABLE.
ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO)
GO
R. Configuration du suivi des modifications sur une table
L'exemple ci-dessous active le suivi des modifications sur la table Person.Contact dans la base de données AdventureWorks.
L'exemple ci-dessous active le suivi des modifications ainsi que le suivi des colonnes qui sont mises à jour lors d'une modification.
L'exemple ci-dessous désactive le suivi des modifications sur la table Person.Contact dans la base de données AdventureWorks :
S. Modification d'une table pour modifier la compression
L'exemple suivant modifie la compression d'une table non partitionnée. Le segment de mémoire ou l'index cluster sera reconstruit. Si la table est un segment, tous les index non cluster associés à la table sont reconstruits.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
L'exemple suivant modifie la compression d'une table partitionnée. La syntaxe REBUILD PARTITION = 1 entraîne uniquement la reconstruction de la partition numéro 1.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
La même opération utilisant la syntaxe de remplacement suivante provoque la reconstruction de toutes les partitions dans la table.
ALTER TABLE PartitionTable1
REBUILD PARTITION ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
Pour obtenir d'autres exemples de compression de données, consultez Création de tables et d'index compressés.
T. Ajout d'une colonne fragmentée
Les exemples suivants illustrent l'ajout et la modification de colonnes fragmentées dans la table T1. Le code pour créer la table T1 se présente comme suit.
CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO
Pour ajouter une colonne fragmentée supplémentaire C5, exécutez l'instruction suivante.
ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO
Pour convertir la colonne non fragmentée C4 en colonne fragmentée, exécutez l'instruction suivante.
ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO
Pour convertir la colonne fragmentée C4 en colonne non fragmentée, exécutez l'instruction suivante.
ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO
U. Ajout d'un jeu de colonnes
Les exemples suivants montrent comment ajouter une colonne à la table T2. Un jeu de colonnes ne peut pas être ajouté à une table qui contient déjà des colonnes fragmentées. Le code permettant de créer la table T2 est comme suit.
CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO
Les trois instructions suivantes ajoutent un jeu de colonnes nommé CS, puis changent les colonnes C2 et C3 en SPARSE.
ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO
ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ;
GO
ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO