• Artículo

Usar datos sql_variant

El tipo de datos sql_variant funciona de la misma manera que el tipo de datos variant en MicrosoftVisual Basic. El tipo de datos sql_variant permite que una columna, una parámetro o una variable almacenen valores de datos de distintos tipos. Por ejemplo, una columna sql_variant puede contener valores int, decimal, char, binary y nchar. Cada instancia de una columna sql_variant registra el valor de los datos y la información de metadatos. Estos datos incluyen el tipo de datos base, el tamaño máximo, la escala, la precisión y la intercalación.

Reglas para utilizar sql_variant

Se aplican las siguientes reglas para utilizar el tipo de datos sql_variant.

Asignación de valor general

  • Los objetos sql_variant pueden contener datos de cualquier tipo de datos de SQL Server, excepto text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, y los tipos de datos definidos por el usuario para CLR (Common Language Runtime) de Microsoft.NET Framework. Una instancia de datos sql_variant no puede tener sql_variant como tipo de datos base subyacente.

  • Es posible especificar constantes de cualquier tipo en predicados o en asignaciones que hacen referencia a columnas sql_variant.

  • Si un valor sql_variant es NULL, no se considera que tiene un tipo de datos base subyacente. Esta regla se aplica incluso cuando el valor NULL viene de una variable o una columna con un tipo de datos determinado.

    En el siguiente ejemplo, el valor de VariantCol está establecido en NULL sin ningún tipo de datos asociado, aunque el valor NULL venga de una variable int:

    DECLARE @IntVar int

    SET @IntVar = NULL

    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123

  • En las asignaciones de objetos sql_variant a un objeto con otro tipo de datos, el valor sql_variant debe convertirse explícitamente al tipo de datos de destino. No se admiten conversiones implícitas cuando se asigna un valor sql_variant a un objeto con otro tipo de datos.

  • Por compatibilidad con otros tipos de datos, los objetos de catálogo (como la función DATALENGTH) que indican la longitud de los objetos sql_variant comunican la longitud de los datos. No se devuelve la longitud de los metadatos contenidos en un objeto sql_variant.

  • Las columnas sql_variant siempre funcionan con ANSI_PADDING en ON. Si se asignan valores char, nchar, varchar, nvarchar o varbinary de un origen que tiene ANSI_PADDING en OFF, los valores no se rellenan.

  • La actualización de una columna en el suscriptor da lugar al cambio de tipo base en otra columna sql_variant. El procedimiento siguiente muestra este concepto:

    1. Cree una publicación/suscripción combinada. La tabla publicada debe tener una columna sql_variant y una columna c1. Agregue algunos datos a la columna sql_variant. El tipo base de los datos es datetime.

    2. Después de la sincronización inicial, el tipo base en el suscriptor sigue siendo datetime.

    3. Actualice la columna c1 en el suscriptor.

    4. Los datos de la columna sql_variant en el publicador se han cambiado a datetime2.

sql_variant en tablas

  • Las columnas sql_variant se pueden utilizar en índices y claves únicas, siempre que la longitud de los datos de las columnas de clave no pase de 900 bytes.

  • Las columnas sql_variant no admiten la propiedad IDENTITY, pero se permiten como parte de claves principales o externas.

  • Las columnas sql_variant no se pueden utilizar en una columna calculada.

  • Utilice ALTER TABLE para cambiar columnas de cualquier tipo de datos, excepto text, ntext, image, timestamp o sql_variant a sql_variant. Todos los valores existentes se convierten en valores sql_variant, cuyo tipo de datos base es el mismo de la columna antes de que se ejecutase la instrucción ALTER TABLE. No es posible utilizar ALTER TABLE para cambiar el tipo de datos de una columna sql_variant a ningún otro tipo de datos porque no se admiten conversiones implícitas de sql_variant a otros tipos de datos.

