Jaa

Using sql_variant Data

  • Artikkeli

The sql_variant data type operates similarly to the variant data type in Microsoft Visual Basic. sql_variant enables a single column, parameter, or variable to store data values of different data types. For example, one sql_variant column can hold int, decimal, char, binary, and nchar values. Each instance of a sql_variant column records the data value and the metadata information. This includes the base data type, maximum size, scale, precision, and collation.

Rules for Using sql_variant

The following rules apply to using the sql_variant data type.

General Value Assignment

  • sql_variant objects can hold data of any SQL Server 2005 data type except text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, and Microsoft .NET Framework common language runtime (CLR) user-defined types. An instance of sql_variant data also cannot have sql_variant as its underlying base data type.
  • Constants of any type can be specified in predicates or assignments referencing sql_variant columns.
  • If a sql_variant value is NULL, it is not considered to have an underlying base data type. This rule applies even when the null value comes from a variable or column with a specific data type.
    In the following example, the value of VariantCol is set to NULL without an associated data type, even though the null value came from an int variable:
    DECLARE @IntVar int
    SET @IntVar = NULL
    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123
  • In assignments from sql_variant objects to an object that has any other data type, the sql_variant value must be explicitly cast to the data type of the destination. No implicit conversions are supported when a sql_variant value is assigned to an object that has another data type.
  • For compatibility with other data types, the catalog objects, such as the DATALENGTH function, that report the length of sql_variant objects report the length of the data. The length of the metadata that is contained in a sql_variant object is not returned.
  • sql_variant columns always operate with ANSI_PADDING ON. If char, nchar, varchar, nvarchar, or varbinary values are assigned from a source that has ANSI_PADDING OFF, the values are not padded.

sql_variant in Tables

  • sql_variant columns can be used in indexes and unique keys, as long as the length of the data in the key columns does not exceed 900 bytes.
  • sql_variant columns do not support the IDENTITY property, but sql_variant columns are allowed as part primary key or foreign key.
  • sql_variant columns cannot be used in a computed column.
  • Use ALTER TABLE to change a column of any data type except text, ntext, image, timestamp, or sql_variant to sql_variant. All existing values are converted to sql_variant values whose base data type is the same as the data type of the column before the ALTER TABLE statement was executed. ALTER TABLE cannot be used to change the data type of a sql_variant column to any other data type because there are no supported implicit conversions from sql_variant to other data types.

Collation

  • The COLLATE clause cannot be used to assign a column collation to a sql_variant column. The character-based values (char, nchar, varchar, and nvarchar) in a sql_variant column can be of any collation, and a single sql_variant column can hold character-based values of mixed collations.
  • When a value is assigned to a sql_variant instance, both the data value and base data type of the source are assigned. If the source value has a collation, the collation is also assigned. If the source value has a user-defined data type, the base data type of the user-defined data type is assigned, not the user-defined data type. The sql_variant instance does not inherit any rules or defaults bound to the user-defined data type. If a value from a column with an identity property is assigned to a sql_variant instance, the sql_variant takes the base data type of the source column but does not inherit the IDENTITY property. It is an error to assign a text, ntext, or image value to a sql_variant instance. Implicit conversions are supported when assigning values from objects that have other data types to a sql_variant object.

sql_variant Comparisons

sql_variant columns can contain values of several base data types and collations; therefore special rules apply when you compare sql_variant operands. These rules apply to operations that involve comparisons, such as:

  • Transact-SQL comparison operators
  • ORDER BY, GROUP BY
  • Indexes
  • The MAX and MIN aggregate functions
  • UNION (without ALL)
  • CASE expressions

For sql_variant comparisons, the SQL Server data type hierarchy order is grouped into data type families. The sql_variant family has the highest family precedence.

Data type hierarchy Data type family

sql_variant

sql_variant

datetime

Date and Time

smalldatetime

Date and Time

float

Approximate numeric

real

Approximate numeric

decimal

Exact numeric

money

Exact numeric

smallmoney

Exact numeric

bigint

Exact numeric

int

Exact numeric

smallint

Exact numeric

tinyint

Exact numeric

bit

Exact numeric

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

Binary

binary

