Partager via

  • Article

Gestion des données ntext, text et image

Important

Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez plutôt les types de données varchar(max), nvarchar(max) et varbinary(max). Pour plus d'informations, consultez Utilisation de types de données de valeur élevée.

Les types de données ntext, text et imagede SQL Server peuvent contenir une très grande quantité de données (jusqu'à 2 Go) sur une seule valeur. Ainsi, si une seule valeur de l'un de ces types dépasse habituellement les capacités d'extraction d'une application, certaines valeurs vont jusqu'à dépasser la capacité de la mémoire virtuelle disponible sur l'ordinateur client. Des étapes spéciales sont donc souvent nécessaires pour extraire ces valeurs.

Si une valeur de données de type ntext, text ou image n'est pas plus grande qu'une chaîne Unicode, une chaîne de caractères ou une chaîne binaire (respectivement 4 000 caractères, 8 000 caractères et 8 000 octets), la valeur peut être référencée dans les instructions SELECT, UPDATE et INSERT pratiquement de la même manière que les types de données moins importants. Par exemple, une colonne ntext avec une petite valeur peut être référencée dans une liste de sélection d'une instruction SELECT de la même manière qu'une colonne nvarchar. Certaines restrictions doivent cependant être respectées. Par exemple, il n'est pas possible de référencer directement une colonne ntext, text ou image dans une clause WHERE. Ces colonnes peuvent être placées dans une clause WHERE en tant que paramètres d'une fonction retournant un autre type de données (tel que ISNULL, SUBSTRING ou PATINDEX), ou dans une expression IS NULL, IS NOT NULL ou LIKE.

Manipulation de plus grandes valeurs de données

Cependant, lorsque les valeurs de données ntext, text et image deviennent plus importantes, elles doivent être traitées bloc par bloc. Transact-SQL et les API de bases de données contiennent tous des fonctions qui permettent aux applications de travailler par bloc sur les données ntext, text et image.

Les API de bases de données utilisent un modèle commun pour manipuler les longues colonnes de type ntext, text et image :

  • Pour lire une colonne longue, l'application inclut simplement la colonne ntext, text ou image dans une liste de sélection, puis elle lie la colonne à une variable de programme assez grande pour contenir un bloc de données de taille raisonnable. L'application exécute alors l'instruction et se sert d'une fonction ou d'une méthode API pour extraire les données par bloc dans la variable liée.

  • Pour écrire une colonne longue, l'application exécute une instruction INSERT ou UPDATE avec un marqueur de paramètre (?) à la place de la valeur à insérer dans la colonne ntext, text ou image. Le marqueur de paramètre (ou le paramètre dans le cas de ADO) est lié à une variable de programme assez importante pour contenir les blocs de données. L'application s'exécute en boucle en commençant par transférer le jeu de données suivant dans la variable liée, puis en appelant une fonction ou une méthode API pour écrire ce bloc de données. Ceci est répété jusqu'à l'envoi complet du bloc de données.

Utilisation de texte dans la ligne

Dans SQL Server, les utilisateurs peuvent activer une option text in row sur une table, de manière à ce qu'elle puisse stocker des données text, ntext ou image dans sa ligne de données.

Pour activer cette option, exécutez la procédure stockée sp_tableoption, en spécifiant text in row en tant que nom d'option et on comme valeur d'option. La taille maximale par défaut pouvant être stockée dans une ligne pour un objet BLOB (Binary Large OBject), notamment des données text, ntext ou image, est de 256 octets, mais les valeurs peuvent être comprises entre 24 et 7 000. Pour spécifier une taille maximale autre que celle par défaut, spécifiez un entier compris dans cette plage comme valeur de l'option.

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 dans les 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.

Pour plus d'informations, consultez sp_tableoption (Transact-SQL).

Utilisation de pointeurs de texte

À moins que l'option text in row soit spécifiée, les chaînes text, ntext ou image sont stockées en dehors d'une ligne de données ; seuls les pointeurs de texte de ces chaînes résident dans les lignes de données. Les pointeurs de texte indiquent le nœud racine d'une arborescence composée de pointeurs internes, mappés à des pages dans lesquelles sont stockés les fragments de chaîne (de données text, ntext et image).

Les pointeurs de texte dans la ligne de SQL Server 2000 s'avèrent différents de ceux qui apparaissent dans les versions antérieures de SQL Server. Les pointeurs de texte dans la ligne fonctionnent de manière identique aux descripteurs de fichiers pour les données BLOB ; les pointeurs de texte précédents fonctionnent tels des adresses de données BLOB. Ainsi, lors de l'utilisation de pointeurs de texte dans la ligne, veillez à conserver les caractéristiques suivantes en tête :

Important

Bien que du texte dans la ligne soit autorisé dans un curseur, il n'en est pas de même avec un pointeur de texte dans la ligne. SQL Server renvoie l'erreur 328 si vous tentez de déclarer un curseur qui contient un pointeur de texte dans la ligne.

  1. Nombre

    Un maximum de 1 024 pointeurs de texte dans la ligne actifs est autorisé par transaction et par base de données.

  2. Verrouillage

    Lorsqu'un utilisateur obtient un pointeur de texte actif, SQL Server 2000 verrouille les données et vérifie qu'aucun autre utilisateur ne modifie ou ne supprime la ligne lorsque le premier utilisateur possède le pointeur de texte. Le déverrouillage survient lorsque le pointeur de texte devient non valide. Pour invalider un pointeur de texte, utilisez sp_invalidate_textptr (Transact-SQL).

    Un pointeur de texte ne peut être utilisé pour la mise à jour de valeurs BLOB lorsque le niveau d'isolement de la transaction est en lecture non validée (read uncommitted) ou que la base de données est en mode lecture-seule.

    SQL Server 2000 ne verrouille pas la ligne de données si la base de données est en mode mono-utilisateur.

    Pour illustrer cela, d'après le tableau suivant :

    CREATE TABLE t1 (c1 int, c2 text)
EXEC sp_tableoption 't1', 'text in row', 'on'
INSERT t1 VALUES ('1', 'a')

    La transaction suivante réussira :

    INSERT t1 VALUES ('1','This is text.')
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = textptr(c2)
FROM t1
WHERE c1 = 1;
READTEXT t1.c2 @ptr 0 5
COMMIT TRAN
GO

    La transaction suivante échouera :

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = textptr(c2)
FROM t1
WHERE c1 = 1
WRITETEXT t1.c2 @ptr 'xx'
COMMIT TRAN
GO

  3. Durée

    Les pointeurs de texte en ligne sont uniquement valides au sein d'une transaction. Lorsqu'une transaction est validée, le pointeur de texte devient non valide.

    Au sein d'une transaction, les pointeurs de texte dans une ligne peuvent ne pas être validés lorsqu'une action parmi les suivantes se produit :

    • La session se termine.

    • La ligne de données est supprimée dans la même transaction. (D'autres transactions ne peuvent supprimer une ligne de données en raison du verrouillage obtenu la concernant.)

    • Le schéma d'une table dans laquelle réside le pointeur de texte est modifiée. Les actions de modification de schéma qui rendent les pointeurs de texte non valides comprennent : la création ou la suppression d'index cluster, la modification ou la suppression de la table, la troncation de la table, la modification de l'option text in row via sp_tableoption et l'exécution de sp_indexoption.

    À l'aide de l'exemple précédent, le script suivant fonctionnerait avec des versions antérieures de SQL Server, mais créerait une erreur dans SQL Server 2000.

    DECLARE @ptrval varbinary(16)
PRINT 'get error here'
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1

    Dans SQL Server 2000, le pointeur de texte dans la ligne doit être utilisé au sein d'une transaction :

    BEGIN TRAN
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1
COMMIT

  4. Texte NULL

    Vous pouvez obtenir un pointeur de texte dans la ligne sur du texte NULL créé par INSERT. Auparavant, vous pouviez obtenir des pointeurs de texte uniquement après la mise à jour avec la valeur NULL d'un BLOB.

    Le code suivant, par exemple, ne fonctionne pas dans SQL Server 7.0 mais dans SQL Server 2000.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
INSERT INTO t1 VALUES (4, NULL)
BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 4
WRITETEXT t1.c2 @ptrval 'x4'
COMMIT

    Dans SQL Server 7.0, vous devez procéder comme suit :

    INSERT INTO t1 VALUES (4, NULL)
UPDATE t1 
   SET c2 = NULL 
   WHERE c1 = 4
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 4
WRITETEXT t1.c2 @ptrval 'x4'

Le tableau suivant résume les différences.

Différence

Pointeur de texte dans la ligne

Pointeur de texte en dehors de la ligne

Nombre

Maximum de 1 024 actifs par transaction et par base de données.

Illimité.

Verrouillage

La ligne de données est verrouillée S jusqu'à ce que le pointeur devienne non valide.

Les verrous ne sont pas obtenus lorsque la transaction est en lecture non validée (read uncommitted) ou que la base de données est en mode mono-utilisateur ou lecture-seule.

La ligne de données n'est pas verrouillée.

Durée

Devient non valide à la fin de la transaction ou de la session, lorsqu'une ligne est supprimée ou que le schéma de la table est modifié.

Devient non valide lors de la suppression de la ligne.

Texte NULL

Peut être obtenu juste après l'insertion du texte NULL.

Peut être obtenu uniquement après la mise à jour.

Utilisation des données ntext, text et image avec les API de bases de données

Voici en résumé la manière dont les API de bases de données gèrent les données ntext, text et image :

  • ADO

    ADO peut mapper les colonnes ou les paramètres ntext, text ou image à un objet Champ ou Paramètre. Utilisez la méthode GetChunk pour extraire les données bloc par bloc et la méthode AppendChunk pour écrire les données bloc par bloc.

  • OLE DB

    OLE DB utilise l'interface ISequentialStream pour gérer les types de données ntext, text et image. La méthode ISequentialStream::Read lit les données longues bloc par bloc, et ISequentialStream::Write les enregistre dans la base de données bloc par bloc. Pour plus d'informations, consultez Objets BLOB et OLE.

  • ODBC

    ODBC comporte une fonctionnalité appelée « data-at-execution » utilisée avec les types de données ODBC pour données longues : SQL_WLONGVARCHAR (ntext), SQL_LONGVARCHAR (text) et SQL_LONGVARBINARY (image). Ces types de données sont liés à une variable de programme. SQLGetData est ensuite appelé pour extraire les données longues bloc par bloc, et SQLPutData est appelé pour les envoyer bloc par bloc. Pour plus d'informations, consultez Gestion des colonnes text et image.

  • DB-Library

    Les applications DB-Library lient elles aussi les colonnes ntext, text et image à des variables de programme. La fonction DB-Library dbtxtptr permet de placer un pointeur à l'endroit où se trouve la colonne longue dans la base de données. La fonction dbreadtext est utilisée pour lire les données longues bloc par bloc. Des fonctions telles que dbwritetext, dbupdatetext et dbmoretext sont utilisées pour enregistrer les données longues bloc par bloc.

    [!REMARQUE]

    L'accès au texte de ligne avec DB-Library n'est pas pris en charge.

Pour plus d'informations, consultez Fonctions texte et image (Transact-SQL).