Définition des options de base de données

Article
12/15/2008

Mis à jour : 5 décembre 2005

Pour chaque base de données, il est possible de définir plusieurs options au niveau de la base de données afin de déterminer ses caractéristiques. Ces options sont propres à une base de données et n'affectent pas les autres bases. Ces options de base de données deviennent les valeurs par défaut quand vous créez une base de données et elles peuvent être modifiées au moyen de la clause SET de l'instruction ALTER DATABASE. En outre, SQL Server Management Studio permet de définir la plupart de ces options.

Remarque :
Vous pouvez définir les paramètres à l'échelle du serveur au moyen de la procédure stockée système sp_configure ou de SQL Server Management Studio. Pour plus d'informations, consultez Définition des options de configuration de serveur. Les instructions SET permettent de définir les paramètres de niveau connexion. Pour plus d'informations, consultez Options SET.

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. Par exemple, si vous voulez que la valeur par défaut de l'option de base de données AUTO_SHRINK soit « ON » (Activée) pour toutes les nouvelles bases de données à venir, donnez à l'option AUTO_SHRINK de model la valeur ON.

Quand une option de base de données a été définie, un point de contrôle est automatiquement créé de façon à ce que la modification entre en vigueur immédiatement. Pour plus d'informations, consultez CHECKPOINT (Transact-SQL).

Options de base de données

Les tableaux ci-dessous répertorient les options de base de données définies lors de la création d'une base de données et leurs valeurs par défaut. Pour une description complète de ces options, consultez ALTER DATABASE (Transact-SQL).

Options automatiques

Contrôle de certains comportements automatiques.

Option	Description	Valeur par défaut
AUTO_CLOSE	Si la valeur ON est spécifiée, la base de données est arrêtée proprement et ses ressources sont libérées dès que le dernier utilisateur la quitte. La base de données est rouverte automatiquement lorsqu'un utilisateur tente de l'utiliser à nouveau. Si la valeur OFF est spécifiée, la base de données reste ouverte après que le dernier utilisateur l'a quittée.	ON pour toutes les bases de données lorsque vous utilisez Microsoft SQL Server 2000 Desktop Engine ou SQL Server 2005 Express Edition, et OFF pour toutes les autres versions, quel que soit le système d'exploitation.
AUTO_CREATE_STATISTICS	Si cette option a la valeur ON, des statistiques sont créées automatiquement sur les colonnes utilisées dans un prédicat. Si elle a la valeur OFF, les statistiques ne sont pas créées automatiquement et peuvent au lieu de cela être créées manuellement.	ON
AUTO_UPDATE_STATISTICS	Si cette option a la valeur ON, les statistiques manquantes requises par une requête à des fins d'optimisation sont générées automatiquement lors de l'optimisation de la requête. Si la valeur OFF est spécifiée, les statistiques doivent être créées manuellement. Pour plus d'informations, consultez Statistiques d'index.	ON
AUTO_SHRINK	Si cette option a la valeur ON, les fichiers de base de données peuvent faire l'objet d'un compactage périodique. Tant les fichiers de données que les journaux des transactions peuvent être automatiquement compactés par SQL Server. 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 elle a la valeur OFF, les fichiers de base de données ne sont pas compressés automatiquement lors des vérifications périodiques de l'espace inutilisé.	OFF

Options de curseur

Contrôle du comportement et de l'étendue du curseur.

Option	Description	Valeur par défaut
CURSOR_CLOSE_ON_COMMIT	Si la valeur ON est spécifiée, tout curseur ouvert au moment où une transaction est validée ou annulée est fermé. Si la valeur OFF est spécifiée, les curseurs restent ouverts lorsqu'une transaction est validée. L'annulation d'une transaction ferme tous les curseurs à l'exception de ceux définis avec la valeur INSENSITIVE ou STATIC.	OFF
CURSOR_DEFAULT	Si LOCAL est défini et qu'aucun curseur n'est défini comme GLOBAL lors de sa création, le curseur est d'étendue locale pour le lot d'instructions, 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. 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 d'instructions exécuté par la connexion peut faire référence au nom du curseur.	GLOBAL

CURSOR_CLOSE_ON_COMMIT

