O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE COALESCE(shippeddate, '19000101') = COALESCE(@dt, '19000101');

The problem is that even though the solution now returns the correct result—even when the input is NULL—the predicate isn’t a search argument. This means that SQL Server cannot efficiently use an index on the shippeddate column. To make the predicate a search argument, you need to avoid manipulating the filtered column and rewrite the predicate like the following.

SELECT orderid, orderdate, empid
FROM Sales.Or...


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


Instead of manipulating the filtered columns OR AND statements were used to build a predicate with manipulation. Any manipulation of a filtered column prevents the predicate from being a search argument. Unclear if a a manipulated filter column in a predicate is better or worse than a table expression