Options SET de ALTER DATABASE (Transact-SQL)

Cette rubrique contient la syntaxe ALTER DATABASE en rapport avec la définition d'options de base de données. Pour obtenir des informations sur une autre syntaxe ALTER DATABASE, consultez ALTER DATABASE (Transact-SQL). La mise en miroir de bases de données et les niveaux de compatibilité sont des options SET mais sont décrits dans des rubriques distinctes en raison de leur longueur. Pour plus d'informations, consultez Mise en miroir de bases de données ALTER DATABASE (Transact-SQL) et Niveau de compatibilité ALTER DATABASE (Transact-SQL).

Conventions de syntaxe de Transact-SQL

Syntaxe

ALTER DATABASE database_name 
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

<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> 
}

<auto_option> ::= 

{
    AUTO_CLOSE { ON | OFF } 
  | AUTO_CREATE_STATISTICS { ON | OFF } 
  | AUTO_SHRINK { ON | OFF } 
  | AUTO_UPDATE_STATISTICS { ON | OFF } 
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<change_tracking_option> ::=
{
  CHANGE_TRACKING { 
        = ON [ <change_tracking_option_list > ] |
    <change_tracking_option_list> |
        = OFF
  }
}

<change_tracking_option_list> ::=
{
    ( <change_tracking_option> | <change_tracking_option_list> , 
    <change_tracking_option> )
}
  
<change_tracking_option> ::=
{
  AUTO_CLEANUP = { ON | OFF } 
  | CHANGE_RETENTION = { retention_period { DAYS | HOURS | MINUTES } ]
}

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<database_mirroring_option>ALTER DATABASE Database Mirroring<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }

  | TRUSTWORTHY { ON | OFF }
}
<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<termination>::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Arguments

<auto_option>::=

Contrôle les options automatiques.

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

• AUTO_CLOSE { ON | OFF }

  • ON
    La base de données est arrêtée correctement et ses ressources sont libérées dès que le dernier utilisateur l'a quittée.

    La base de données est rouverte automatiquement lorsqu'un utilisateur tente de la réutiliser. Par exemple, en exécutant une instruction USE database_name. Si la base de données est fermée correctement et que AUTO_CLOSE a la valeur ON, elle ne se rouvre qu'au moment où un utilisateur tente de l'utiliser au redémarrage suivant du moteur de base de données. 

  • OFF
    La base de données reste ouverte après que le dernier utilisateur l'a quittée.

  L'option AUTO_CLOSE est utile pour les bases de données bureautiques, puisqu'elle permet aux fichiers de base de données d'être gérés comme des fichiers normaux. Ceux-ci peuvent être déplacés, copiés en vue d'une sauvegarde ou même transmis par messagerie électronique à d'autres utilisateurs.

  [!REMARQUE]

  Dans les versions antérieures de SQL Server, AUTO_CLOSE est un processus synchrone susceptible d'affecter les performances si l'application qui accède à la base de données établit et interrompt à plusieurs reprises les connexions au moteur de base de données. À partir de SQL Server 2005, le processus AUTO_CLOSE est asynchrone ; l'ouverture et la fermeture répétées de la base de données n'ont plus aucune incidence sur les performances.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_close_on de l'affichage catalogue sys.databases ou la propriété IsAutoClose de la fonction DATABASEPROPERTYEX.

  [!REMARQUE]

  Lorsque AUTO_CLOSE a la valeur ON, certaines colonnes de l'affichage catalogue sys.databases et la fonction DATABASEPROPERTYEX retournent la valeur NULL, car la base de données est inaccessible et qu'aucune donnée ne peut être récupérée. Pour résoudre ce problème, exécutez une instruction USE pour ouvrir la base de données.

  [!REMARQUE]

  La mise en miroir de bases de données exige AUTO_CLOSE OFF.

  Si la base de données a la valeur AUTOCLOSE = ON, une opération qui initialise un arrêt de la base de données automatique efface le cache du plan pour l'instance de SQL Server. 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. Dans SQL Server 2005 Service Pack 2, 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.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    L'optimiseur de requête crée des statistiques sur les colonnes uniques des prédicats de requête, en fonction des besoins, afin d'améliorer les plans de requêtes et les performances des requêtes. Ces statistiques de colonnes uniques sont créées lorsque l'optimiseur de requête compile les requêtes. Les statistiques de colonnes uniques sont créées uniquement sur les colonnes qui ne constituent pas déjà la première colonne d'un objet de statistiques existant.

    La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

  • OFF
    L'optimiseur de requête ne crée pas de statistiques sur les colonnes uniques des prédicats de requête lorsqu'il est en train de compiler des requêtes. Si cette option a la valeur OFF, il peut en résulter des plans de requêtes non optimisés et une dégradation des performances des requêtes.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_create_stats_on de l'affichage catalogue sys.databases ou la propriété IsAutoCreateStatistics de la fonction DATABASEPROPERTYEX.

  Pour plus d'informations, consultez la section « Utilisation des options de statistiques à l'échelle de la base de données » dans Utilisation des statistiques pour améliorer les performances des requêtes.

