Using sql_variant Data
The sql_variant data type operates similarly to the variant data type in Microsoft® Visual Basic®. It allows a single column, parameter, or variable to store data values of different data types. For example, a single sql_variant column can hold int, decimal, char, binary, and nchar values. Each instance of an sql_variant column records the data value and the meta data information, which includes the base data type, maximum size, scale, precision, and collation.
The sql_variant data type follows these rules:
- General value assignment
- sql_variant objects can hold data of any SQL Server data type except text, ntext, image, and timestamp. 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 an 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 this example, the value of VariantCol is set to NULL with no 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 with 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 an sql_variant value is assigned to an object with 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 meta data contained in an 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 objects can hold data of any SQL Server data type except text, ntext, image, and timestamp. An instance of sql_variant data also cannot have sql_variant as its underlying base data type.
- 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 or foreign keys.
- 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 an sql_variant column to any other data type because there are no supported implicit conversions from sql_variant to other data types.
- 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.
- Collation
- The COLLATE clause cannot be used to assign a column collation to an sql_variant column. The character-based values (char, nchar, varchar, and nvarchar) in an 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 an 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 an 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 an sql_variant instance. Implicit conversions are supported when assigning values from objects with other data types to an sql_variant object.
- The COLLATE clause cannot be used to assign a column collation to an sql_variant column. The character-based values (char, nchar, varchar, and nvarchar) in an sql_variant column can be of any collation, and a single sql_variant column can hold character-based values of mixed collations.
sql_variant Comparisons
sql_variant columns can contain values of several base data types and collations, so special rules apply when comparing sql_variant operands. These rules apply to operations involving 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 | Datetime |
| smalldatetime | Datetime |
| float | approximate number |
| real | approximate number |
| decimal | exact number |
| money | exact number |
| smallmoney | exact number |
| bigint | exact number |
| int | exact number |
| smallint | exact number |
| tinyint | exact number |
| bit | exact number |
| 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 an sql_variant column. The values in this 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)) |
This is 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 this table are examples of the rules regarding collation precedence using 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) |
This is 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 an sql_variant value when an sql_variant parameter is specified:
- COALESCE
- MAX
- MIN
- NULLIF
These 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 |
These 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 function SQL_VARIANT_PROPERTY(): is 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. They cannot be specified in full-text functions such as CONTAINSTABLE and FREETEXTTABLE.
These 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 Microsoft® SQL Server™ 2000 catalog components report information about sql_variant columns.
The result of the CASE expression is sql_variant if any 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:
-- Generates an error:
SELECT VariantCol + @CharacterVar
FROM MyTable
Casting the sql_variant operand can perform the operation:
-- Does not generates an error:
SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar
FROM MyTable
Applications and sql_variant Data
If an application requests a result set in which a given 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 the same way as a result set column of the underlying base data type. This topic describes how Microsoft® SQL Server™ returns sql_variant data that has not been cast or converted to a specific base data type.
The 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 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 working with applications that have connected with 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 4000 characters, SQL Server returns the first 4000 characters.
SQL Server converts sql_variant values to varchar(255) when working with applications that have connected with the following interfaces:
- The SQL Server ODBC Drivers from SQL Server version 6.5 or earlier.
- Any version of the DB-Library dll.
If the resulting string exceeds 255 characters, SQL Server returns the first 255 characters.