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.
CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO
B. Suppression d'une colonne
L'exemple suivant supprime une colonne dans une table.
CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO
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.
CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO
D. Ajout d'une colonne avec une contrainte
L'exemple suivant ajoute une nouvelle colonne avec une contrainte UNIQUE.
CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO
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.
CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO
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.
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO
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é.
CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO
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.
USE AdventureWorks ;
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO
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.
CREATE TABLE dbo.cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
);
-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);
-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;
J. Suppression d'une contrainte
L'exemple suivant supprime une contrainte UNIQUE d'une table.
CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO
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.
CREATE TABLE dbo.trig_example
(id INT,
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print 'TRIG1 Error: you attempted to insert a salary > $100,000'
ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO
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.
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO
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.
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO
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.
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
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.
USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING;
L'exemple ci-dessous active le suivi des modifications ainsi que le suivi des colonnes qui sont mises à jour lors d'une modification.
USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
L'exemple ci-dessous désactive le suivi des modifications sur la table Person.Contact dans la base de données AdventureWorks :
USE AdventureWorks;
ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;
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