Two rows look identical in result set but distinct by return more rows than expected

This question is asked by interviewer they show two table like below.

Both tables have same records, but when we are using the distinct it return the differ result.

 

Why this happens.

DISTINCT on sql_variant does not deduplicate by displayed value. It compares the underlying data type and metadata as well, so values that appear identical but were stored using different base types remain distinct.

Let’s see the demo.

Creating two table and inserting few records.

CREATE TABLE tbl1 (     ID INT IDENTITY PRIMARY KEY,     DataValue sql_variant );   CREATE TABLE tbl2 (     ID INT IDENTITY PRIMARY KEY,     DataValue int );  insert into tbl1 (DataValue) values (10)         -- int insert into tbl1 (DataValue) values ('10')      -- varchar insert into tbl1 (DataValue) values (CAST(10 AS bigint)) --bigint insert into tbl1 (DataValue) values (CAST('10' AS nvarchar(10))); --nvarchar insert into tbl2 (DataValue) values (10), (10),(10),(10)

See the data

Now using distinct

Main reason is the datatype of both table tbl1 data type is sql_variant which can story any data type but in the seconds table tbl2 has int so in this table we can only insert int data type.

Due to that it tbl1 return 2 rows (int + bigint) and (varchar +nvarchar) while tbl2 has only one datatype.