Si la valeur ON est spécifiée, tout curseur ouvert au moment où une transaction est validée ou annulée est fermé.

Si la valeur OFF est spécifiée, les curseurs restent ouverts lorsqu'une transaction est validée. L'annulation d'une transaction ferme tous les curseurs à l'exception de ceux définis avec la valeur INSENSITIVE ou STATIC.

OFF

CURSOR_DEFAULT

Si LOCAL est défini et qu'aucun curseur n'est défini comme GLOBAL lors de sa création, le curseur est d'étendue locale pour le lot d'instructions, 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.

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 d'instructions exécuté par la connexion peut faire référence au nom du curseur.

GLOBAL

Options de disponibilité de base de données

Contrôle des aspects suivants : si la base de données est en ligne ou hors connexion, si elle est accessible en lecture seule et les utilisateurs qui peuvent s'y connecter.

Option	Description	Valeur par défaut
OFFLINE \| ONLINE \| EMERGENCY	Si la valeur OFFLINE est spécifiée, la base de données est fermée et arrêtée proprement, puis marquée comme étant hors connexion. Si la valeur ONLINE est spécifiée, la base de données est ouverte et disponible. Si la valeur EMERGENCY est indiquée, la base de données est marquée comme READ_ONLY, l'enregistrement est désactivé et l'accès est limité aux membres du rôle de serveur fixe sysadmin.	ONLINE
READ_ONLY \| READ_WRITE	Si READ_ONLY est spécifié, les utilisateurs peuvent lire des données dans la base de données, mais ne peuvent pas les modifier. Si READ_WRITE est spécifié, la base de données est accessible en lecture et en écriture.	READ_WRITE
SINGLE_USER \| RESTRICTED_USER \| MULTI_USER	Si SINGLE_USER est spécifié, un seul utilisateur à la fois peut se connecter à la base de données. Toutes les autres connexions utilisateur sont interrompues. Si RESTRICTED_USER est spécifié, seuls les membres du rôle de base de données fixe db_owner et des rôles de serveur fixes dbcreator et sysadmin peuvent se connecter à la base de données, leur nombre n'étant pas limité. MULTI_USER permet à tous les utilisateurs bénéficiant des autorisations appropriées de se connecter à la base de données.	MULTI_USER

OFFLINE | ONLINE | EMERGENCY

Si la valeur OFFLINE est spécifiée, la base de données est fermée et arrêtée proprement, puis marquée comme étant hors connexion.

Si la valeur ONLINE est spécifiée, la base de données est ouverte et disponible.

Si la valeur EMERGENCY est indiquée, la base de données est marquée comme READ_ONLY, l'enregistrement est désactivé et l'accès est limité aux membres du rôle de serveur fixe sysadmin.

ONLINE

READ_ONLY | READ_WRITE

Si READ_ONLY est spécifié, les utilisateurs peuvent lire des données dans la base de données, mais ne peuvent pas les modifier.

Si READ_WRITE est spécifié, la base de données est accessible en lecture et en écriture.

READ_WRITE

SINGLE_USER | RESTRICTED_USER | MULTI_USER

Si SINGLE_USER est spécifié, un seul utilisateur à la fois peut se connecter à la base de données. Toutes les autres connexions utilisateur sont interrompues.

Si RESTRICTED_USER est spécifié, seuls les membres du rôle de base de données fixe db_owner et des rôles de serveur fixes dbcreator et sysadmin peuvent se connecter à la base de données, leur nombre n'étant pas limité.

MULTI_USER permet à tous les utilisateurs bénéficiant des autorisations appropriées de se connecter à la base de données.

MULTI_USER

Options d'optimisation des corrélations de dates

Contrôle de l'option date_correlation_optimization.

Option	Description	Valeur par défaut
DATE_CORRELATION_OPTIMIZATION	Si ON est spécifié, SQL Server conserve des statistiques de corrélation entre deux tables de la base de données qui sont liées par une contrainte FOREIGN KEY et comportent des colonnes datetime. Si la valeur OFF est spécifiée, les statistiques de corrélation ne sont pas conservées.	OFF

DATE_CORRELATION_OPTIMIZATION