Intercalación

  • La cláusula COLLATE no se puede utilizar para asignar una intercalación de columna a una columna sql_variant. Los valores basados en caracteres (char, nchar, varchar y nvarchar) de una columna sql_variant pueden ser de cualquier intercalación y una sola columna sql_variant puede tener valores basados en caracteres de intercalaciones mixtas.

  • Cuando se asigna un valor a una instancia de sql_variant, se asignan tanto el valor de los datos como el tipo de datos base del origen. Si el valor de origen tiene una intercalación, también se asigna la intercalación. Si el valor de origen tiene un tipo de datos definido por el usuario, se asigna el tipo de datos base del tipo de datos definido por el usuario, no el tipo de datos definido por el usuario. La instancia de sql_variant no hereda ninguna regla ni valor predeterminado enlazados al tipo de datos definido por el usuario. Si se asigna un valor de una columna con una propiedad de identidad a una instancia de sql_variant, sql_variant obtiene el tipo de datos base de la columna de origen, pero no hereda la propiedad IDENTITY. Es un error asignar un valor text, ntext o image a una instancia de sql_variant. Se admiten conversiones implícitas cuando se asignan valores de objetos con otros tipos de datos a un objeto sql_variant.

Comparaciones de sql_variant

Las columnas sql_variant pueden contener valores de varios tipos de datos base e intercalaciones; por tanto, cuando se comparan operandos sql_variant se aplican reglas especiales. Estas reglas se aplican a operaciones en las que intervienen comparaciones, como:

  • Operadores de comparación de Transact-SQL

  • ORDER BY, GROUP BY

  • Índices

  • Las funciones de agregado MAX y MIN

  • UNION (sin ALL)

  • Expresiones CASE

En las comparaciones de sql_variant, el orden de la jerarquía de tipos de datos de SQL Server se agrupa en familias de tipos de datos. La familia sql_variant tiene la máxima prioridad de familia.

Jerarquía de tipos de datos

Familia de tipos de datos

sql_variant

sql_variant

datetime

Fecha y hora

smalldatetime

Fecha y hora

Float

Valor numérico aproximado

Real

Valor numérico aproximado

decimal

Valor numérico exacto

money

Valor numérico exacto

smallmoney

Valor numérico exacto

bigint

Valor numérico exacto

int

Valor numérico exacto

smallint

Valor numérico exacto

tinyint

Valor numérico exacto

bit

Valor numérico exacto

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

Binario

binary

Binario

uniqueidentifier

Uniqueidentifier

Estas reglas se aplican a comparaciones de sql_variant:

  • Cuando se comparan valores sql_variant con distintos tipos de datos base y dichos tipos base se encuentran en familias de tipos de datos diferentes, el valor cuya familia de tipo de datos tenga prioridad en el diagrama de jerarquía se considera el mayor de los dos valores.

  • Cuando se comparan valores sql_variant de distintos tipos de datos base y éstos se encuentran en la misma familia de tipos de datos, el valor cuyo tipo de datos base tenga menor prioridad en el diagrama de jerarquía se convierte implícitamente al otro tipo de datos y, a continuación, se realiza la comparación.

  • Cuando se comparan valores sql_variant de los tipos de datos char, varchar, nchar o varchar, se evalúan por los siguientes criterios: LCID, versión de LCID, indicadores de comparación e Id. de orden. Cada uno de estos criterios se compara como valores enteros y en el orden enumerado.

Estas reglas pueden producir en comparaciones entre valores sql_variant resultados distintos a los obtenidos en comparaciones entre valores del mismo tipo de datos base.

Operando A

Operando B

Resultado de la comparación no variant

Resultado de la comparación sql_variant

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

Debido a que los valores de distintas familias de tipos de datos deben convertirse explícitamente antes de que se haga referencia a ellos en los predicados de comparaciones, los efectos de las reglas sólo se observan cuando se ordenan los conjuntos de resultados en una columna sql_variant. Los valores de la siguiente tabla son ejemplos de las reglas relacionadas con la precedencia de tipos de datos.

PriKey

VariantCol

1

50,0 (tipo de base float)

2

5000 (tipo de base int)

3

'124000' (tipo de base char(6))

La tabla siguiente muestra el resultado de la instrucción: SELECT * FROM VariantTest ORDER BY VariantCol ASC.

PriKey

VariantCol

3

'124000' (tipo de base char(6))

2

5000 (tipo de base int)

1

50,0 (tipo de base float)

