Modifier une table SQL Server avec ALTER TABLE

Exemples de scripts T-SQL pour modifier des tables SQL Server en utilisant les instructions de type ALTER TABLE.

Comment modifier une table SQL Server avec un script simple, en utilisant l’instruction ALTER ? Connectez-vous simplement au serveur qui héberge l’instance en utilisant SSMS par exemple. Puis à la base de données et exécutez une requête ALTER TABLE SQL Server. L’objectif ici est de mettre à jour une table déjà existante pour adapter sa structure par exemple. Ou les contraintes associées à la table.

De plus, le mot-clé ALTER en SQL vous permet d’ajouter ou de supprimer une colonne. Utilisez ALTER deux fois, la première fois pour spécifier la table cible et la deuxième fois pour spécifier la colonne à modifier.

Avant de commencer, notez que techniquement lorsque nous utilisons une instruction ALTER TABLE, il s’agit de plusieurs types d’objets différents :

Ensemble de Colonnes : Modifier ou ajouter un ensemble de colonnes pour des colonnes éparses.
Stockage de Colonnes : Changer les propriétés de stockage d’une colonne (par exemple, de ROW à COLUMNSTORE).
Colonnes : Ajouter, modifier ou supprimer des colonnes.
Contraintes : Ajouter, modifier ou supprimer des contraintes. Cela inclut les clés primaires, les clés étrangères, les contraintes uniques et les contraintes de vérification.
Nom de Répertoire FileTable : Changer le nom de répertoire pour un FileTable.
Index : Bien que vous ne puissiez pas directement créer ou supprimer des index avec ALTER TABLE, vous pouvez les affecter indirectement en ajoutant ou en supprimant des colonnes ou des contraintes.
Changer de Partitions : Échanger une partition de table avec la partition d’une autre table.
Nom de la Table : Changer le nom de la table.
Partitionnement de Table : Modifier la structure de partition d’une table.
Groupe de Fichiers Texte/Image : Migrer des données texte ou image vers un autre groupe de fichiers.

Dans cet article, nous ne couvrirons que quelques exemples de bases.

Table des matières

1. Créer un exemple de table SQL Server à modifier en utilisant des scripts

Utilisons une table inspirée de la table des ventes utilisée dans ce premier tutoriel sur comment créer une table SQL Server avec une clé primaire. Cependant, elle aura une structure différente, le code est disponible ci-dessous. Copiez et collez-le dans SSMS pour créer la table d’exemple, elle sera nécessaire dans les requêtes suivantes.

-- Créer la table des ventes à modifier avec ALTER
CREATE TABLE [dbo].[SALES] 
(
   [Year]         INT NULL, 
   [MonthName]    NVARCHAR(50) NULL, 
   [MonthCurrent] BIT NULL,
   [NumberMonth]  TINYINT NULL
);
GO

2. Script pour modifier la longueur d’une colonne dans SQL Server

Dans le script ci-dessous, deux lignes sont utilisées, la première est l’instruction ALTER TABLE. Et l’autre mot-clé est ALTER COLUMN pour changer explicitement la longueur de la colonne du nom du mois.

-- Changer la longueur à 100, initialement c'était 50
ALTER TABLE [dbo].[SALES]
	ALTER COLUMN [MonthName] NVARCHAR(100);
GO

Veuillez noter que si vous souhaitez réduire la taille de la colonne et que des données sont déjà présentes, alors le système de base de données effectue une vérification pour vérifier la taille des données. Ainsi, si vous réduisez la taille à 20 caractères, c’est correct. Mais si vous insérez des noms de mois (jusqu’à 9 caractères pour septembre), alors le système renvoie une erreur.

En effet, veuillez noter que le contenu doit être compatible avec la nouvelle longueur. Si la longueur diminue, alors l’opération nécessite une étape supplémentaire qui est de sauvegarder le contenu dans une autre table ou de raccourcir la longueur du contenu.

ALTER TABLE [dbo].[SALES]
	ALTER COLUMN [MonthName] NVARCHAR(20);