Si ON est spécifié, SQL Server conserve des statistiques de corrélation entre deux tables de la base de données qui sont liées par une contrainte FOREIGN KEY et comportent des colonnes datetime.

Si la valeur OFF est spécifiée, les statistiques de corrélation ne sont pas conservées.

OFF

Options d'accès externe

Contrôle de la possibilité d'accès à la base de données par des ressources externes, telles que des objets d'une autre base de données.

Option	Description	Valeur par défaut
DB_CHAINING	Si ON est spécifié, la base de données peut être la source ou la cible d'un chaînage des propriétés des bases de données croisées. Si OFF est spécifié, la base de données ne peut pas être impliquée dans un chaînage des propriétés des bases de données croisées.	OFF
TRUSTWORTHY	Si cette option a la valeur ON, les modules de base de données (tels que des procédures stockées ou des fonctions définies par l'utilisateur) utilisant un contexte d'emprunt d'identité ont accès à des ressources extérieures à la base de données. Si elle a la valeur OFF, l'accès aux ressources extérieures à la base de données dans un contexte d'emprunt d'identité est interdit. TRUSTWORTHY prend la valeur OFF chaque fois que la base de données est attachée.	OFF

DB_CHAINING

Si ON est spécifié, la base de données peut être la source ou la cible d'un chaînage des propriétés des bases de données croisées.

Si OFF est spécifié, la base de données ne peut pas être impliquée dans un chaînage des propriétés des bases de données croisées.

OFF

TRUSTWORTHY

Si cette option a la valeur ON, les modules de base de données (tels que des procédures stockées ou des fonctions définies par l'utilisateur) utilisant un contexte d'emprunt d'identité ont accès à des ressources extérieures à la base de données.

Si elle a la valeur OFF, l'accès aux ressources extérieures à la base de données dans un contexte d'emprunt d'identité est interdit.

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

OFF

Option de paramétrage

Contrôle de l'option de paramétrage.

Option	Description	Valeur par défaut
PARAMETERIZATION	Si SIMPLE est spécifié, les requêtes sont paramétrées conformément au comportement par défaut de la base de données. Si FORCED est spécifié, SQL Server paramètre toutes les requêtes de la base de données.	SIMPLE

PARAMETERIZATION

Si SIMPLE est spécifié, les requêtes sont paramétrées conformément au comportement par défaut de la base de données.

Si FORCED est spécifié, SQL Server paramètre toutes les requêtes de la base de données.

SIMPLE

Options de récupération

Contrôle du mode de récupération de la base de données.

Option	Description	Valeur par défaut
RECOVERY	Si la valeur FULL est spécifiée, en cas d'échec d'un support, une restauration complète est effectuée au moyen des sauvegardes des journaux de transactions. Si un fichier de données est endommagé, la récupération des supports peut restaurer toutes les transactions validées. Si l'option BULK_LOGGED est spécifiée, la récupération après l'échec d'un support bénéficie des meilleures performances et utilise un espace minimal pour les journaux avec certaines opérations en bloc ou à grande échelle. Si l'option SIMPLE est spécifiée, une stratégie de sauvegarde élémentaire utilisant un espace minimal pour les journaux est assurée.	FULL
PAGE_VERIFY	Si CHECKSUM est spécifié, le Moteur de base de données calcule une somme de contrôle portant sur le contenu de la page toute entière et stocke la valeur obtenue dans l'en-tête de la page dès que cette dernière 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 comparé à la valeur préalablement stockée dans l'en-tête de la page. Si TORN_PAGE_DETECTION est spécifié, un bit spécifique pour chaque secteur de 512 octets d'une page de base de données de 8 kilo-octets est enregistré et stocké dans l'en-tête de la page lorsque celle-ci 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 NONE est spécifié, les écritures de pages de base de données ne génèrent aucune valeur CHECKSUM ou TORN_PAGE_DETECTION. SQL Server ne vérifie pas la somme de contrôle ou la page endommagée lors des lectures, même si une valeur CHECKSUM ou TORN_PAGE_DETECTION est présente dans l'en-tête des pages.	CHECKSUM

RECOVERY

Si la valeur FULL est spécifiée, en cas d'échec d'un support, une restauration complète est effectuée au moyen des sauvegardes des journaux de transactions. Si un fichier de données est endommagé, la récupération des supports peut restaurer toutes les transactions validées.

Si l'option BULK_LOGGED est spécifiée, la récupération après l'échec d'un support bénéficie des meilleures performances et utilise un espace minimal pour les journaux avec certaines opérations en bloc ou à grande échelle.

Si l'option SIMPLE est spécifiée, une stratégie de sauvegarde élémentaire utilisant un espace minimal pour les journaux est assurée.

FULL

PAGE_VERIFY

Si CHECKSUM est spécifié, le Moteur de base de données calcule une somme de contrôle portant sur le contenu de la page toute entière et stocke la valeur obtenue dans l'en-tête de la page dès que cette dernière 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 comparé à la valeur préalablement stockée dans l'en-tête de la page.

Si TORN_PAGE_DETECTION est spécifié, un bit spécifique pour chaque secteur de 512 octets d'une page de base de données de 8 kilo-octets est enregistré et stocké dans l'en-tête de la page lorsque celle-ci 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 NONE est spécifié, les écritures de pages de base de données ne génèrent aucune valeur CHECKSUM ou TORN_PAGE_DETECTION. SQL Server ne vérifie pas la somme de contrôle ou la page endommagée lors des lectures, même si une valeur CHECKSUM ou TORN_PAGE_DETECTION est présente dans l'en-tête des pages.

CHECKSUM

Options de Service Broker

Contrôle des options de Service Broker .

Option	Description	Valeur par défaut
ENABLE_BROKER \| DISABLE_BROKER \| NEW_BROKER \| ERROR_BROKER_CONVERSATIONS	Si la valeur ENABLE_BROKER est spécifiée, Service Broker est activé pour la base de données spécifiée. Si la valeur DISABLE_BROKER est spécifiée, Service Broker est désactivé pour la base de données spécifiée. La valeur NEW_BROKER prescrit que la base de données reçoit un nouvel identificateur de Service Broker. Avec ERROR_BROKER_CONVERSATIONS, un message d'erreur est généré pour les conversations de la base de données lorsque celle-ci est attachée.	ENABLE_BROKER

ENABLE_BROKER | DISABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS

Si la valeur ENABLE_BROKER est spécifiée, Service Broker est activé pour la base de données spécifiée.

Si la valeur DISABLE_BROKER est spécifiée, Service Broker est désactivé pour la base de données spécifiée.

La valeur NEW_BROKER prescrit que la base de données reçoit un nouvel identificateur de Service Broker.

Avec ERROR_BROKER_CONVERSATIONS, un message d'erreur est généré pour les conversations de la base de données lorsque celle-ci est attachée.

ENABLE_BROKER

Options d'isolement de capture instantanée

Détermine le niveau d'isolement des transactions.

Option	Description	Valeur par défaut
ALLOW_SNAPSHOT_ISOLATION	La valeur ON indique que les transactions peuvent spécifier le niveau d'isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient une capture instantanée des données si celle-ci existe au début de la transaction. Si la valeur OFF est spécifiée, les transactions ne peuvent pas définir le niveau d'isolement des transactions SNAPSHOT.	OFF
READ_COMMITTED_SNAPSHOT	Si ON est spécifié, les transactions qui définissent le niveau d'isolement READ_COMMITTED utilisent le versioning de ligne au lieu du verrouillage. Quand une transaction s'exécute au niveau d'isolement READ_COMMITTED, toutes les instructions voient une capture instantanée des données si celle-ci existe au début de l'instruction. Si la valeur OFF est spécifié, les transactions qui spécifient le niveau d'isolement READ_COMMITTED utilisent le verrouillage. Lors du paramétrage de l'option READ_COMMITTED_SNAPSHOT, seule la connexion exécutant la commande ALTER DATABASE est autorisée dans la base de données. La base de données ne peut contenir aucune autre connexion ouverte avant la fin de l'exécution de la commande ALTER DATABASE. Il n'est pas nécessaire que la base de données soit en mode mono-utilisateur.	OFF

ALLOW_SNAPSHOT_ISOLATION

La valeur ON indique que les transactions peuvent spécifier le niveau d'isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient une capture instantanée des données si celle-ci existe au début de la transaction.

Si la valeur OFF est spécifiée, les transactions ne peuvent pas définir le niveau d'isolement des transactions SNAPSHOT.

OFF

READ_COMMITTED_SNAPSHOT

Si ON est spécifié, les transactions qui définissent le niveau d'isolement READ_COMMITTED utilisent le versioning de ligne au lieu du verrouillage. Quand une transaction s'exécute au niveau d'isolement READ_COMMITTED, toutes les instructions voient une capture instantanée des données si celle-ci existe au début de l'instruction.

Si la valeur OFF est spécifié, les transactions qui spécifient le niveau d'isolement READ_COMMITTED utilisent le verrouillage.

Lors du paramétrage de l'option READ_COMMITTED_SNAPSHOT, seule la connexion exécutant la commande ALTER DATABASE est autorisée dans la base de données. La base de données ne peut contenir aucune autre connexion ouverte avant la fin de l'exécution de la commande ALTER DATABASE. Il n'est pas nécessaire que la base de données soit en mode mono-utilisateur.

OFF

Options SQL

Contrôle des options de conformité à la norme ANSI.

Option	Description	Valeur par défaut
ANSI_NULL_DEFAULT	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. La valeur par défaut est NULL si ON est spécifié. La valeur par défaut est NOT NULL si OFF est spécifié.	OFF
ANSI_NULLS	Si la valeur ON est spécifiée, toutes les comparaisons à une valeur NULL sont évaluées à UNKNOWN (inconnu). Si la valeur OFF est spécifiée, les comparaisons de valeurs non-UNICODE à une valeur NULL sont évaluées à TRUE (vrai) si les deux valeurs sont NULL.	OFF
ANSI_PADDING	Si cette option possède la valeur ON, 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. Si cette option possède la valeur OFF, les espaces à droite (dans les colonnes varchar ou nvarchar) et les zéros à droite (dans les colonnes varbinary) sont supprimés. Ce paramètre affecte uniquement la définition des nouvelles colonnes. Les colonnes char et binary 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 et binary qui n'acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.	OFF
ANSI_WARNINGS	Si cette option a la valeur ON, des erreurs ou des avertissements sont générés lorsque des conditions telles qu'une division par zéro se produisent, ou que des valeurs NULL apparaissent dans des fonctions d'agrégation. Si la valeur OFF est spécifiée, aucun avertissement n'est émis lorsque des conditions telles qu'une division par zéro interviennent.	OFF
ARITHABORT	Si la valeur ON est spécifiée, une requête s'arrête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution. Si cette option a la valeur OFF, un message d'avertissement s'affiche si l'une de ces erreurs se produit, mais le traitement de la requête, du lot d'instructions ou de la transaction se poursuit, comme s'il n'y avait pas eu d'erreur.	OFF
CONCAT_NULL_YIELDS_NULL	Si la valeur ON est spécifiée, le résultat de la concaténation est NULL lorsque l'un des deux opérandes est NULL. Si la valeur OFF est spécifiée, la valeur NULL est considérée comme une chaîne de caractères vide.	OFF
QUOTED_IDENTIFIER	Si la valeur ON est spécifiée, vous pouvez encadrer de guillemets doubles les identificateurs délimités. Si cette option est désactivée (OFF), les identificateurs ne peuvent pas être mis entre guillemets et doivent respecter toutes les règles Transact-SQL applicables aux identificateurs.	OFF
NUMERIC_ROUNDABORT	Si la valeur ON est spécifiée, une erreur est générée lorsqu'une perte de précision survient dans une expression. Lorsque cette option est désactivée (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.	OFF
RECURSIVE_TRIGGERS	Si la valeur ON est spécifiée, l'exécution récursive des déclencheurs AFTER est autorisée. Si la valeur OFF est spécifiée, seule l'exécution directe des déclencheurs AFTER est autorisée.	OFF

Pour modifier les options de base de données

Voir aussi

Historique des modifications

Version	Historique
5 décembre 2005	Contenu modifié : Suppression de l'option SUPPLEMENTAL_LOGGING. Ajout de l'option ANSI_PADDING.