There are two main issues which cause implicit (or explicit) conversion to make a big difference to the query plan:
The main point of problems is where a join or filter predicate, or an ordering or grouping, is over a converted column. Note also that this applies equally to any function on a column used in such a context. A column which is just selected has far less impact.
This means firstly, that the compiler cannot use an index lookup to get the data, nor can it rely on the conversion output to be in the correct order for operations such as a Merge join or Stream Aggregate.
This particular point is far more insidious than some realize. With some conversions, for example
float, the compiler understands these to be order-preserving. But conversions such as
nvarchar have no such semantic.
Therefore often, when joining on say
nvarchar column, it is necessary to decide which table will be the one to be seeked, and therefore often an explicit conversion is better.
The second issue is the more well-known one: no statistics. The compiler has no understanding of what the result of the conversion implies, therefore it cannot use statistics to determine estimated rowcounts and densities. This can often affect the whole plan, and often makes a bigger difference the deeper in the plan it is.
If the conversion or function is only in the select (and is not predicated in outer parts of the query), then there is far less impact, even though the same warning occurs. In this case, normally the conversion will happen just before the row is output from the query, it does not normally affect anytthe query plan.
Two cases where
select can make a difference are:
- Conversions to much larger types, such as the LOB
max types. it is beneficial to ensure that the conversion happens as late as possible.
- Very complex nested string functions, that are passed to each other via
CROSS APPLY. Here, the opposite is true: do the calculation earlier, and prevent the compiler trying to nest the whole thing as one big Compute Scalar.
OUTER APPLY is your friend, it seems to give an optimization-fence effect.