Partager via

  • Article

sp_tableoption (Transact-SQL)

Définit les valeurs d'option des tables définies par l'utilisateur. La procédure sp_tableoption peut être utilisée pour contrôler le comportement dans la ligne des tables comportant des colonnes varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, ou des colonnes de type défini par l'utilisateur volumineuses.

Important

La fonctionnalité text in row sera supprimée dans une future version de SQL Server. Pour stocker des données de valeur élevée, nous vous recommandons d'utiliser les types de données varchar(max), nvarchar(max) et varbinary(max).

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          ,[ @OptionValue =] 'value'

Arguments

  • [ @TableNamePattern =] 'table'
    Spécifie le nom qualifié ou non d'une table de base de données définie par l'utilisateur. Si un nom complet de table (incluant un nom de base de données) est fourni, le nom de base de données doit être celui de la base de données active. Vous ne pouvez pas définir simultanément les options des tables pour plusieurs tables. table est de type nvarchar(776), et n'a pas de valeur par défaut.

  • [ @OptionName = ] 'option_name'
    Spécifie un nom d'option de table. option_name est de type varchar(35), et n'a pas de valeur par défaut égale à NULL. option_name peut prendre l'une des valeurs suivantes.

    Valeur

    Description

    table lock on bulk load

    Désactivée (valeur par défaut), oblige le processus de chargement en masse effectué sur les tables définies par l'utilisateur à obtenir des verrous de lignes. Activée, oblige le processus de chargement en masse effectué sur les tables définies par l'utilisateur à obtenir un verrou de mise à jour en bloc.

    insert row lock

    Non pris en charge dans SQL Server 2008.

    SQL Server utilise la stratégie de verrouillage de ligne et recourt parfois au verrouillage de page ou de table. Cette option n'a aucun effet sur le comportement de verrouillage de SQL Server et elle n'est incluse qu'à des fins de compatibilité des scripts et procédures existants.

    text in row

    Si la valeur est OFF ou 0 (désactivé, valeur par défaut), le comportement en cours n'est pas modifié, et la ligne ne contient pas d'objet BLOB.

    Lorsqu'elle est spécifiée et que la valeur de @OptionValue est ON (activé) ou une valeur de type entier comprise entre 24 et 7 000, les nouvelles chaînes text, ntext ou image sont stockées directement dans la ligne de données. Tous les objets BLOB existants (données text, ntext ou image) sont convertis au format text in row lorsque la valeur de l'objet BLOB est mise à jour. Pour plus d'informations, consultez Remarques.

    large value types out of row

    1 = Les colonnes varchar(max), nvarchar(max), varbinary(max), xml et de type défini par l'utilisateur volumineuses de la table sont stockées hors de la ligne, avec un pointeur de 16 octets vers la racine.

    0 = Les valeurs varchar(max), nvarchar(max), varbinary(max), xml et de type défini par l'utilisateur volumineuses sont stockées directement dans la ligne de données, jusqu'à une limite de 8 000 octets et tant que la valeur peut être contenue dans l'enregistrement. Si la valeur ne tient pas dans l'enregistrement, un pointeur est stocké dans la ligne et le reste est stocké hors de la ligne dans l'espace de stockage LOB. 0 est la valeur par défaut.

    format de stockage vardecimal

    Lorsque la valeur est TRUE, ON ou 1, la table désignée est activée pour le format de stockage vardecimal. Lorsque la valeur est FALSE, OFF ou 0, la table n'est pas activée pour le format de stockage vardecimal. Le format de stockage vardecimal ne peut être activé que si la base de données est activée pour ce type de stockage à l'aide de sp_db_vardecimal_storage_format. Pour plus d'informations sur le format de stockage vardecimal, consultez Stockage des données décimales sous forme de colonne de longueur variable. Cette option nécessite SQL Server 2005 Service Pack 2. Le format de stockage Vardecimal n'est disponible que dans les éditions SQL Server Enterprise, Developer et Evaluation. Dans SQL Server 2008 et versions ultérieures, toutes les bases de données utilisateur sont activées pour le format de stockage vardecimal. Dans SQL Server 2008 et versions ultérieures, le format de stockage vardecimal est désapprouvé. Utilisez plutôt la compression ROW. Pour plus d'informations, consultez Création de tables et d'index compressés. 0 est la valeur par défaut.

  • [ @OptionValue =] 'value'
    Spécifie si option_name est activé (TRUE, ON ou 1) ou désactivé (FALSE, OFF ou 0). Le paramètre value est de type varchar(12), sans valeur par défaut. Le paramètre value ne tient pas compte de la casse.

    Pour l'option text in row, les valeurs d'option valides sont 0, ON, OFF ou un entier compris entre 24 et 7 000. Lorsque value a la valeur ON, la limite a comme valeur par défaut 256 octets.

