Données dans la ligne

Mis à jour : 12 décembre 2006

Dans SQL Server 2005, les types de valeurs élevées de petite à moyenne taille (varchar(max), nvarchar(max), varbinary(max) et xml) et les types de données LOB (Large OBject) (text, ntext et image) peuvent être stockés dans une ligne de données. Ce comportement est contrôlé par deux options dans la procédure stockée système sp_tableoption : l'option large value types out of row pour les types de valeurs élevées, et l'option text in row pour les types d'objets volumineux. Ces options sont idéalement utilisées avec des tables dans lesquelles les valeurs de données des types mentionnées plus haut sont généralement lues ou écrites dans une unité, et quand la plupart des instructions qui font référence à la table pointent vers ce type de données. L'utilité du stockage de données dans la ligne dépend donc des caractéristiques d'usage et de charge de travail.

Important :

L'option text in row sera supprimée dans une prochaine version de SQL Server. Évitez de faire appel à cette option dans un nouveau travail de développement et prévoyez la modification des applications qui l'utilisent actuellement. Nous vous conseillons de stocker les données volumineuses à l'aide des types de données varchar(max), nvarchar(max) ou varbinary(max). Pour contrôler le comportement dans la ligne et hors ligne de ces types de données, utilisez l'option large value types out of row.

Sauf si l'option text in row a la valeur ON ou indique une limite spécifique, les chaînes text, ntext ou image sont des longues chaînes binaires ou de caractères (jusqu'à 2 gigaoctets) qui sont stockées en dehors d'une ligne de données. La ligne de données contient uniquement un pointeur de texte 16 octets qui pointe vers le nœud racine d'une arborescence composée de pointeurs externes. Ces pointeurs mappent les pages dans lesquelles les fragments de chaîne sont stockés. Pour plus d'informations sur le stockage de chaînes text, ntext ou image, consultez Utilisation des données de type text et image.

Vous pouvez définir l'option text in row pour des tables contenant des colonnes de données LOB. Vous pouvez également spécifier une limite comprise entre 24 et 7 000 octets.

De même, à moins que l'option large value types out of row ait été activée (ON), les colonnes varchar(max), nvarchar(max), varbinary(max) et xml sont stockées, si possible, à l'intérieur de la ligne de données. Si c'est le cas, le Moteur de base de données essaie de faire rentrer la valeur spécifique si possible, ou la pousse hors de la ligne. Si l'option large value types out of row est activée, les valeurs sont stockées hors de la ligne et désignées par un pointeur de texte de 16 octets dans l'enregistrement.

Remarque :
L'espace de stockage maximal dans la ligne pour les valeurs élevées est de 8 000 octets lorsque l'option large value types out of row est désactivée (OFF). Contrairement à l'option text in row, vous ne pouvez pas spécifier la limite dans la ligne pour les colonnes dans la table.

Lorsqu'une table est configurée pour stocker soit des valeurs élevées, soit des objets volumineux directement dans la ligne de données, les valeurs réelles des colonnes seront dans la ligne si une des conditions suivantes est remplie :

• La longueur de la chaîne est inférieure à la limite spécifiée pour les colonnes text, ntext et image.
• Il y a suffisamment d'espace disponible dans la ligne de données pour contenir la chaîne.

Lorsqu'une valeur de colonne de valeurs élevées ou de type objet volumineux est stockée dans la ligne de données, le Moteur de base de données n'a pas besoin d'accéder à une page séparée ou à un ensemble de pages pour lire ou écrire la chaîne binaire ou de caractères. Ceci rend la lecture et l'écriture des chaînes dans la ligne presque aussi rapide que l'écriture ou la lecture de chaînes varchar, nvarchar ou varbinary limitées en taille. De même, lorsque les valeurs sont stockées hors ligne, le Moteur de base de données subit une lecture ou une écriture de page supplémentaire.

Pour les objets volumineux, si la longueur de la chaîne dépasse la limite spécifiée pour l'option text in row ou l'espace disponible dans la ligne, l'ensemble de pointeurs normalement stockés dans le nœud racine de l'arborescence de pointeurs sont stockés dans la ligne. Les pointeurs sont stockés dans la ligne si une des conditions suivantes est remplie :