• AUTO_SHRINK { ON | OFF }

  • ON
    Les fichiers de base de données peuvent faire l'objet d'une réduction périodique.

    Les fichiers de données et les fichiers journaux peuvent être automatiquement réduits. AUTO_SHRINK ne réduit la taille du journal des transactions que si le mode de récupération SIMPLE est défini pour la base de données ou si le journal est sauvegardé. Si la valeur OFF est définie, les fichiers de base de données ne sont pas réduits automatiquement lors des vérifications périodiques de l'espace inutilisé.

    L'option AUTO_SHRINK provoque un compactage dès qu'un fichier comprend plus de 25 % d'espace inutilisé. Le fichier est compacté à une taille laissant 25 % d'espace inutilisé ou à sa taille initiale au moment de sa création, selon la valeur la plus élevée.

    Vous ne pouvez pas compacter une base de données en lecture seule.

  • OFF
    Les fichiers de base de données ne sont pas réduits automatiquement lors des vérifications périodiques de l'espace inutilisé.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_shrink_on de l'affichage catalogue sys.databases ou la propriété IsAutoShrink de la fonction DATABASEPROPERTYEX.

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Spécifie que l'optimiseur de requête met à jour les statistiques lorsqu'elles sont utilisées par une requête et lorsqu'elles sont peut-être obsolètes. Les statistiques deviennent obsolètes après que des opérations d'insertion, de mise à jour, de suppression ou de fusion ont modifié la distribution des données dans la table ou la vue indexée. L'optimiseur de requête détermine si les statistiques sont obsolètes en comptant le nombre de modifications de données depuis la dernière mise à jour des statistiques et en comparant le nombre de modifications à un seuil. Ce seuil est basé sur le nombre de lignes contenues dans la table ou la vue indexée.

    L'optimiseur de requête vérifie s'il existe des statistiques obsolètes avant de compiler une requête et avant d'exécuter un plan de requête mis en cache. Avant de compiler une requête, l'optimiseur de requête utilise les colonnes, les tables et les vues indexées du prédicat de requête pour identifier les statistiques susceptibles d'être obsolètes. Avant d'exécuter un plan de requête mis en cache, le moteur de base de données vérifie que le plan de requête fait référence à des statistiques à jour.

    L'option AUTO_UPDATE_STATISTICS s'applique aux statistiques créées pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l'aide de l'instruction CREATE STATISTICS. Cette option s'applique également aux statistiques filtrées.

    La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

    Utilisez l'option AUTO_UPDATE_STATISTICS_ASYNC pour spécifier si les statistiques doivent être mises à jour en mode synchrone ou asynchrone.

  • OFF
    Spécifie que l'optimiseur de requête ne met pas à jour les statistiques lorsqu'elles sont utilisées par une requête et lorsqu'elles sont peut-être obsolètes. Si cette option a la valeur OFF, il peut en résulter des plans de requêtes non optimisés et une dégradation des performances des requêtes.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_update_stats_on de l'affichage catalogue sys.databases ou la propriété IsAutoUpdateStatistics de la fonction DATABASEPROPERTYEX.

  Pour plus d'informations, consultez la section « Utilisation des options de statistiques à l'échelle de la base de données » dans Utilisation des statistiques pour améliorer les performances des requêtes.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont asynchrones. L'optimiseur de requête n'attend pas la fin des mises à jour des statistiques pour compiler les requêtes.

    Affecter la valeur ON à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

    Par défaut, l'option AUTO_UPDATE_STATISTICS_ASYNC a la valeur OFF ; l'optimiseur de requête met à jour les statistiques en mode synchrone.

  • OFF
    Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont synchrones. L'optimiseur de requête attend la fin des mises à jour des statistiques pour compiler les requêtes.

    Affecter la valeur OFF à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

  L'état de cette option peut être déterminé en examinant la colonne is_auto_update_stats_async_on dans l'affichage catalogue sys.databases.

  Pour plus d'informations sur l'utilisation des mises à jour de statistiques synchrones ou asynchrones, consultez la section « Utilisation des options de statistiques à l'échelle de la base de données » dans Utilisation des statistiques pour améliorer les performances des requêtes.

<change_tracking_option>::=

Contrôle les options de suivi des modifications. Vous pouvez activer le suivi des modifications, définir des options, modifier des options et désactiver le suivi des modifications. Pour consulter des exemples, reportez-vous à la section Exemples plus loin dans cette rubrique.

• ON
  Active le suivi des modifications pour la base de données Lorsque vous activez le suivi des modifications, vous pouvez également définir les options AUTO CLEANUP et CHANGE RETENTION.

• AUTO_CLEANUP = { ON | OFF }

  • ON
    Les informations de suivi des modifications sont supprimées automatiquement à l'issue de la période de rétention spécifiée.

  • OFF
    Les données de suivi des modifications ne sont pas supprimées de la base de données.

• CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }
  Spécifie la période minimale de conservation des informations de suivi des modifications dans la base de données. Les données sont supprimées uniquement lorsque AUTO_CLEANUP a la valeur ON.

  retention_period est un entier qui spécifie la composante numérique de la période de rétention.

  La période de rétention par défaut est 2 jours. La période de rétention minimale est 1 minute.

• OFF
  Désactive le suivi des modifications pour la base de données. Vous devez désactiver le suivi des modifications sur toutes les tables avant de le désactiver sur la base de données.

<cursor_option>::=

Contrôle les options de curseur.

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    Tout curseur ouvert au moment où une transaction est validée ou annulée est fermé.

  • OFF
    Les curseurs restent ouverts lorsqu'une transaction est validée. La restauration d'une transaction ferme tous les curseurs à l'exception de ceux définis avec la valeur INSENSITIVE ou STATIC.

  Les paramètres de niveau connexion définis à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de CURSOR_CLOSE_ON_COMMIT. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui désactive l'option CURSOR_CLOSE_ON_COMMIT pour la session (valeur OFF), lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  L'état de cette option peut être déterminé en examinant la colonne is_cursor_close_on_commit_on de l'affichage catalogue sys.databases ou la propriété IsCloseCursorsOnCommitEnabled de la fonction DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Détermine si l'étendue du curseur utilise LOCAL ou GLOBAL.

  • LOCAL
    Si LOCAL est spécifié et qu'aucun curseur n'est défini comme GLOBAL lors de sa création, le curseur a une étendue locale pour le lot, la procédure stockée ou le déclencheur dans lequel il a été créé. Le nom du curseur n'est valide que dans cette étendue. Le curseur peut être référencé par des variables de curseur locales dans le lot, la procédure stockée ou le déclencheur, ou bien par un paramètre OUTPUT d'une procédure stockée. Le curseur est désalloué implicitement à la fin du lot, de la procédure stockée ou du déclencheur, à moins d'avoir été retourné dans un paramètre OUTPUT. S'il a été retourné dans un paramètre OUTPUT, le curseur est libéré lorsque la dernière variable qui y fait référence est libérée, ou est hors de portée.

  • GLOBAL
    Si GLOBAL est spécifié et qu'aucun curseur n'est défini comme LOCAL lors de sa création, le curseur est d'étendue globale pour la connexion. Toute procédure stockée ou tout lot exécuté par la connexion peut faire référence au nom du curseur.

  Le curseur n'est libéré implicitement qu'au moment de la déconnexion. Pour plus d'informations, consultez DECLARE CURSOR (Transact-SQL).

  L'état de cette option peut être déterminé en examinant la colonne is_local_cursor_default de l'affichage catalogue sys.databases ou la propriété IsLocalCursorsDefault de la fonction DATABASEPROPERTYEX.

