You are here:Home » SQL Server » DataType can matter in where condition - a worst case scenario

DataType can matter in where condition - a worst case scenario

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,
 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123
Best case,
 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'
Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.