DataType can matter in where condition - a worst case scenario by serverku

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.

Table scan or Unexpected output due to improper where condition applied for DateTime filter by serverku

When you are using date filter with queries, like to find the records from tables for the particular year/month, we are approaching some extra data type conversion on fields. Below are some examples.

Should not use,

 
-- #1. : This cause to table scan
SELECT *  
FROM OrderDetails
WHERE datepart(month,Orderdate) =?
AND datepart(year,Orderdate) = ?

-- #2. : This cause to unexpected output 
SELECT * 
FROM OrderDetails
WHERE convert(varchar,OrderDate,112) between
'20110101' and '20110131' 

Should use,

 
-- #1.
SELECT * 
FROM OrderDetails
WHERE convert(varchar,Orderdate,112) >= '20110101' 
AND convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

-- #2.
SELECT * 
FROM OrderDetails
WHERE cast (OrderDate as datetime) BETWEEN
 '2011-01-01 00:00:00.000' AND '2011-01-31 23:59:59.900'

Create table with sample records or use existing table which has datetime data type field, Run query above and check the results with execution plan. Hope you like it.