<database_mirroring>

Pour obtenir la description des arguments, consultez Mise en miroir de bases de données ALTER DATABASE (Transact-SQL).

<date_correlation_optimization_option> ::=

Contrôle l'option date_correlation_optimization.

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    SQL Server conserve les statistiques de corrélation entre deux tables quelconques de la base de données liées par une contrainte FOREIGN KEY et possédant des colonnes datetime. Pour plus d'informations, consultez Optimisation des requêtes qui ont accès à des colonnes datetime corrélées.

  • OFF
    Les statistiques de corrélation ne sont pas conservées.

  Pour affecter la valeur ON à DATE_CORRELATION_OPTIMIZATION, il ne doit exister aucune connexion active à la base de données, à l'exception de celle qui exécute l'instruction ALTER DATABASE. Ensuite, différentes connexions peuvent être prises en charge.

  Le paramètre actuel de cette option peut être déterminé en examinant la colonne is_date_correlation_on dans l'affichage catalogue sys.databases.

<db_encryption_option>::=

Contrôle l'état de chiffrement de la base de données.

• ENCRYPTION {ON | OFF}
  Spécifie si la base de données doit être chiffrée (ON) ou non chiffrée (OFF). Pour plus d'informations sur le chiffrement des bases de données, consultez Présentation du chiffrement transparent des données (TDE).

Lorsque le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers seront chiffrés. Tous les nouveaux groupes de fichiers hériteront de la propriété chiffrée. Si des groupes de fichiers dans la base de données ont la valeur READ ONLY, l'opération de chiffrement de la base de données échouera.

Vous pouvez voir l'état de chiffrement de la base de données en utilisant la vue de gestion dynamique sys.dm_database_encryption_keys.

<db_state_option>::=

Contrôle l'état de la base de données.

• OFFLINE
  La base de données est fermée et arrêtée correctement, puis marquée comme étant déconnectée. Tant que la base de données est hors connexion, elle ne peut pas être modifiée.

• ONLINE
  La base de données est ouverte et peut être utilisée.

• EMERGENCY
  La base de données est marquée READ_ONLY (en lecture seule), la journalisation désactivée et l'accès limité aux membres du rôle serveur fixe sysadmin. EMERGENCY est principalement utilisé à des fins de dépannage. Par exemple, une base de données marquée comme suspecte en raison d'un fichier journal corrompu peut se voir affecté l'état EMERGENCY. L'administrateur système peut alors accéder en lecture seule à la base de données. Seuls les membres du rôle serveur fixe sysadmin peuvent définir l'état EMERGENCY pour une base de données.

L'état de cette option peut être déterminé en examinant les colonnes state et state_desc dans l'affichage catalogue sys.databases ou la propriété Status de la fonction DATABASEPROPERTYEX. Pour plus d'informations, consultez États d'une base de données.

Une base de données marquée RESTORING ne peut pas se voir affecté la valeur OFFLINE, ONLINE ou EMERGENCY. Une base de données peut être à 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.

<db_update_option>::=

Contrôle si des mises à jour sont autorisées dans la base de données.

• READ_ONLY
  Les utilisateurs peuvent lire des données dans la base de données mais ils n'ont pas le droit de les modifier.

• READ_WRITE
  La base de données est accessible aux opérations de lecture et d'écriture.

Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

<db_user_access_option> ::=

Contrôle l'accès utilisateur à la base de données.

• SINGLE_USER
  Indique que l'accès à la base de données n'est autorisé qu'à un seul utilisateur à la fois. Si SINGLE_USER est spécifié et que d'autres utilisateurs sont connectés à la base de données, l'instruction ALTER DATABASE est bloquée jusqu'à ce que tous les autres utilisateurs se déconnectent de cette base de données. Pour remplacer ce comportement, examinez la clause WITH <termination>.

  La base de données demeure en mode SINGLE_USER même si l'utilisateur qui a défini l'option se déconnecte. À ce stade, un autre utilisateur (et un seul) peut se connecter à la base de données.

  Avant d'affecter la valeur SINGLE_USER à la base de données, vérifiez que l'option AUTO_UPDATE_STATISTICS_ASYNC a la valeur OFF. Si la valeur est ON, le thread d'arrière-plan utilisé pour mettre à jour les statistiques se connecte à la base de données et vous ne pourrez pas accéder à celle-ci en mode mono-utilisateur. Pour consulter l'état de cette option, interrogez la colonne is_auto_update_stats_async_on dans l'affichage catalogue sys.databases. Si l'option a la valeur ON, effectuez les tâches suivantes :

  1. Affectez la valeur OFF à AUTO_UPDATE_STATISTICS_ASYNC.

  2. Recherchez les travaux des statistiques asynchrones actifs en interrogeant la vue de gestion dynamique sys.dm_exec_background_job_queue.

  Si des travaux sont actifs, laissez ces travaux se terminer ou arrêtez-les manuellement à l'aide de KILL STATS JOB.

• RESTRICTED_USER
  RESTRICTED_USER permet uniquement aux membres du rôle de base de données fixe db_owner et des rôles serveur fixes dbcreator et sysadmin de se connecter à la base de données, mais n'en limite pas le nombre. Toutes les connexions à la base de données sont déconnectées dans la plage de temps spécifiée par la clause d'arrêt de l'instruction ALTER DATABASE. Après que la base est passée à l'état RESTRICTED_USER, toute tentative de connexion par des utilisateurs non qualifiés est refusée.

• MULTI_USER
  Tous les utilisateurs qui bénéficient des autorisations appropriées peuvent se connecter à la base de données.

L'état de cette option peut être déterminé en examinant la colonne user_access de l'affichage catalogue sys.databases ou la propriété UserAccess de la fonction DATABASEPROPERTYEX.

<external_access_option>::=

Contrôle si des ressources externes, par exemple des objets d'une autre base de données, peuvent accéder à la base de données.