• La quantité d'espace requise pour stocker les pointeurs est inférieure à la limite spécifiée pour l'option text in row.
• L'espace disponible dans la ligne de données est suffisant pour stocker les pointeurs.

Lorsque les pointeurs sont déplacés du nœud racine vers la ligne, le Moteur de base de données ne doit pas utiliser de nœud racine. L'accès à une page n'est donc plus nécessaire lors de la lecture ou de l'écriture de la chaîne. Les performances sont alors améliorées.

Lorsque des nœuds racines sont utilisés, ils sont stockés en tant que fragments de chaîne dans une page LOB et peuvent contenir jusqu'à cinq pointeurs internes. Le Moteur de base de données requiert 72 octets d'espace disponible dans la ligne pour stocker cinq pointeurs pour une chaîne dans la ligne. Si l'espace disponible dans la ligne est insuffisant pour contenir les pointeurs lorsque l'option text in row est activée (ON) ou que l'option large value types out of row est désactivée (OFF), il est possible que le Moteur de base de données doive allouer une page de 8 K pour les contenir. Si la longueur de la valeur dépasse 40 200 octets, plus de cinq pointeurs sont nécessaires, auquel cas seuls 24 octets sont stockés dans la ligne principale et une page de données supplémentaire est allouée dans l'espace de stockage LOB.

Lorsque des chaînes volumineuses sont stockées dans la ligne, elles sont stockées de la même manière que des chaînes de longueur variable. Le Moteur de base de données trie les colonnes dans l'ordre décroissant et pousse les valeurs hors de la ligne jusqu'à ce que les colonnes restantes soient contenues dans la page de données (8 K).

Activation et désactivation de l'option large value types out of row

Vous pouvez activer l'option large value types out of row pour une table en utilisant la procédure stockée sp_tableoption comme suit :

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

Si vous spécifiez OFF, la limite dans la ligne pour les colonnes varchar(max), nvarchar(max), varbinary(max) et xml est fixée à 8 000 octets. Un pointeur racine de 16 octets est stocké dans la ligne et la valeur est stockée dans l'espace de stockage LOB. Nous conseillons d'activer cette option pour les tables dont la plupart des instructions ne font pas référence à des colonnes de valeurs élevées. Le stockage de ces colonnes hors de la ligne permet d'augmenter le nombre de lignes contenues dans une page, ce qui réduit le nombre d'opérations d'E/S nécessaires à l'analyse de la table.

Lorsque la valeur de cette option est OFF, de nombreuses chaînes risquent d'être stockées dans la ligne, ce qui pourrait réduire le nombre de lignes de données contenues sur chaque page. Si la plupart des instructions qui font référence à la table ne doivent pas accéder aux colonnes varchar(max), nvarchar(max), varbinary(max) et xml, la diminution du nombre de lignes dans une page peut entraîner l'augmentation du nombre de pages qui doivent être lues pour l'exécution des requêtes. La réduction du nombre de lignes par page peut augmenter le nombre de pages à analyser si l'optimiseur ne trouve aucun index utilisable.

Vous pouvez également utiliser la procédure stockée sp_tableoption pour désactiver l'option large value types out of row :

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

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 existantes ne sont pas immédiatement converties. Le stockage des chaînes est modifié au fur et à mesure de leur mise à jour. Toute nouvelle valeur insérée dans une table est stockée en fonction de l'option de table en vigueur.

Pour examiner la valeur de l'option large value types out of row pour une table spécifique, interrogez la colonne large_value_types_out_of_row dans l'affichage catalogue sys.tables. Cette colonne a pour valeur 0 si l'option large value types out of row n'est pas activée pour la table, et la valeur 1 si les types de valeur élevée sont stockés en dehors de la ligne (option large value types are stored out of row).

Activation et désactivation de l'option texte dans la ligne

Vous pouvez activer l'option text in row pour une table en utilisant la procédure stockée sp_tableoption comme suit :

sp_tableoption N'MyTable', 'text in row', 'ON'

Vous pouvez, si vous le souhaitez, spécifier une limite maximale, située entre 24 et 7 000 octets, pour la longueur d'une chaîne text, ntext et image pouvant être stockée dans une ligne de données :

sp_tableoption N'MyTable', 'text in row', '1000'

