Two rows look identical in result set but group by return two group

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

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

 

Why this happens.

GROUP BY on sql_variant considers both the underlying data type and the stored value.
Two values that display the same may belong to different base types, so SQL Server treats them as different groups.

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 grouping the data

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.