Partager via

  • Article

ALTER DATABASE (Transact-SQL)

Modifie une base de données ou les fichiers et groupes de fichiers associés à celle-ci. Ajoute ou supprime des fichiers et des groupes de fichiers d'une base de données, modifie ses attributs ou ceux de ses fichiers et groupes de fichiers, modifie le classement de la base de données et définit des options de base de données. Les captures instantanées de base de données ne peuvent pas être modifiées. Pour modifier les options de base de données associées à la réplication, utilisez sp_replicationdboption.

En raison de sa longueur, la syntaxe ALTER DATABASE est répartie dans les rubriques suivantes :

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

ALTER DATABASE database_name 
{
  | MODIFY NAME = new_database_name 
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | <set_database_options>
}
[;]

<file_and_filegroup_options >::=
    <add_or_modify_files>::=
    <filespec>::= 
    <add_or_modify_filegroups>::=
    <filegroup_updatability_option>::=
<set_database_options>::=
    <optionspec>::= 
    <auto_option> ::= 
    <change_tracking_option> ::=
    <cursor_option> ::= 
    <database_mirroring_option> ::= 
    <date_correlation_optimization_option> ::=
    <db_encryption_option> ::=
    <db_state_option> ::=
    <db_update_option> ::=
    <db_user_access_option> ::=
    <external_access_option> ::=
    <parameterization_option> ::=
    <recovery_option> ::= 
    <service_broker_option> ::=
    <snapshot_option> ::=
    <sql_option> ::= 
    <termination> ::=

Arguments

  • database_name
    Nom de la base de données à modifier.

  • MODIFY NAME **=**new_database_name
    Remplace le nom de la base de données par le nom spécifié comme new_database_name.

  • COLLATE collation_name
    Spécifie le classement de la base de données. L'argument collation_name peut être un nom de classement Windows ou SQL. S'il n'est pas spécifié, le classement de l'instance de SQL Server est attribué à la base de données.

    Pour plus d'informations sur les noms de classements Windows et SQL, consultez COLLATE (Transact-SQL).

<file_and_filegroup_options >::=

Pour plus d'informations, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

<set_database_options >::=

Pour plus d'informations, consultez Options SET de ALTER DATABASE (Transact-SQL).

Notes

Pour supprimer une base de données, utilisez DROP DATABASE.

Pour diminuer la taille d'une base de données, utilisez DBCC SHRINKDATABASE.

L'instruction ALTER DATABASE doit s'exécuter en mode de validation automatique (mode de gestion des transactions par défaut) et n'est pas autorisée dans une transaction explicite ou implicite. Pour plus d'informations, consultez Transactions en mode autocommit.

Dans SQL Server 2005 ou version ultérieure, l'état d'un fichier de base de données (par exemple, en ligne ou hors connexion) est préservé indépendamment de l'état de la base de données. Pour plus d'informations, consultez États des fichiers. L'état des fichiers dans un groupe de fichiers détermine la disponibilité de tout le groupe de fichiers. Pour qu'un groupe de fichiers soit disponible, tous les fichiers du groupe doivent être en ligne. Si un groupe de fichiers est hors connexion, toute tentative d'accès au groupe par une instruction SQL échoue avec une erreur. Lorsque vous créez des plans de requêtes pour les instructions SELECT, l'optimiseur de requête évite les index non-cluster et les vues indexées qui résident dans les groupes de fichiers hors connexion. Cela permet aux instructions de s'exécuter correctement. Toutefois, si le groupe de fichiers hors connexion contient le segment de mémoire ou l'index cluster de la table cible, les instructions SELECT échouent. Par ailleurs, toute instruction INSERT, UPDATE ou DELETE qui modifie une table avec un index dans un groupe de fichiers hors connexion échoue.

Lorsqu'une base de données est dans l'état RESTORING, la plupart des instructions ALTER DATABASE échouent. La seule exception concerne la configuration des options de mise en miroir de base de données. Une base de données peut être dans l'état RESTORING durant une opération de restauration active, ou lorsqu'une opération de restauration d'un fichier de base de données ou d'un fichier journal échoue car un fichier de sauvegarde est corrompu. Pour plus d'informations, consultez Réponse aux erreurs de restauration SQL Server provoquées par des sauvegardes endommagées.

Le cache de plan pour l'instance de SQL Server est effacé en définissant une des options suivantes :

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

 

Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. Pour chaque mémoire cache effacée dans le cache de plan, le journal des erreurs SQL Server contient le message d'information suivant : « SQL Server a rencontré %d occurrence(s) de vidages de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance ou de reconfiguration de base de données ». Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle.

Modification du classement de la base de données

Avant d'appliquer un classement différent à une base de données, vérifiez que les conditions suivantes sont réunies :

  1. Vous êtes le seul utilisateur actuel de la base de données.

  2. Aucun objet lié à un schéma ne dépend du classement de la base de données.

    Si les objets suivants, qui dépendent du classement de base de données, existent dans la base de données, l'instruction ALTER DATABASEdatabase_nameCOLLATE échoue. SQL Server retourne un message d'erreur pour chaque objet bloquant l'action ALTER :

    • Fonctions et vues définies par l'utilisateur, créées à l'aide de SCHEMABINDING.

    • Colonnes calculées.

    • Contraintes CHECK.

    • Fonctions comportant des valeurs de tables qui retournent des tables avec des colonnes de caractères dont les classements sont hérités du classement par défaut de la base de données.

    Les informations de dépendance pour les entités non liées au schéma sont mises à jour automatiquement lorsque le classement de base de données est modifié. Pour plus d'informations, consultez Description des dépendances SQL.

  3. La modification du classement de la base de données ne crée pas de noms système en double parmi les noms système des objets de la base de données.

    Les espaces de noms suivants peuvent provoquer l'échec d'une modification du classement de la base de données si les doublons de noms résultent du classement modifié :

    • noms d'objets (procédure, table, déclencheur ou vue) ;

    • noms de schéma ;

    • entités de sécurité (groupe, rôle ou utilisateur) ;

    • noms de type scalaire (types système et définis par l'utilisateur) ;

    • noms de catalogues de texte intégral ;

    • noms de colonnes ou de paramètres dans un objet ;

    • noms d'index dans une table.

    Les doublons de noms résultant du nouveau classement entraînent l'échec de l'action de modification, et SQL Server retourne un message d'erreur spécifiant l'espace de noms dans lequel le doublon a été trouvé.

Affichage des informations de bases de données

Vous pouvez utiliser les affichages catalogue, les fonctions système et les procédures stockées du système pour retourner des informations sur les bases de données, les fichiers et les groupes de fichiers. Pour plus d'informations, consultez Affichage des métadonnées de la base de données.

Autorisations

Exige l'autorisation ALTER sur la base de données.

Exemples

A. Modification du nom d'une base de données

L'exemple suivant remplace le nom de la base de données AdventureWorks par Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks
Modify Name = Northwind ;
GO

B. Modification du classement d'une base de données

L'exemple suivant crée une base de données nommée testdb avec le classement SQL_Latin1_General_CP1_CI_AS, puis modifie le classement de la base de données testdb en COLLATE French_CI_AI.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO