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...
- 3. Filtering and Sorting Data
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 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
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/57758/