sp_tableoption (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Définit les valeurs d'option des tables définies par l'utilisateur. sp_tableoptionpeut être utilisé pour contrôler le comportement en ligne des tables avec varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image ou de grandes colonnes de type défini par l’utilisateur.

Important

Le texte de la fonctionnalité de ligne sera supprimé dans une version ultérieure de SQL Server. Pour stocker des données de grande valeur, nous vous recommandons d’utiliser les types de données varchar(max), nvarchar(max) et varbinary(max).

Conventions de la syntaxe Transact-SQL

Syntaxe

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Arguments

[ @TableNamePattern = ] N'TableNamePattern'

Nom qualifié ou non qualifié d’une table de base de données définie par l’utilisateur. @TableNamePattern est nvarchar(776), sans valeur par défaut. Si un nom de table complet (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 en cours. Les options de table pour plusieurs tables ne peuvent pas être définies en même temps.

[ @OptionName = ] 'OptionName'

Nom de l’option de table. @OptionName est varchar(35) et peut être 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 N'est plus pris en charge.

Cette option n’a aucun effet sur le comportement de verrouillage de SQL Server et n’est incluse que pour la compatibilité des scripts et procédures existants.
text in row Quand OFF ou 0 (désactivé, valeur par défaut), il ne change pas le comportement actuel et il n’y a pas d’objet BLOB dans la ligne.

Lorsque vous spécifiez et @OptionValue est (activé) ou une valeur entière à partir de 700024 , de nouveaux texte, ntext ou chaînes d’image sont stockés directement dans la ligne de données.ON Toutes les données BLOB existantes (objet volumineux binaire : texte, ntext ou image) sont remplacées par du texte au format de ligne lorsque la valeur BLOB est mise à jour. Pour plus d'informations, consultez la section Notes.
large value types out of row 1 = varchar(max), nvarchar(max), varbinary(max), xml et de grandes colonnes définies par l’utilisateur (UDT) dans la table sont stockées hors ligne, avec un pointeur de 16 octets vers la racine.

0 = varchar(max), nvarchar(max), varbinary(max), xml et les valeurs UDT volumineuses sont stockées directement dans la ligne de données, jusqu’à une limite de 8 000 octets et tant que la valeur peut correspondre à l’enregistrement. Si la valeur ne correspond pas à l’enregistrement, un pointeur est stocké en ligne et le reste est stocké hors ligne dans l’espace de stockage métier. La valeur par défaut est 0.

Le type défini par l’utilisateur (UDT) volumineux s’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.

Utilisez l’option TEXTIMAGE_ONCREATE TABLE pour spécifier un emplacement pour le stockage de types de données volumineux.
format de stockage vardecimal S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.

Quand TRUE, ONou 1, la table désignée est activée pour le format de stockage vardecimal . Quand FALSE, OFFou 0, la table n’est pas activée pour le format de stockage vardecimal . Le format de stockage vardecimal ne peut être activé que lorsque la base de données est activée pour le format de stockage vardecimal à l’aide de sp_db_vardecimal_storage_format. Dans SQL Server 2008 (10.0.x) et versions ultérieures, le format de stockage vardecimal est déconseillé. Utilisez ROW plutôt la compression. Pour plus d’informations, consultez Compression de données. La valeur par défaut est 0.

[ @OptionValue = ] 'OptionValue'

Spécifie si la @OptionName est activée (TRUE, ONou ) ou 1désactivée (FALSE, OFFou 0). @OptionValue est varchar(12), sans valeur par défaut. @OptionValue ne respecte pas la casse.

Pour le texte de l’option de ligne, les valeurs d’option valides sont 0, ONou OFFun entier de l’option 7000.24 Lorsque @OptionValue est ON, la limite par défaut est de 256 octets.

Valeurs des codes de retour

0 (réussite) ou numéro d’erreur (échec).

Notes

sp_tableoption ne peut être utilisé que pour définir des valeurs d’option pour les tables définies par l’utilisateur. Pour afficher les propriétés de la table, utilisez OBJECTPROPERTY ou la requête sys.tables.

L’option sp_tableoption texte dans la ligne peut être activée ou désactivée uniquement sur les tables qui contiennent des colonnes de texte. Si la table n’a pas de colonne de texte, SQL Server génère une erreur.

Lorsque l’option texte dans la ligne est activée, le paramètre @OptionValue permet aux utilisateurs de spécifier la taille maximale à stocker dans une ligne pour un objet 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 texte, ntext ou image sont stockées dans la ligne de données si les conditions suivantes s’appliquent :

  • Le texte dans la ligne est activé.
  • La longueur de la chaîne est plus courte que la limite spécifiée dans @OptionValue.
  • Il y a suffisamment d’espace disponible dans la ligne de données.

Lorsque des chaînes BLOB sont stockées dans la ligne de données, la lecture et l’écriture du texte, du ntext ou des chaînes d’image peuvent être aussi rapides que la lecture ou l’écriture de chaînes binaires et de caractères. SQL Server n’a pas besoin d’accéder à des pages distinctes pour lire ou écrire la chaîne BLOB.

Si un texte, ntext ou une chaîne d’image est supérieur à la limite spécifiée ou à l’espace disponible dans la ligne, les pointeurs sont stockés dans la ligne à la place. 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 utilise uniquement le nombre d’octets requis pour stocker la chaîne ou le pointeur.

Les chaînes BLOB existantes ne sont pas converties immédiatement lorsque le texte de la ligne est activé pour la première fois. Ces chaînes ne sont converties que lors de leur mise à jour. De même, lorsque la limite d’option de texte dans la ligne est augmentée, le texte, le texte, ntext ou les chaînes d’image déjà dans la ligne de données ne sont pas convertis pour respecter la nouvelle limite jusqu’à ce qu’ils soient mis à 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 texte dans la ligne 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 modifier une table du format de stockage vardecimal au format de stockage décimal normal, la base de données doit se trouver dans le modèle de récupération SIMPLE. La modification du mode de récupération interrompt la chaîne de journaux à des fins de sauvegarde. Vous devez donc créer une sauvegarde complète de base de données après avoir supprimé le format de stockage vardecimal d’une table.

Si vous convertissez une colonne de type de données métier existante (texte, ntext ou image) en types de valeurs de grande taille petite à moyenne (varchar(max), nvarchar(max)ou varbinary(max)) et que la plupart des instructions ne référencent pas les colonnes de type valeur volumineuses dans votre environnement, envisagez de modifier large_value_types_out_of_row pour 1 obtenir des performances optimales. Lorsque la valeur de l’option large_value_types_out_of_row est modifiée, les valeurs varchar(max), nvarchar(max), varbinary(max) et xml ne sont pas immédiatement converties. Le stockage des chaînes est modifié, car ils sont mis à jour ultérieurement. Les nouvelles valeurs insérées dans une table sont stockées en fonction de l'option de table active. Pour obtenir des résultats immédiats, effectuez une copie des données, puis remplissez à nouveau la table après avoir modifié le paramètre large_value_types_out_of_row ou mettez à jour chaque colonne de types de valeurs de petite à moyenne taille moyenne pour que le stockage des chaînes soit modifié avec l’option de table en vigueur. Vous pouvez également recréer les index sur la table après la mise à jour ou le nouveau remplissage afin de condenser la table.

Autorisations

Pour exécuter sp_tableoption , vous devez ALTER disposer d’une autorisation sur la table.

Exemples

R. Stocker des données XML hors de la ligne

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

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

B. Activer le format de stockage vardecimal sur un tableau

L’exemple suivant modifie la Production.WorkOrderRouting table pour stocker le type de données décimal au format de stockage vardecimal.

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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

Contenu connexe