Si vous spécifiez ON au lieu d'une limite précise, la limite prend par défaut la valeur 256 octets. Cette valeur par défaut permet de profiter de la plupart des avantages en termes de performances associés à l'option text in row. En règle générale, il vaut mieux ne pas spécifier une valeur inférieure à 72, mais pas non plus trop élevée. Ceci est particulièrement le cas pour les tables dont la plupart des instructions ne font pas référence aux colonnes text, ntext et image, ou dans lesquelles il y a plusieurs colonnes text, ntext et image.

Si vous définissez une limite élevée pour l'option text in row et que de nombreuses chaînes sont stockées dans la ligne, vous pouvez réduire de façon significative le nombre de lignes de données que chaque page peut contenir. Si la plupart des instructions qui font référence à la table n'accèdent pas aux colonnes text, ntext ou image, la diminution du nombre de lignes dans une page augmente le nombre de pages à lire pour traiter les requêtes. La réduction du nombre de lignes par page peut augmenter la taille des index et le nombre de pages à analyser si l'optimiseur ne trouve aucun index utilisable. La valeur par défaut (256) de la limite de l'option text in row est suffisante pour permettre le stockage dans les lignes des chaînes de taille réduite et des pointeurs de texte racine, mais insuffisante pour réduire le nombre de lignes par page au point d'influencer positivement les performances.

L'option text in row reçoit automatiquement la valeur 256 pour les variables de type de données table et pour les tables retournées par des fonctions définies par l'utilisateur qui retournent une table. Ce paramètre ne peut pas être modifié.

Vous pouvez également utiliser la procédure stockée sp_tableoption pour désactiver l'option en spécifiant la valeur OFF ou 0 :

sp_tableoption N'MyTable', 'text in row', 'OFF'

Pour examiner la valeur de l'option text in row pour une table spécifique, interrogez la colonne text_in_row_limit dans la vue de catalogue sys.tables. Cette colonne indique 0 si aucune option text in row n'a été activée pour la table et une valeur supérieure à 0 si une limite a été définie.

Effets de l’utilisation de l'option text in row

L'option text in row a les effets suivants :

• Après avoir activé l'option text in row, vous pouvez utiliser les instructions TEXTPTR, READTEXT, UPDATETEXT ou WRITETEXT pour lire ou modifier des portions de valeurs text, ntext ou image stockées dans la table. Dans une instruction SELECT, vous pouvez lire la totalité d'une chaîne text, ntext ou image ou utiliser la fonction SUBSTRING pour en lire des portions. Toutes les instructions INSERT ou UPDATE qui font référence à la table doivent spécifier des chaînes complètes et ne peuvent pas modifier uniquement une portion d'une chaîne text, ntext ou image.
• Lorsque l'option text in row est activée pour la première fois, les chaînes text, ntext ou image existantes ne sont pas immédiatement converties en chaînes dans la ligne. Cette conversion a lieu uniquement si, par la suite, elles font l'objet d'une mise à jour. Toute chaîne text, ntext ou image insérée après l'activation de l'option text in row est insérée comme une chaîne dans la ligne.
• La désactivation de l'option text in row peut être une opération longue et journalisée. Pendant son exécution, la table est verrouillée et toutes les chaînes text, ntext et image dans la ligne sont converties en chaînes text, ntext et image ordinaires. La durée d'exécution de la commande et la quantité de données modifiées dépendent de la quantité de chaînes text, ntext et image dans la ligne à convertir en chaînes ordinaires.
• Du point de vue du fournisseur OLE DB ou du pilote ODBC pour SQL Native Client, l'impact de l'option text in row se traduit uniquement par un accès plus rapide aux données text, ntext et image.
• Les fonctions texte et image de la bibliothèque DB-Library, telles que dbreadtext et dbwritetext, ne peuvent pas être appliquées à une table après l'activation de l'option text in row.

Voir aussi

Concepts

Affectation d'un type de données à une colonne

Autres ressources

Création de tables

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
12 décembre 2006	Nouveau contenu : Dans la section « Activation et désactivation de l'option texte dans la ligne », ajout du paragraphe qui invite à examiner la valeur de l'option large value types out of row pour une table spécifique, à interroger la colonne large_value_types_out_of_row dans l'affichage catalogue sys.tables.