Los valores de la siguiente tabla son ejemplos de reglas relacionadas con la precedencia de intercalación que utiliza distintas intercalaciones.

IntKey

VariantCol

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

4

17,5 (decimal)

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

7

1,2 (decimal)

La tabla siguiente muestra el resultado de la instrucción: SELECT * FROM CollateTest ORDER BY VariantCol. Esta tabla muestra los valores de la familia de tipos de datos numéricos exactos agrupados y los valores varchar agrupados en sus intercalaciones respectivas.

IntKey

VariantCol

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

7

1,2 (decimal)

4

17,5 (decimal)

Funciones y datos sql_variant

Las siguientes funciones de Transact-SQL admiten parámetros sql_variant y devuelven un valor sql_variant cuando se especifica un parámetro sql_variant:

COALESCE

MIN

MAX

NULLIF

Las siguientes funciones admiten referencias a columnas o variables sql_variant y no utilizan sql_variant como el tipo de datos de sus valores devueltos.

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

Las siguientes propiedades Transact-SQL no admiten parámetros sql_variant:

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

Las funciones CAST y CONVERT admiten sql_variant.

Se puede utilizar la nueva función SQL_VARIANT_PROPERTY() para obtener información de propiedades acerca de valores sql_variant, como el tipo de datos, la precisión o la escala.

Otros elementos Transact-SQL y datos sql_variant

En el predicado LIKE no se admiten columnas sql_variant.

Las columnas sql_variant no se admiten en los índices de texto. Las columnas sql_variant no se pueden especificar en funciones de texto como CONTAINSTABLE y FREETEXTTABLE.

Las siguientes instrucciones Transact-SQL admiten la especificación de sql_variant en las mismas ubicaciones de sintaxis donde se especifican otros tipos de datos integer:

  • ALTER PROCEDURE

  • ALTER TABLE

  • CREATE PROCEDURE

  • CREATE TABLE

  • DECLARE variable

Los componentes de catálogo de SQL Server comunican información acerca de las columnas sql_variant.

El resultado de la expresión CASE es de tipo sql_variant si las expresiones de entrada o de resultado se evalúan como sql_variant. El tipo base subyacente del resultado es del tipo de la expresión evaluada como resultado en tiempo de ejecución.

Los operandos de operadores de concatenación de números o de cadenas no pueden ser de tipo sql_variant. Por ejemplo, el siguiente código genera un error:

SELECT VariantCol + @CharacterVar

FROM MyTable

Sin embargo, si se convierte el operando sql_variant se puede realizar la operación:

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

Aplicaciones y datos sql_variant

Si una aplicación solicita un conjunto de resultados en el que determinada columna devuelve datos sql_variant de un único tipo de datos base subyacente, la aplicación puede utilizar las funciones CAST o CONVERT en las instrucciones Transact-SQL para devolver los datos sql_variant mediante el tipo de datos base subyacente. En este caso la aplicación trata los datos de la misma forma que una columna de conjunto de resultados del tipo de datos base subyacente.

El proveedor OLE DB de SQL Server Native Client para SQL Server introduce un tipo OLE DB específico del proveedor DBTYPE_SQLVARIANT para usarlo con columnas y parámetros de sql_variant.

El controlador ODBC de SQL Server Native Client para SQL Server introduce un tipo de datos SQL_SS_VARIANT de base de datos ODBC específico de proveedor para usarlo con columnas y parámetros de sql_variant.

SQL Server convierte los valores sql_variant a nvarchar(4000) cuando se trabaja con aplicaciones que se conectan mediante las siguientes interfaces:

  • El proveedor OLE DB para SQL Server versión 7.0.

  • El controlador ODBC para SQL Server a partir de SQL Server 7.0.

Si la cadena de resultado excede de 4.000 caracteres, SQL Server devuelve los primeros 4.000 caracteres.

SQL Server convierte los valores sql_variant a varchar(255) cuando funciona con aplicaciones que se conectan mediante las siguientes interfaces:

  • Los controladores ODBC para SQL Server a partir de SQL Server versión 6.5 o anteriores.

Si la cadena de resultado excede de 255 caracteres, SQL Server devuelve los primeros 255 caracteres.