"Go" Comparison of SQL Server sql_variant types

The sql_variant type is used to store various data types that are supported in SQL Server.

For sql_variant comparisons, the SQL Server data type hierarchy sequence is divided into multiple data type systems, and thesql_variant data type is at the top of the list of data type hierarchies used for transformations.

Data type hierarchy	Data Type Series
sql_variant	sql_variant
DateTime2	Date and time
DateTimeOffset	Date and time
Datetime	Date and time
smalldatetime	Date and time
Date	Date and time
Time	Date and time
Float	Approximate values
Real	Approximate values
Decimal	Exact values
Money	Exact values
SmallMoney	Exact values
bigint	Exact values
Int	Exact values
smallint	Exact values
tinyint	Exact values
Bit	Exact values
nvarchar	Unicode
NChar	Unicode
varchar	Unicode
Char	Unicode
varbinary	Binary
Binary	Binary
uniqueidentifier	uniqueidentifier

The following rules apply to sql_variant comparisons:

• When a sql_variant value of a different base data type is compared and the base data type belongs to a different data type family, the higher values in the data type series in the hierarchy chart are considered to be larger in two values.

• When a sql_variant value of a different base data type is compared, and the base data type is of the same data type family, values that are lower in the underlying data type are implicitly converted to other data types in the hierarchy chart before being compared.

• when you compare thesql_variant values of a char, varchar,nchar, or nvarchar data type , the collation of these values is first compared based on the following criteria: LCID, LCID version, Comparison flag, and sort Id. Each of these conditions is compared as an integer value in the order listed. If all of these conditions are equal, the actual string values are compared by collation.

For example:
Begin
Declare
@v1 sql_variant = cast (' 15.00 ' as float (53)),
@v2 sql_variant = cast (' 15.00 ' as Decimal (18,4)),
@v3 sql_variant = cast (' As-tinyint '),
@v4 sql_variant = cast (' 00:00:15.00 ' as time)

Select
Case when @v1 > @v2
Then ' v1 > V2 '
When @v2 > @v1
Then ' v2 > V1 '
Else ' v2 = V1 '--
End
Case when @v1 > @v3
Then ' v1 > V3 '
When @v3 > @v1
Then ' v3 > V1 '
Else ' v3 = V1 '--
End
Case when @v2 > @v3
Then ' v2 > V3 '
When @v3 > @v2
Then ' V3 > V2 '
Else ' v2 = V3 '--
End
Case when @v1 > @v4
Then ' v1 > V4 '
When @v4 > @v1
Then ' v4 > V1 '--
Else ' v4 = V1 '
End
End

@v1 and @v2, because the basic type of @v1 float belongs to the approximate series of values, @v2 basic type decimal belongs to the exact data series, so ' v1 > V2 '

@v1 and @v3, because @v1 's basic type float is an approximate series of values, the basic type of @v3 tinyint belongs to the exact data series, so ' v1 > V3 '

@v2 and @v3, because the basic type of @v2, the basic type of decimal and @v3 tinyint belong to the exact data series, V3 implicitly converts to the decimal type, so v2 = V3

@v1 and @v4, because the basic type of @v1 float belongs to the approximate series, the basic type of @v4 time belongs to the date and the Times, so v4 > v1

"Go" Comparison of SQL Server sql_variant types