• DB_CHAINING { ON | OFF }

  • ON
    La base de données peut être la source ou la cible d'une chaîne des propriétés des bases de données croisées.

  • OFF
    La base de données ne peut prendre part à un chaînage des propriétés des bases de données croisées.

  Important

  L'instance de SQL Server reconnaît ce paramètre lorsque l'option du serveur cross db ownership chaining est 0 (OFF). Lorsque cross db ownership chaining a la valeur 1 (ON), toutes les bases de données utilisateur peuvent participer aux chaînes de propriétés des bases de données croisées, quelle que soit la valeur de cette option. Cette option est configurée à l'aide de sp_configure.

  Pour définir cette option, l'autorisation CONTROL SERVER est requise sur la base de données. L'option DB_CHAINING ne peut pas être définie sur ces bases de données système : master, model et tempdb.

  L'état de cette option peut être déterminé en examinant la colonne is_db_chaining_on dans l'affichage catalogue sys.databases.

  Pour plus d'informations, consultez Chaînes de propriétés.

• TRUSTWORTHY { ON | OFF }

  • ON
    Les modules de base de données (par exemple, les procédures stockées ou les fonctions définies par l'utilisateur) qui utilisent un contexte d'emprunt d'identité peuvent accéder à des ressources en dehors de la base de données.

  • OFF
    Les modules de base de données qui utilisent l'emprunt d'identité ne peuvent pas accéder à des ressources externes à la base de données;

  TRUSTWORTHY prend la valeur OFF chaque fois que la base de données est attachée.

  Par défaut, pour toutes les bases de données système, sauf pour la base msdb, l'option TRUSTWORTHY est définie à OFF (désactivé). La valeur ne peut pas être modifiée pour les bases de données model et tempdb. Nous vous recommandons de ne jamais affecter la valeur ON (activé) à l'option TRUSTWORTHY pour la base de données master.

  Pour définir cette option, l'autorisation CONTROL SERVER est requise sur la base de données.

  L'état de cette option peut être déterminé en examinant la colonne is_trustworthy_on dans l'affichage catalogue sys.databases.

<parameterization_option> ::=

Contrôle l'option de paramétrage.

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    Les requêtes sont paramétrées en fonction du comportement par défaut de la base de données. Pour plus d'informations, consultez Paramétrage simple.

  • FORCED
    SQL Server paramètre toutes les requêtes de la base de données. Pour plus d'informations, consultez Paramétrage forcé.

  Le paramètre actuel de cette option peut être déterminé en examinant la colonne is_parameterization_forced dans l'affichage catalogue sys.databases.

<recovery_option> ::=

Contrôle les options de récupération de base de données et la vérification des erreurs d'E/S disque.

• FULL
  Assure une récupération complète après la défaillance d'un support à l'aide des sauvegardes des journaux des transactions. Si un fichier de données est endommagé, la récupération des supports peut restaurer toutes les transactions validées. Pour plus d'informations, consultez Sauvegarde en mode de récupération complète.

• BULK_LOGGED
  Fournit la récupération après la défaillance d'un support en associant des performances optimales et une utilisation minimale de l'espace réservé aux fichiers journaux pour certaines opérations en bloc ou de grande échelle. Pour plus d'informations sur les opérations journalisées en bloc, consultez Opérations pouvant faire l'objet d'une journalisation minimale. Avec le mode de récupération BULK_LOGGED, ces opérations font l'objet d'une journalisation minimale. Pour plus d'informations, consultez Sauvegarde avec le mode de récupération utilisant les journaux de transactions.

• SIMPLE
  Une stratégie de sauvegarde simple utilisant un espace de journalisation minimal est fournie. L'espace réservé aux fichiers journaux peut être automatiquement réutilisé lorsqu'il n'est plus utilisé pour la récupération des défaillances serveur. Pour plus d'informations, consultez Sauvegarde selon le mode de récupération simple.

  Important

  Le mode de récupération simple est plus facile à gérer que les deux autres modes, mais le risque de perte de données est plus élevé lorsqu'un fichier de données est endommagé. Toutes les modifications apportées depuis la dernière sauvegarde de la base de données ou de la sauvegarde différentielle de la base de données sont perdues et doivent être réintroduites manuellement.

Le mode de récupération par défaut dépend du mode de récupération de la base de données model. Pour plus d'informations sur la sélection du mode de récupération le plus approprié, consultez Choix du mode de récupération d'une base de données.

L'état de cette option peut être déterminé en examinant les colonnes recovery_model et recovery_model_desc de l'affichage catalogue sys.databases ou la propriété Recovery de la fonction DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Les pages incomplètes peuvent être détectées par le moteur de base de données.

  • OFF
    Les pages incomplètes ne peuvent pas être détectées par le moteur de base de données.

  Important

  La structure syntaxique TORN_PAGE_DETECTION ON | OFF sera supprimée dans une version ultérieure de SQL Server. Évitez d'utiliser cette structure syntaxique dans le développement de nouvelles applications et envisagez de modifier celles qui l'utilisent actuellement. Utilisez l'option PAGE_VERIFY à la place.

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  Détecte les pages de base de données endommagées résultant d'erreurs de chemin d'E/S disque. Les erreurs de chemin d'E/S disque peuvent endommager la base de données et résultent généralement d'une défaillance matérielle des disques ou de pannes d'alimentation survenant au moment de l'écriture de la page sur le disque.

  • CHECKSUM
    Calcule une somme de contrôle du contenu d'une page entière et stocke la valeur dans l'en-tête de page lorsque celle-ci est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, la somme de contrôle est recalculée et le résultat est comparé à la valeur préalablement stockée dans l'en-tête de la page. Si les valeurs diffèrent, le message d'erreur 824 (indiquant l'échec d'une somme de contrôle) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Un échec de somme de contrôle indique un problème de chemin d'E/S. Pour en déterminer la cause, vous devez examiner le matériel, les pilotes de microprogrammes, le BIOS, les pilotes de filtre (par exemple un logiciel antivirus) et d'autres composants de chemin d'E/S.

  • TORN_PAGE_DETECTION
    Enregistre un modèle spécifique de 2 bits pour chaque secteur de 512 octets dans la page de base de données de 8 kilo-octets (Ko) et le stocke dans l'en-tête de page de base de données au moment où la page est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, les bits endommagés stockés dans l'en-tête de la page sont comparés aux informations réelles du secteur concerné. Lorsque les valeurs ne concordent pas, cela signifie que seule une partie de la page a été écrite sur le disque. Dans un tel cas, le message d'erreur 824 (indiquant une erreur de page endommagée) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Les pages endommagées sont généralement détectées par la récupération de base de données s'il s'agit réellement d'une écriture de page incomplète. Toutefois, les échecs de chemin d'E/S peuvent donner lieu à tout moment à une page endommagée.

  • NONE
    Les écritures de page de base de données ne génèrent pas de valeur CHECKSUM ni TORN_PAGE_DETECTION. SQL Server ne vérifie pas une somme de contrôle ou une page endommagée au cours d'une lecture, même si l'en-tête de page comporte une valeur CHECKSUM ou TORN_PAGE_DETECTION.

  Prenez en considération les points suivants lorsque vous utilisez l'option PAGE_VERIFY :

  • Dans SQL Server 2005 et SQL Server 2008, la valeur par défaut est CHECKSUM. Dans SQL Server 2000, TORN_PAGE_DETECTION est la valeur par défaut.

  • Lorsqu'une base de données utilisateur ou système est mise à niveau vers SQL Server 2005 ou SQL Server 2008, la valeur PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) est conservée. Nous vous recommandons d'utiliser CHECKSUM. 

    [!REMARQUE]

    Dans les versions antérieures de SQL Server, l'option de base de données PAGE_VERIFY est définie par la valeur NONE pour la base de données tempdb et ne peut pas être modifiée. Dans SQL Server 2008, la valeur par défaut pour la base de données tempdb est CHECKSUM pour les nouvelles installations de SQL Server. Lorsque vous mettez à niveau une installation SQL Server, la valeur par défaut reste NONE. Il est possible de modifier cette option. Nous vous recommandons d'utiliser CHECKSUM pour la base de données tempdb.

  • Même si la valeur TORN_PAGE_DETECTION utilise moins de ressources, elle ne fournit qu'un sous-ensemble limité de la protection offerte par CHECKSUM.

  • Il est possible de définir PAGE_VERIFY sans mettre la base de données hors connexion, la verrouiller ni empêcher d'une quelconque façon la concurrence pour cette base de données.

  • CHECKSUM et TORN_PAGE_DETECTION s'excluent mutuellement. Les deux options ne peuvent pas être activées en même temps.

  Lors de la détection d'une page endommagée ou d'un échec de somme de contrôle, vous pouvez procéder à une récupération en restaurant les données ou en reconstruisant éventuellement l'index si la défaillance se limite à des pages d'index. En présence d'une erreur de somme de contrôle, pour déterminer le type de la ou des pages de données affectées, exécutez DBCC CHECKDB. Pour plus d'informations sur les options de restauration, consultez Arguments RESTORE (Transact-SQL). Bien que la restauration des données permette de résoudre le problème d'endommagement des données, la cause première, par exemple une défaillance matérielle du disque, doit être identifiée et corrigée le plus rapidement possible pour éviter que ces erreurs persistent.

  SQL Server procède à quatre nouvelles tentatives pour une lecture qui échoue avec une erreur de somme de contrôle, de page endommagée ou d'E/S disque. Si la lecture réussit lors d'une de ces tentatives, un message est écrit dans le journal des erreurs et l'exécution de la commande qui a déclenché la lecture se poursuit. Si les tentatives de lecture échouent, la commande échoue elle aussi avec le message d'erreur 824.

  Pour plus d'informations sur les messages d'erreurs 823 et 824 relatifs à une somme de contrôle, une page endommagée et des tentatives de lecture, ainsi que sur d'autres fonctionnalités d'audit d'E/S de SQL Server, consultez ce site Web de Microsoft.

  Le paramètre actuel de cette option peut être déterminé en examinant la colonne page_verify_option dans l'affichage catalogue sys.databases ou la propriété IsTornPageDetectionEnabled de la fonction DATABASEPROPERTYEX.

<service_broker_option>::=

Contrôle les options Service Broker suivantes : active ou désactive la remise de messages, définit un nouvel identificateur Service Broker ou définit les priorités de conversation sur ON ou OFF. Pour plus d'informations sur la remise de messages et les identificateurs Service Broker, consultez Gestion des identités Service Broker. Pour plus d'informations sur les niveaux de priorité des conversations, consultez Priorités de conversation. Pour consulter des exemples qui illustrent comment utiliser l'option HONOR_BROKER_PRIORITY, reportez-vous à Gestion des priorités de conversation.

• ENABLE_BROKER
  Spécifie que Service Broker est activé pour la base de données spécifiée. La remise des messages est démarrée et l'indicateur is_broker_enabled a la valeur True dans l'affichage catalogue sys.databases. La base de données conserve l'identificateur Service Broker existant.

  [!REMARQUE]

  ENABLE_BROKER requiert un verrou de base de données exclusif. Si d'autres sessions ont verrouillé des ressources dans la base de données, ENABLE_BROKER attend jusqu'à ce que les autres sessions libèrent leurs verrous. Pour activer Service Broker dans une base de données utilisateur, vérifiez qu'aucune autre session n'utilise la base de données avant d'exécuter l'instruction ALTER DATABASE SET ENABLE_BROKER, comme le fait de mettre la base de données en mode mono-utilisateur. Pour activer Service Broker dans la base de données msdb, arrêtez d'abord l'Agent SQL Server, afin que Service Broker puisse obtenir le verrou nécessaire.

• DISABLE_BROKER
  Spécifie que Service Broker est désactivé pour la base de données spécifiée. La remise des messages est arrêtée et l'indicateur is_broker_enabled a la valeur False dans l'affichage catalogue sys.databases. La base de données conserve l'identificateur Service Broker existant.

• NEW_BROKER
  Spécifie que la base de données doit recevoir un nouvel identificateur Service Broker. Dans la mesure où la base de données est considérée comme un nouveau Service Broker, toutes les conversations existantes dans la base de données sont immédiatement supprimées sans générer de message de fin de dialogue. Tout itinéraire qui fait référence à l'ancien identificateur Service Broker doit être recréé avec le nouvel identificateur.

• ERROR_BROKER_CONVERSATIONS
  Spécifie que la remise de messages Service Broker est activée. Cela conserve l'identificateur Service Broker existant pour la base de données. Service Broker met fin à toutes les conversations dans la base de données avec une erreur. De cette façon, les applications peuvent effectuer un nettoyage régulier des conversations existantes.

• HONOR_BROKER_PRIORITY {ON | OFF}

  • ON
    Les opérations d'envoi prennent en considération les niveaux de priorité assignés aux conversations. Les messages issus de conversations dont les niveaux de priorité sont élevés sont envoyés avant ceux issus de conversations dont les niveaux de priorité sont faibles.

  • OFF
    Les opérations d'envoi s'exécutent comme si toutes les conversations étaient dotées du niveau de priorité par défaut.

  Les modifications apportées à l'option HONOR_BROKER_PRIORITY prennent effet immédiatement pour les nouveaux dialogues ou les dialogues qui n'ont pas de messages en attente d'envoi. Les dialogues qui ont des messages attendant d'être envoyés lorsque l'instruction ALTER DATABASE est exécutée ne récupéreront pas le nouveau paramètre tant que certains messages pour le dialogue n'auront pas été envoyés. La durée nécessaire pour que tous les dialogues commencent à utiliser le nouveau paramètre peut varier considérablement.

  Le paramètre actuel de cette propriété est signalé dans la colonne is_broker_priority_honored dans l'affichage catalogue sys.databases.

<snapshot_option>::=

Détermine le niveau d'isolation de la transaction.

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    Active l'option Capture instantanée au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n'utilise l'isolement de capture instantanée. Une fois que cette option est activée, les transactions peuvent spécifier le niveau d'isolation de la transaction SNAPSHOT. Quand une transaction s'exécute au niveau d'isolation SNAPSHOT, toutes les instructions voient une capture instantanée des données telle qu'elle existe au début de la transaction. Si une transaction exécutée au niveau d'isolement SNAPSHOT accède à des données dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON dans toutes les bases de données ou chaque instruction de la transaction doit utiliser des indicateurs de verrouillage sur toute référence d'une clause FROM à une table de la base de données dont l'option ALLOW_SNAPSHOT_ISOLATION a la valeur OFF.

  • OFF
    Désactive l'option Capture instantanée au niveau de la base de données. Les transactions ne peuvent pas spécifier le niveau d'isolation de la transaction SNAPSHOT.

  Lorsque vous modifiez l'état de ALLOW_SNAPSHOT_ISOLATION (de ON à OFF ou inversement), ALTER DATABASE ne retourne pas le contrôle à l'appelant tant que toutes les transactions existantes dans la base de données n'ont pas été validées. Si la base de données présente déjà l'état spécifié dans l'instruction ALTER DATABASE, le contrôle est immédiatement retourné à l'appelant. Si l'instruction ALTER DATABASE n'est pas retournée rapidement, utilisez sys.dm_tran_active_snapshot_database_transactions pour déterminer si certaines transactions sont de longue durée. Si l'instruction ALTER DATABASE est annulée, la base de données conserve l'état qu'elle présentait au démarrage de ALTER DATABASE. L'affichage catalogue sys.databases indique l'état des transactions d'isolation de capture instantanée dans la base de données. Si snapshot_isolation_state_desc a la valeur IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF marque une pause de six secondes puis renouvelle l'opération.

  Vous ne pouvez pas modifier l'état de ALLOW_SNAPSHOT_ISOLATION si la base de données est hors connexion (OFFLINE).

  Si vous configurez ALLOW_SNAPSHOT_ISOLATION dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé si la base de données devient par la suite accessible en lecture et en écriture (READ_WRITE).

  Vous pouvez modifier les paramètres de ALLOW_SNAPSHOT_ISOLATION pour les bases de données master, model, msdb et tempdb. Si vous modifiez le paramètre pour la base de données tempdb, le paramètre est conservé à chaque arrêt et redémarrage de l'instance du moteur de base de données. Si vous modifiez le paramètre pour la base de données model, il devient le paramètre par défaut pour toutes les nouvelles bases de données créées à l'exception de tempdb.

  Cette option a la valeur ON par défaut pour les bases de données master et msdb.

  Le paramètre actuel de cette option peut être déterminé en examinant la colonne snapshot_isolation_state dans l'affichage catalogue sys.databases.

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    Active l'option Capture instantanée Read Committed au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n'utilise l'isolement de capture instantanée. Une fois que cette option est activée, les transactions qui définissent le niveau d'isolation de lecture validée utilisent le versioning de ligne au lieu du verrouillage. Lorsqu'une transaction est exécutée au niveau d'isolation de lecture validée, toutes les instructions voient une capture instantanée des données telles qu'elles se présentent au début de l'instruction.

  • OFF
    Désactive l'option Capture instantanée Read Committed au niveau de la base de données. Les transactions spécifiant le niveau d'isolation READ COMMITTED utilisent le verrouillage.

  Pour activer (ON) ou désactiver (OFF) READ_COMMITTED_SNAPSHOT, il ne doit exister aucune connexion active à la base de données, à l'exception de la connexion exécutant la commande ALTER DATABASE. Toutefois, il n'est pas nécessaire que la base de données soit en mode mono-utilisateur. Vous ne pouvez pas modifier l'état de cette option si la base de données est hors connexion (OFFLINE).

  Si vous configurez READ_COMMITTED_SNAPSHOT dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé lorsque la base de données devient par la suite accessible en lecture et en écriture (READ_WRITE).

  Il n'est pas possible d'affecter la valeur ON à READ_COMMITTED_SNAPSHOT pour les bases de données système master, tempdb ou msdb. Si vous modifiez le paramètre pour la base de données model, il devient le paramètre par défaut pour toutes les nouvelles bases de données créées à l'exception de tempdb.

  Le paramètre actuel de cette option peut être déterminé en examinant la colonne is_read_committed_snapshot_on dans l'affichage catalogue sys.databases.

<sql_option>::=

Contrôle les options de conformité ANSI au niveau de la base de données.

• ANSI_NULL_DEFAULT { ON | OFF }
  Détermine la valeur par défaut, NULL ou NOT NULL, d'une colonne, d'un type de données d'alias ou d'un type CLR défini par l'utilisateur dont le paramètre d'acceptation des valeurs NULL n'est pas défini de façon explicite dans les instructions CREATE TABLE ou ALTER TABLE. Les colonnes définies avec des contraintes respectent les règles de contrainte, quelle que soit la valeur de ce paramètre.

  • ON
    La valeur par défaut est NULL.

  • OFF
    La valeur par défaut est NOT NULL.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULL_DEFAULT au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULL_DEFAULT pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_NULL_DFLT_ON (Transact-SQL).

  Pour garantir la compatibilité ANSI, l'activation (ON) de l'option de base de données ANSI_NULL_DEFAULT entraîne la définition de NULL comme valeur par défaut de la base de données.

  L'état de cette option peut être déterminé en examinant la colonne is_ansi_null_default_on de l'affichage catalogue sys.databases ou la propriété IsAnsiNullDefault de la fonction DATABASEPROPERTYEX.

• ANSI_NULLS { ON | OFF }

  • ON
    Toutes les comparaisons avec une valeur NULL produisent le résultat UNKNOWN (inconnu).

  • OFF
    Les comparaisons de valeurs non-UNICODE avec une valeur NULL génèrent la valeur TRUE si les deux valeurs sont NULL.

  Important

  Dans une future version de SQL Server, ANSI_NULLS aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULLS au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULLS pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_NULLS (Transact-SQL).

  SET ANSI_NULLS doit également avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  L'état de cette option peut être déterminé en examinant la colonne is_ansi_nulls_on de l'affichage catalogue sys.databases ou la propriété IsAnsiNullsEnabled de la fonction DATABASEPROPERTYEX.

• ANSI_PADDING { ON | OFF }

  • ON
    Les chaînes sont complétées pour avoir la même longueur avant leur conversion ou insertion dans un type de données varchar ou nvarchar.

    Les espaces à droite dans les valeurs de type caractère insérées dans des colonnes varchar ou nvarchar et les zéros à droite dans les valeurs binaires insérées dans des colonnes varbinary ne sont pas supprimés. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.

  • OFF
    Les espaces à droite pour varchar ou nvarchar et les zéros pour varbinary sont supprimés.

  Lorsque cette option a la valeur OFF, elle affecte uniquement la définition des nouvelles colonnes.

  Important

  Dans une future version de SQL Server, ANSI_PADDING aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

  Les colonnes char(n) et binary(n) qui acceptent les valeurs NULL sont complétées à concurrence de la longueur de la colonne lorsque ANSI_PADDING a la valeur ON, mais les espaces et les zéros à droite sont supprimés lorsque ANSI_PADDING a la valeur OFF. Les colonnes char(n) et binary(n) qui n'acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_PADDING au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_PADDING pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_PADDING (Transact-SQL).

  Important

  Il est recommandé de toujours affecter la valeur ON à l'option ANSI_PADDING. Par ailleurs, ANSI_PADDING doit avoir la valeur ON lorsque vous créez ou manipulez des index dans des colonnes calculées ou des vues indexées.

  L'état de cette option peut être déterminé en examinant la colonne is_ansi_padding_on de l'affichage catalogue sys.databases ou la propriété IsAnsiPaddingEnabled de la fonction DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    Des erreurs ou des avertissements sont générés en présence de certaines conditions, telles qu'une division par zéro, ou lorsque des valeurs NULL apparaissent dans des fonctions d'agrégation.

  • OFF
    Aucun avertissement n'est généré et des valeurs Null sont retournées lorsque des conditions telles qu'une division par zéro se manifestent.

  SET ANSI_WARNINGS doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_NULLS défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_WARNINGS pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_WARNINGS (Transact-SQL).

  L'état de cette option peut être déterminé en examinant la colonne is_ansi_warnings_on de l'affichage catalogue sys.databases ou la propriété IsAnsiWarningsEnabled de la fonction DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Arrête une requête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution.

  • OFF
    Un message d'avertissement s'affiche si l'une de ces erreurs se produit, mais le traitement de la requête, du lot ou de la transaction se poursuit, comme s'il n'y avait pas d'erreur.

  SET ARITHABORT doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  L'état de cette option peut être déterminé en examinant la colonne is_arithabort_on de l'affichage catalogue sys.databases ou la propriété IsArithmeticAbortEnabled de la fonction DATABASEPROPERTYEX.

• COMPATIBILITY_LEVEL { 80 | 90 | 100 }
  Pour plus d'informations, consultez Niveau de compatibilité ALTER DATABASE (Transact-SQL).

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    Le résultat d'une concaténation est NULL lorsque l'un des deux opérandes est NULL. Par exemple, la concaténation de la chaîne de caractères « Ceci est  » et NULL donne la valeur NULL et non la valeur « Ceci est  ».

  • OFF
    La valeur Null est considérée comme une chaîne de caractères vide.

  CONCAT_NULL_YIELDS_NULL doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Important

  Dans une future version de SQL Server, CONCAT_NULL_YIELDS_NULL aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de CONCAT_NULL_YIELDS_NULL défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à CONCAT_NULL_YIELDS_NULL pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  L'état de cette option peut être déterminé en examinant la colonne is_concat_null_yields_null_on de l'affichage catalogue sys.databases ou la propriété IsNullConcat de la fonction DATABASEPROPERTYEX.

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    Des guillemets doubles peuvent être utilisés pour entourer des identificateurs délimités.

    Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet. Les identificateurs entre guillemets n'ont pas à respecter les règles Transact-SQL propres aux identificateurs. Ils peuvent être des mots clés et contenir des caractères généralement interdits dans les identificateurs Transact-SQL. Si un guillemet simple (') fait partie de la chaîne littérale, il pourra être représenté par un guillemet double ('').

  • OFF
    Les identificateurs ne peuvent figurer entre guillemets et doivent respecter toutes les règles Transact-SQL en matière d'identificateurs. Les chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.

  SQL Server permet également de délimiter les identificateurs par des crochets ([ ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramétrage de QUOTED_IDENTIFIER. Pour plus d'informations, consultez Identificateurs délimités (Moteur de base de données).

  Lors de la création d'une table, l'option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table, même si elle a la valeur OFF au moment de sa création.

  Les paramètres définis au niveau de la connexion à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de QUOTED_IDENTIFIER. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à QUOTED_IDENTIFIER, lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

  L'état de cette option peut être déterminé en examinant la colonne is_quoted_identifier_on de l'affichage catalogue sys.databases ou la propriété IsQuotedIdentifiersEnabled de la fonction DATABASEPROPERTYEX.

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    Une erreur est générée lors d'une perte de précision dans une expression.

  • OFF
    Les pertes de précision ne génèrent pas de messages d'erreur et le résultat est arrondi en fonction de la précision de la colonne ou de la variable contenant le résultat.

  NUMERIC_ROUNDABORT doit avoir la valeur OFF lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées

  L'état de cette option peut être déterminé en examinant la colonne is_numeric_roundabort_on de l'affichage catalogue sys.databases ou la propriété IsNumericRoundAbortEnabled de la fonction DATABASEPROPERTYEX.

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    L'activation récursive des déclencheurs AFTER est autorisée.

  • OFF
    Seule l'activation récursive directe des déclencheurs AFTER n'est pas autorisée. Pour désactiver également la récursivité indirecte des déclencheurs AFTER, affectez la valeur 0 à l'option serveur nested triggers à l'aide de sp_configure.

  [!REMARQUE]

  Seule la récursivité directe est désactivée lorsque RECURSIVE_TRIGGERS a la valeur OFF. Pour désactiver la récursivité indirecte, vous devez aussi affecter la valeur 0 à l'option serveur nested triggers.

  L'état de cette option peut être déterminé en examinant la colonne is_recursive_triggers_on de l'affichage catalogue sys.databases ou la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

WITH <termination>::=

Spécifie le(s) cas où une transaction incomplète doit être restaurée lors d'un changement d'état de la base de données. Lorsque la clause de fin est omise, l'instruction ALTER DATABASE attend indéfiniment s'il existe un verrou quelconque sur la base de données. Une seule clause de fin peut être spécifiée, à la suite des clauses SET.

[!REMARQUE]

Toutes les options de base de données n'utilisent pas la clause WITH <termination>. Pour plus d'informations, consultez le tableau du paragraphe « Configuration des options » de la section Notes.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  Indique si l'annulation intervient après le nombre de secondes spécifié ou immédiatement.

• NO_WAIT
  Indique que la modification souhaitée de l'option ou de l'état de la base de données échoue si sa réalisation immédiate suppose la validation ou l'annulation des transactions de leur propre fait.

Notes

Configuration des options

Pour récupérer les paramètres actuels des options de base de données, utilisez l'affichage catalogue sys.databases ou DATABASEPROPERTYEX. Pour obtenir la liste des valeurs par défaut assignées à la base de données lors de sa création, consultez Définition des options de base de données.

Lorsque vous définissez une option de base de données, la modification prend effet immédiatement.

Pour modifier les valeurs par défaut de l'une des options de base de données afin qu'elles s'appliquent à toutes les nouvelles bases de données créées, modifiez l'option de base de données appropriée dans la base de données model.

Toutes les options de base de données n'utilisent pas la clause WITH <termination> et ne peuvent pas être combinées avec d'autres options. Le tableau suivant répertorie ces options ainsi que l'état de l'option et d'arrêt.

Catégorie d'options	Peut être spécifiée avec d'autres options	Peut utiliser la clause WITH <termination>
<db_state_option>	Oui	Oui
<db_user_access_option>	Oui	Oui
<db_update_option>	Oui	Oui
<external_access_option>	Oui	Non
<cursor_option>	Oui	Non
<auto_option>	Oui	Non
<sql_option>	Oui	Non
<recovery_option>	Oui	Non
<database_mirroring_option>	Non	Non
ALLOW_SNAPSHOT_ISOLATION	Non	Non
READ_COMMITTED_SNAPSHOT	Non	Oui
<service_broker_option>	Oui	Non
DATE_CORRELATION_OPTIMIZATION	Oui	Oui
<parameterization_option>	Oui	Oui
<change_tracking_option>	Oui	Oui
<db_encryption>	Oui	Non

Le cache de plan pour l'instance de SQL Server est effacé par la configuration d'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. 

Exemples

A. Configuration des options d'une base de données

L'exemple suivant définit les options de mode de récupération et de vérification de pages de données pour l'exemple de base de données AdventureWorks .

USE master;
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

B. Paramétrage de la base de données avec READ_ONLY

Pour modifier l'état d'une base de données ou d'un groupe de fichiers en spécifiant READ_ONLY ou READ_WRITE, vous avez besoin d'un accès exclusif à la base de données. L'exemple ci-dessous illustre le basculement de la base de données en mode SINGLE_USER pour obtenir l'accès exclusif. L'exemple affecte ensuite à la base de données AdventureWorks l'état READ_ONLY et rend à tous les utilisateurs l'accès à la base de données.

[!REMARQUE]

Cet exemple utilise l'option de fin WITH ROLLBACK IMMEDIATE dans la première instruction ALTER DATABASE. Toutes les transactions incomplètes seront annulées et les autres connexions à l'exemple de base de données AdventureWorks immédiatement déconnectées.

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

C. Activation du niveau d'isolation de capture instantanée sur une base de données

L'exemple ci-dessous illustre l'activation de l'option d'infrastructure d'isolation de capture instantanée pour la base de données AdventureWorks.

USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO

Le jeu de résultats montre que l'infrastructure d'isolation de capture instantanée est activée.

name            snapshot_isolation_state  description
--------------- ------------------------  -----------
AdventureWorks  1                         ON

D. Activation, modification et désactivation du suivi des modifications

L'exemple ci-dessous illustre l'activation du suivi des modifications pour la base de données AdventureWorks et la définition d'une période de rétention de 4 jours.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

L'exemple suivant illustre comment modifier la période de rétention en spécifiant 3 jours.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

L'exemple ci-dessous illustre comment désactiver le suivi des modifications pour la base de données AdventureWorks.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

Historique des modifications pour le document

Mise à jour du contenu
Révision des descriptions des options AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS et AUTO_UPDATE_STATISTICS_ASYNC afin de les rendre plus précises.

Voir aussi

Référence

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

sp_configure (Transact-SQL)

sys.databases (Transact-SQL)

sys.data_spaces (Transact-SQL)

Concepts

Utilisation des statistiques pour améliorer les performances des requêtes

Configuration et gestion du suivi des modifications

Activation des niveaux d'isolement selon le versioning de ligne