Why order by clause not working properly on sql_variant column

ORDER BY on sql_variant is unreliable because SQL Server sorts by data type precedence first and value second, not by logical value. Mixed data types will always be grouped by their underlying type. sql_variant does NOT sort by actual stored value alone. It first sorts by data type precedence, then by value inside that type.

When we are ordering sql_variant it orders on below order

datetime > float > decimal > int > varchar > nvarchar > bit ...

let’s see the demo

here creating a table and inserting few records.

CREATE TABLE sql_variant_Puzzles_Demo (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (1)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(0)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10)              -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2)                -- int INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES (10.0)              -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(2.0)                -- float INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('10')           -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2')            -- varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2024-01-01' AS date))--date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST('2020-01-01' AS date)) --date INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2024-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES('2020-01-01') --varchar INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(1 as bit)) --bit INSERT INTO sql_variant_Puzzles_Demo(DataValue) VALUES(CAST(0 as bit)) --bit

Table created and data inserted successfully.

See the data

Now ordering the data in descending order and see

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType')  as datatype FROM sql_variant_Puzzles_Demo order by DataValue desc

 

Not getting the correct sorting order.