Utilisation de données sql_variant
Le type de données sql_variant opère de la même façon que le type de données variant dans Microsoft Visual Basic. sql_variant permet à une colonne, un paramètre ou une variable de stocker des valeurs de données de différents types de données. Par exemple, une colonne sql_variant peut contenir des valeurs int, decimal, char, binary et nchar. Chaque instance d'une colonne sql_variant enregistre la valeur de données et les informations de métadonnées. Celles-ci comprennent le type de données de base, la taille maximale, l'échelle, la précision et le classement.
Règles d'utilisation de sql_variant
Les règles ci-dessous régissent l'utilisation du type de données sql_variant.
Affectation de valeur générale
Les objets sql_variant peuvent contenir des données de n'importe quel type de données SQL Server à l'exception des types text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml et timestamp et des types Microsoft .NET Framework CLR (Common Language Runtime) définis par l'utilisateur. sql_variant ne peut pas être le type de données de base sous-jacent d'une instance de données sql_variant.
Des constantes de tout type peuvent être spécifiées dans des prédicats ou des affectations faisant référence à des colonnes sql_variant.
Si une valeur sql_variant est NULL, elle est considérée comme ne détenant pas de type de données de base sous-jacent. Cette règle s'applique même si la valeur NULL provient d'une variable ou d'une colonne ayant un type de données spécifique.
Dans l'exemple suivant, la valeur de VariantCol est définie à NULL sans type de données associé, même si la valeur NULL provenait d'une variable int :
DECLARE @IntVar int
SET @IntVar = NULL
UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123
Lorsque des valeurs d'objets sql_variant sont affectées à un objet de n'importe quel autre type de données, elles doivent doit être explicitement converties dans le type de données de la destination. Aucune conversion implicite n'est prise en charge lorsqu'une valeur sql_variant est affectée à un objet de type de données différent.
À des fins de compatibilité avec les autres types de données, les objets de catalogue, tels que la fonction DATALENGTH, qui fournissent la longueur des objets sql_variant indiquent la longueur des données. La longueur des métadonnées contenues dans un objet sql_variant n'est pas retournée.
La propriété ANSI_PADDING des colonnes sql_variant a toujours pour valeur ON. Si des valeurs char, nchar, varchar, nvarchar ou varbinary sont affectées à partir d'une source dont la propriété ANSI_PADDING a pour valeur OFF, les valeurs ne sont pas complétées.
La mise à jour d'une colonne au niveau de l'Abonné entraîne la modification du type de base dans une autre colonne sql_variant. La procédure ci-après illustre ce concept :
Créez un abonnement/publication de fusion. La table publiée doit avoir une colonne sql_variant et une colonne c1. Ajoutez des données à la colonne sql_variant. Le type de base des données est datetime.
Après la synchronisation initiale, le type de base au niveau de l'Abonné est encore datetime.
Mettez à jour la colonne c1 au niveau de l'Abonné.
Les données de la colonne sql_variant sur le serveur de publication ont été modifiées à datetime2.
sql_variant dans les tables
Des colonnes sql_variant peuvent être utilisées dans des index et des clés uniques, sous réserve que la longueur des données dans les colonnes clés n'excède pas 900 octets.
Les colonnes sql_variant ne prennent pas en charge la propriété IDENTITY mais elles peuvent figurer dans une clé primaire ou une clé étrangère.
Les colonnes sql_variant ne peuvent pas être utilisées dans une colonne calculée.
Utilisez ALTER TABLE pour modifier une colonne de tout type de données, à l'exception des types text, ntext, image, timestamp ou sql_variant en une colonne de type sql_variant. Toutes les valeurs existantes sont converties en valeurs sql_variant dont le type de données de base est le même que celui de la colonne avant l'exécution de l'instruction ALTER TABLE. Vous ne pouvez pas utiliser ALTER TABLE pour transformer le type de données d'une colonne sql_variant en un autre type de données, car aucune conversion implicite n'est prise en charge de sql_variant vers les autres types de données.
Classement
La clause COLLATE ne peut pas être utilisée pour affecter un classement de colonne à une colonne sql_variant. Les valeurs de type caractère (char, nchar, varchar et nvarchar) contenues dans une colonne sql_variant peuvent présenter un classement quelconque, et une même colonne sql_variant peut détenir des valeurs de type caractère combinant différents classements.
Lorsqu'une valeur est affectée à une instance sql_variant, la valeur de données et le type de données de base de la source sont attribués. Si la valeur de la source présente un classement, celui-ci est également attribué. Si la valeur de la source présente un type de données défini par l'utilisateur, le type de données de base de ce type est affecté, et non le type lui-même. L'instance sql_variant n'hérite d'aucune règle ou valeur par défaut liée au type de données défini par l'utilisateur. Si une valeur provenant d'une colonne dotée d'une propriété IDENTITY est affectée à une instance sql_variant, sql_variant adopte le type de données de base de la colonne source mais n'hérite pas de la propriété IDENTITY. Affecter une valeur text, ntext ou image à une instance sql_variant constitue une erreur. Les conversions implicites sont prises en charge lors de l'affectation à un objet sql_variant de valeurs d'objets d'autres types de données.
Comparaisons sql_variant
Les colonnes sql_variant peuvent contenir des valeurs de types de données de base et de classements différents ; par conséquent, des règles particulières s'appliquent lors de la comparaison des opérandes sql_variant. Ces règles concernent les opérations qui impliquent des comparaisons comme avec :
des opérateurs de comparaison Transact-SQL
ORDER BY et GROUP BY ;
Index
les fonctions d'agrégation MAX et MIN ;
UNION (sans ALL) ;
Expressions CASE
Pour les comparaisons sql_variant, la hiérarchie des types de données SQL Server est ordonnée en familles. La famille sql_variant possède le degré de priorité le plus élevé.
Hiérarchie des types de données |
Famille de types de données |
|---|---|
sql_variant |
sql_variant |
datetime |
Date et heure |
smalldatetime |
Date et heure |
Float |
Valeur numérique approchée |
Real |
Valeur numérique approchée |
decimal |
Valeur numérique exacte |
money |
Valeur numérique exacte |
smallmoney |
Valeur numérique exacte |
bigint |
Valeur numérique exacte |
int |
Valeur numérique exacte |
smallint |
Valeur numérique exacte |
tinyint |
Valeur numérique exacte |
bit |
Valeur numérique exacte |
nvarchar |
Unicode |
nchar |
Unicode |
varchar |
Unicode |
char |
Unicode |
varbinary |
Binaire |
binary |
Binaire |
uniqueidentifier |
Uniqueidentifier |
Les règles suivantes s'appliquent aux comparaisons sql_variant :
Lors de la comparaison des valeurs sql_variant issues de différents types de données de base appartenant à des familles de types de données différentes, la valeur de la famille dont le rang est supérieur dans la hiérarchie est considérée comme la valeur la plus élevée des deux.
Lors de la comparaison des valeurs sql_variant issues de différents types de données de base appartenant à la même famille, la valeur du type dont le rang est inférieur dans la hiérarchie est implicitement convertie vers l'autre type de données et la comparaison est alors effectuée.
Lorsque les valeurs sql_variant des types de données char, varchar, nchar ou varchar sont comparées, elles sont évaluées d'après les critères suivants : LCID, version LCID, indicateurs de comparaison et ID de tri. Chaque critère est comparé en tant que valeur d'entier, dans l'ordre indiqué.
Ces règles peuvent générer des résultats différents lors de la comparaison de valeurs sql_variant par rapport aux comparaisons entre des valeurs de type de données de base identique.
Opérande A |
Opérande B |
Résultat de comparaison non sql_variant |
Résultat de comparaison sql_variant |
|---|---|---|---|
'123' char |
111 int |
A > B |
B > A |
50000 int |
5E1 float |
A > B |
B > A |
Étant donné que les valeurs émanant de différentes familles de types de données doivent être explicitement converties avant d'être référencées dans les prédicats de comparaison, les effets de ces règles ne sont observables qu'au moment de l'ordonnancement des ensembles de résultats sur une colonne sql_variant. Les valeurs du tableau suivant illustrent les règles de priorité appliquées aux types de données.
PriKey |
VariantCol |
|---|---|
1 |
50,0 (type de base float) |
2 |
5000 (type de base int) |
3 |
'124000' (type de base char(6)) |
Le tableau suivant indique le résultat de l'instruction : SELECT * FROM VariantTest ORDER BY VariantCol ASC.
PriKey |
VariantCol |
|---|---|
3 |
'124000' (type de base char(6)) |
2 |
5000 (type de base int) |
1 |
50,0 (type de base float) |
Les valeurs du tableau suivant illustrent les règles de priorité appliquées aux classements lorsque différents classements sont utilisés.
IntKey |
VariantCol |
|---|---|
1 |
qrs (type varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
2 |
abc (type varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
3 |
qrs (type varchar SQL_Latin1_General_CP1_CS_AS) |
4 |
17,5 (type décimal) |
5 |
abc (type varchar SQL_Latin1_General_CP1_CS_AS) |
6 |
klm (type varchar SQL_Latin1_General_CP1_CS_AS) |
7 |
1,2 (type décimal) |
Le tableau suivant indique le résultat de l'instruction : SELECT * FROM CollateTest ORDER BY VariantCol. Le tableau suivant montre le regroupement de valeurs de la famille du type de données nombre exact et de valeurs varchar par classement respectif.
IntKey |
VariantCol |
|---|---|
5 |
abc (type varchar SQL_Latin1_General_CP1_CS_AS) |
6 |
klm (type varchar SQL_Latin1_General_CP1_CS_AS) |
3 |
qrs (type varchar SQL_Latin1_General_CP1_CS_AS) |
2 |
abc (type varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
1 |
qrs (type varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
7 |
1,2 (type décimal) |
4 |
17,5 (type décimal) |
Fonctions et données sql_variant
Les fonctions Transact-SQL suivantes prennent en charge les paramètres sql_variant et retournent une valeur sql_variant lorsqu'un paramètre sql_variant est spécifié :
COALESCE |
MIN |
MAX |
NULLIF |
Les fonctions suivantes prennent en charge les références aux variables et colonnes sql_variant et n'utilisent pas sql_variant comme type de données pour les valeurs de retour :
COL_LENGTH |
DATALENGTH |
TYPEPROPERTY |
COLUMNPROPERTY |
ISNULL |
|
Les fonctions Transact-SQL suivantes ne prennent pas en charge les paramètres sql_variant :
AVG |
RADIANS |
STDEV[P] |
IDENTITY |
ROUND |
SUM |
ISNUMERIC |
SIGN |
VAR[P] |
POWER |
|
|
Les fonctions CAST et CONVERT prennent en charge sql_variant.
La nouvelle fonction SQL_VARIANT_PROPERTY() permet d'obtenir des informations sur les propriétés des valeurs sql_variant, telles que le type de données, la précision ou l'échelle.
Autres éléments Transact-SQL et données sql_variant
Les colonnes sql_variant ne sont pas prises en charge dans le prédicat LIKE.
Les colonnes sql_variant ne sont pas prises en charge dans les index de texte intégral. Les colonnes sql_variant ne peuvent pas être spécifiées dans les fonctions de texte intégral telles que CONTAINSTABLE et FREETEXTTABLE.
Les instructions Transact-SQL suivantes prennent en charge la spécification de valeurs sql_variant dans les mêmes emplacements syntaxiques que les autres types de données integer :
ALTER PROCEDURE
ALTER TABLE
CREATE PROCEDURE
CREATE TABLE
DECLARE variable
Les composants de catalogue SQL Server contiennent des informations sur les colonnes sql_variant.
Le résultat de l'expression CASE est sql_variant si l'une des expressions d'entrée ou de résultat est de type sql_variant. Le type de base sous-jacent du résultat est celui de l'expression retournée lors de l'exécution.
Les opérandes d'opérateurs de concaténation numérique ou de chaînes ne peuvent pas être de type sql_variant : Par exemple, le code suivant génère une erreur :
SELECT VariantCol + @CharacterVar
FROM MyTable
Toutefois, vous pouvez convertir l'opérande sql_variant pour réaliser l'opération :
SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar
FROM MyTable
Applications et données sql_variant
Si une application demande un jeu de résultats dans lequel une colonne retourne des données sql_variant de type de données de base sous-jacent unique, elle peut utiliser les fonctions CAST ou CONVERT dans les instructions Transact-SQL pour retourner les données sql_variant à l'aide du type de données de base sous-jacent. Dans ce cas, l'application traite les données comme une colonne de jeu de résultats du type de données de base sous-jacent.
Le fournisseur OLE DB SQL Server Native Client pour SQL Server propose un type OLE DB DBTYPE_SQLVARIANT propre au fournisseur utilisable avec les paramètres et colonnes sql_variant.
Le pilote ODBC SQL Server Native Client SQL Server propose un type de données de base de données ODBC SQL_SS_VARIANT propre au fournisseur utilisable avec les paramètres et colonnes sql_variant.
SQL Server convertit les valeurs sql_variant en nvarchar(4000) lorsque vous utilisez des applications qui se sont connectées à l'aide des interfaces suivantes :
le fournisseur OLE DB pour SQL Server version 7.0 ;
le pilote ODBC SQL Server de SQL Server 7.0.
Si la chaîne obtenue excède 4 000 caractères, SQL Server retourne les 4 000 premiers caractères.
SQL Server convertit les valeurs sql_variant en varchar(255) lorsqu'il utilise des applications qui se sont connectées à l'aide des interfaces suivantes :
- les pilotes ODBC SQL Server de SQL Server version 6.5 ou antérieure.
Si la chaîne obtenue excède 255 caractères, SQL Server retourne les 255 premiers caractères.