insert into [dbo].[SALES] ([MonthName]) values ('Janvier'); 
insert into [dbo].[SALES] ([MonthName]) values ('Février'); 
insert into [dbo].[SALES] ([MonthName]) values ('Mars'); 
insert into [dbo].[SALES] ([MonthName]) values ('Avril'); 
insert into [dbo].[SALES] ([MonthName]) values ('Mai'); 
insert into [dbo].[SALES] ([MonthName]) values ('Juin'); 
insert into [dbo].[SALES] ([MonthName]) values ('Juillet'); 
insert into [dbo].[SALES] ([MonthName]) values ('Août'); 
insert into [dbo].[SALES] ([MonthName]) values ('Septembre'); 
insert into [dbo].[SALES] ([MonthName]) values ('Octobre'); 
insert into [dbo].[SALES] ([MonthName]) values ('Novembre'); 
insert into [dbo].[SALES] ([MonthName]) values ('Décembre');

ALTER TABLE [dbo].[SALES] ALTER COLUMN [MonthName] NVARCHAR(5);

L’erreur générée est la suivante :

Msg 2628, Niveau 16, État 1, Ligne 29 Les données de type chaîne ou binaire seraient tronquées dans la table ‘Expert-Only.dbo.SALES’, colonne ‘MonthName’. Valeur tronquée : »

En effet, la taille d’une colonne SQL ne peut être réduite plus que le contenu actuel.

Lisez aussi : À propos des scripts de bases des tables SQL Server, consultez également ce deuxième article sur comment vider une table SQL Server.

3. Script SQL Server pour ajouter ou supprimer une colonne

La commande ALTER TABLE permet également d’ajouter ou de supprimer des colonnes d’une table. Ce script T-SQL ci-dessous montre comment ajouter et supprimer une seule colonne de la table des ventes.

-- Script pour ajouter une colonne à la table des ventes
ALTER TABLE [dbo].[SALES] 
	ADD 	NewColumnA VARCHAR(10) NULL;
GO

-- Script pour supprimer une colonne d'une table SQL Server 
ALTER TABLE [dbo].[SALES] 
	DROP COLUMN NewColumnA;
GO

Les 4 requêtes exécutées ci-dessus effectuent les opérations suivantes :

La première requête du script ajoute une seule colonne nommée NewColumnA à la table SALES en utilisant l’instruction ALTER TABLE combinée avec le mot-clé ADD. Le type de données de la nouvelle colonne est VARCHAR(10) et elle autorise les valeurs NULL.
La deuxième section du code T-SQL supprime une seule colonne d’une table SQL Server en utilisant le mot-clé DROP COLUMN.

4. Script SQL Server pour ajouter ou supprimer plusieurs colonnes à la fois

Ci-dessous se trouvent deux scripts similaires, mais cette fois-ci ils permettent de gérer plusieurs colonnes en même temps.

-- Ajouter plusieurs colonnes à la table des ventes
ALTER TABLE [dbo].[SALES] 
ADD 	NewColumnB VARCHAR(10) NULL, 
	NewColumnC INT NULL;
GO


-- Script pour supprimer plusieurs colonnes d'une table SQL Server 
ALTER TABLE [dbo].[SALES]  
	DROP COLUMN NewColumnB, NewColumnC;
GO

Le premier ajoute plusieurs colonnes à une table également avec le mot-clé ADD. Dans ce cas, deux colonnes nommées NewColumnB et NewColumnC sont ajoutées à la table.
Et la dernière requête supprime plusieurs colonnes de la table. Dans ce cas, les colonnes NewColumnB et NewColumnC qui ont été ajoutées dans la deuxième section sont supprimées.

Modifier les tables SQL Server est simple si elles sont vides

En conclusion, modifier une table SQL Server et ses colonnes avec un script est un processus simple qui implique l’utilisation de l’instruction ALTER TABLE.

La partie délicate de la modification des colonnes intervient lorsqu’il s’agit de changer la structure d’une colonne existante avec des données déjà chargées. Et encore plus complexe est lors du changement, comme la réduction de la taille d’une colonne qui est également une clé étrangère pour une autre table.

Pour aller plus loin, consultez la section fonction SQL Server pour créer du code réutilisable qui renvoie une valeur à partir de tables ou de fonctions et procédures SQL intégrées.