Valeurs du code de retour

0 (succès) ou numéro d'erreur (échec)

Notes

La procédure sp_tableoption peut être utilisée uniquement pour définir les valeurs des options des tables définies par l'utilisateur. Pour afficher les propriétés des tables, utilisez OBJECTPROPERTY.

L'option text in row de sp_tableoption peut être activée ou désactivée uniquement pour les tables qui contiennent des colonnes de texte. Si la table ne contient pas de colonne de texte, SQL Server génère une erreur.

Lorsque l'option text in row est activée, le paramètre @OptionValue permet aux utilisateurs de spécifier la taille maximale de stockage dans une ligne pour des objets BLOB. La valeur par défaut est de 256 octets, mais les valeurs peuvent être comprises entre 24 et 7 000 octets.

Les chaînes text, ntext ou image sont stockées dans la ligne de données si les critères suivants s'appliquent :

  • l'option text in row est activée ;

  • la longueur de la chaîne est plus courte que la limite spécifiée dans @OptionValue ;

  • l'espace disque disponible s'avère suffisant dans la ligne de données.

Lorsque les chaînes BLOB sont stockées dans la ligne de données, la lecture et l'écriture des chaînes text, ntext ou image peuvent s'avérer aussi rapides que la lecture ou l'écriture de chaînes de caractères et binaires. SQL Server n'a pas besoin d'accéder à des pages séparées pour lire ou écrire la chaîne BLOB.

Si une chaîne text, ntext ou image est plus importante que la limite spécifiée ou que l'espace disponible dans la ligne, les pointeurs sont alors stockés dans la ligne. Les conditions concernant le stockage des chaînes BLOB dans la ligne sont toujours applicables : la ligne de données doit disposer d'un espace suffisant pour contenir les pointeurs.

Les chaînes d'objets BLOB et les pointeurs stockés dans la ligne d'une table sont considérés comme des chaînes de longueur variable. SQL Server n'utilise que le nombre d'octets nécessaires au stockage de la chaîne ou du pointeur.

Les chaînes d'objets BLOB existantes ne sont pas converties immédiatement lorsque l'option text in row est activée pour la première fois. Ces chaînes ne sont converties que lors de leur mise à jour. De même, lorsque la limite de l'option text in row est augmentée, les chaînes text, ntext ou image déjà présentes dans la ligne de données ne sont converties à la nouvelle limite que lors de leur mise à jour.

[!REMARQUE]

La désactivation de l'option text in row ou la réduction de sa limite nécessite la conversion de tous les objets BLOB, ce qui peut rallonger le processus, en fonction du nombre de chaînes d'objets BLOB à convertir. La table est verrouillée au cours du processus de conversion.

Une variable de table, comprenant une fonction chargée de retourner une variable de table, possède automatiquement l'option text in row activée avec une limite incluse par défaut de 256 octets. Cette option ne peut pas être modifiée.

L'option text in row prend en charge les fonctions TEXTPTR, WRITETEXT, UPDATETEXT et READTEXT. Les utilisateurs peuvent lire des parties d'objet BLOB avec la fonction SUBSTRING(), mais sans oublier que les pointeurs de texte en ligne ont des durées et des limites différentes des autres pointeurs de texte. Pour plus d'informations, consultez Gestion des données ntext, text et image.

Pour rétablir une table du format de stockage vardecimal au format de stockage décimal normal, la base de données doit être en mode de récupération SIMPLE. Le changement de mode de récupération va rompre la séquence de journaux de transactions consécutifs à des fins de sauvegarde. Par conséquent, vous devez créer une sauvegarde de base de données complète après avoir supprimé le format de stockage vardecimal d'une table.

Autorisations

L'exécution de sp_tableoption nécessite une autorisation ALTER sur la table.

Exemples

A. Stockage des données xml hors de la ligne

Cet exemple spécifie que les données xml de la table HumanResources.JobCandidate doivent être stockées hors de la ligne.

USE AdventureWorks;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Activation du format de stockage vardecimal sur une table

L'exemple suivant modifie la table Production.WorkOrderRouting pour stocker le type de données decimal au vardecimalstorage format.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';