Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

sql_variant

SQL Server 2000

A data type that stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant.

sql_variant may be used in columns, parameters, variables, and return values of user-defined functions. sql_variant allows these database objects to support values of other data types.

Syntax

sql_variant

Remarks

A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values. The only types of values that cannot be stored using sql_variant are text, ntext, image, timestamp, and sql_variant.

sql_variant can have a maximum length of 8016 bytes.

An sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction.

sql_variant may be assigned a default value. This data type also may have NULL as its underlying value, but the NULL values will not have an associated base type. In addition, sql_variant may not have another sql_variant as its base type.

A UNIQUE, primary, or foreign key may include columns of type sql_variant, but the total length of the data values comprising the key of a given row should not be greater than the maximum length of an index (currently 900 bytes).

A table may have any number of sql_variant columns.

sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

ODBC does not fully support sql_variant. Hence, queries of sql_variant columns are returned as binary data when using Microsoft OLE DB Provider for ODBC (MSDASQL). For example, an sql_variant column containing the character string data 'PS2091' is returned as 0x505332303931.

Comparing sql_variant values

The sql_variant data type belongs to the top of the data type hierarchy list for conversion. For sql_variant comparisons, the SQL Server data type hierarchy order is grouped into data type families.

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.

See Also

CAST and CONVERT

Using sql_variant_Data

Show:
© 2014 Microsoft