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:
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
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:
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:
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) |
The following Transact-SQL functions support sql_variant parameters and return an sql_variant value when an sql_variant parameter is specified:
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.
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:
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
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:
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:
If the resulting string exceeds 255 characters, SQL Server returns the first 255 characters.