Complex expressions
Search predicates should not use complex expressions. Much like the deterministic function calls we discussed in the Functions in our predicate section, complex expressions can also cause unnecessary scans.
As was discussed in previous chapters, the Query Optimizer uses statistics, internal transformation rules, and heuristics at compile time to determine a good enough plan to execute a query. This includes the ability to fold expressions, which is the process of simplifying constant expressions at compile time. For example, a predicate such as WHERE Column = 320 * 200 * 32 is computed at compile time to its arithmetic result and, internally, the predicate is evaluated as WHERE Column = 2048000. But unlike constants, calculations that involve column values, parameters, non-deterministic functions, or variables are only evaluated at runtime – this is another example of how the Query Optimizer can’t accurately estimate row counts beforehand, resulting...
