Microsoft KB Archive/270052

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

Article ID: 270052

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q270052



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 &quot;data type family&quot; for sql_variant comparison (the family known as &quot;exact number&quot;). 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.

Additional query words: datatype datatypes

Keywords: kbinfo KB270052

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.