Binary

uniqueidentifier

Uniqueidentifier

These rules apply to sql_variant comparisons:

  • When sql_variant values of different base data types are compared, and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the higher of the two values.
  • When sql_variant values of different base data types are compared, and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
  • When sql_variant values of the char, varchar, nchar, or varchar data types are compared, they are evaluated based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed.

These rules can yield different results for comparisons between sql_variant values than comparisons between values of the same base data type.

Operand A Operand B Non-variant comparison result sql_variant comparison result

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

Because values from different data type families must be explicitly cast before being referenced in comparison predicates, the effects of the rules are observed only when ordering result sets on a sql_variant column. The values in the following table are examples of the rules regarding data type precedence.

PriKey VariantCol

1

50.0 (base type float)

2

5000 (base type int)

3

'124000' (base type char(6))

The following table shows the result of the statement: SELECT * FROM VariantTest ORDER BY VariantCol ASC.

PriKey VariantCol

3

'124000' (base type char(6))

2

5000 (base type int)

1

50.0 (base type float)

The values in the following table are examples of the rules regarding collation precedence that uses different collations.

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)

The following table shows the result of the statement: SELECT * FROM CollateTest ORDER BY VariantCol. This table shows values from the exact number data type family grouped together, and varchar values grouped within their respective collations.

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)

Functions and sql_variant Data

The following Transact-SQL functions support sql_variant parameters and return a sql_variant value when a sql_variant parameter is specified:

COALESCE

MIN

MAX

NULLIF

The following functions support references to sql_variant columns or variables and do not use sql_variant as the data type of their return values:

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

The following Transact-SQL functions do not support sql_variant parameters:

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

The CAST and CONVERT functions support sql_variant.

The new SQL_VARIANT_PROPERTY() function can be used to obtain property information about sql_variant values, such as data type, precision, or scale.

Other Transact-SQL Elements and sql_variant Data

sql_variant columns are not supported in the LIKE predicate.

sql_variant columns are not supported in full-text indexes. sql_variant columns cannot be specified in full-text functions such as CONTAINSTABLE and FREETEXTTABLE.

The following Transact-SQL statements support specifying sql_variant in the same syntax locations that other integer data types are specified:

  • ALTER PROCEDURE
  • ALTER TABLE
  • CREATE PROCEDURE
  • CREATE TABLE
  • DECLARE variable

The SQL Server 2005 catalog components report information about sql_variant columns.

The result of the CASE expression is sql_variant if any one of the input or result expressions evaluate to sql_variant. The underlying base type of the result is that of the expression evaluated as the result at run time.

Operands of numeric or string concatenation operators cannot be sql_variant. For example, the following code generates an error:

SELECT VariantCol + @CharacterVar

FROM MyTable

However, by casting the sql_variant operand, you can perform the operation:

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

FROM MyTable

Applications and sql_variant Data

If an application requests a result set in which a specific column returns sql_variant data of a single underlying base data type, the application can use the CAST or CONVERT functions in the Transact-SQL statements to return the sql_variant data by using the underlying base data type. In this case, the application treats the data just like a result set column of the underlying base data type.

The SQL Native Client OLE DB Provider for SQL Server introduces a provider-specific OLE DB type DBTYPE_SQLVARIANT for use with sql_variant columns and parameters.

The SQL Server SQL Native Client ODBC Driver introduces a provider-specific ODBC database data type SQL_SS_VARIANT for use with sql_variant columns and parameters.

SQL Server converts sql_variant values to nvarchar(4000) when you are working with applications that have connected by using the following interfaces:

  • The OLE DB Provider for SQL Server version 7.0.
  • The SQL Server ODBC Driver from SQL Server 7.0.

If the resulting string exceeds 4,000 characters, SQL Server returns the first 4,000 characters.

SQL Server converts sql_variant values to varchar(255) when it works with applications that have connected by using the following interfaces:

  • The SQL Server ODBC Drivers from SQL Server version 6.5 or earlier.

If the resulting string exceeds 255 characters, SQL Server returns the first 255 characters.

See Also

Other Resources

CAST and CONVERT (Transact-SQL)
sql_variant (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)
Data Types (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance