Query returns zero rows but we can see the records with value in the table

This is an interview question. An interviewer shows the table like below

But when we are writing the query below, we are not getting any records why?

SELECT     ID,     DataValue FROM sql_variant_Puzzles_Demo where DataValue=100

 

What is the reason behind it.

Reason behind it is data type of the column name DataValue. Data type of this column is sql_variant, it can store different type of data. In the above example we are searching int value but there may be change to store the data 100 as varchar or nvarchar or char . due to that it is not returning any row.

Read more about sql_variant  : https://bageshkumarbagi-msbi.blogspot.com/2026/05/sqlvariantproperty-function-in-sql.html

SQL_VARIANT data type in SQL Server : https://bageshkumarbagi-msbi.blogspot.com/2026/05/sqlvariant-data-type-in-sql-server.html

Let’s see the demo.

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 ('100');           INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES ('100.0');          INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES ('100');       INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES (500);       INSERT INTO sql_variant_Puzzles_Demo (DataValue) VALUES (400);

Table created and data inserted successfully. Now see the records

These 100 data types are varchar.

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType') FROM sql_variant_Puzzles_Demo

When we are filtering with int it will not return any data.

SELECT     ID,     DataValue,               SQL_VARIANT_PROPERTY(DataValue, 'BaseType') FROM sql_variant_Puzzles_Demo where DataValue=100