INF: Table with Sql_variant Column Returns Rows That Match INT, MONEY, DECIMAL Data Types When INT Specified in WHERE Clause (270052)

SUMMARY

A table that has a column with sql_variant data type can store data of different types in the same column. If both of the following conditions are true:

The values stored in the sql_variant column are of type int, money, and decimal.

-and-
The table is queried comparing one of these three data types to an int in a WHERE clause.

Then all rows consisting of int, money, and decimal data types are returned.

MORE INFORMATION

The data types int, money, and decimal belong to the same "data type family" for sql_variant comparison (the family known as "exact number"). Comparisons made between data types in the same family will be made based on the data type's position in the data type hierarchy. The lower ranking data type will first be converted to the other data type before the comparison is made. This behavior is by design.

The following sample code demonstrates this behavior:

USE tempdb
GO

CREATE TABLE tbl1 (col1 sql_variant)
GO

INSERT tbl1 VALUES (10)
INSERT tbl1 VALUES ($10)
INSERT tbl1 VALUES (10.00)
INSERT tbl1 VALUES ('10')
GO

SELECT * FROM tbl1 WHERE col1 = 10
-- This query returns all the three rows in the table.

Implicit conversions of the integer 10 are made to int, money, and decimal data types because integers are lowest in the hierarchy. The output of the SELECT statement is:

col1
--------------------------------------------------------
10
10.0000
10.00

(3 row(s) affected)

For more information on the sql_variant data type and type families, refer to the SQL Server Books Online.