How can CONVERT in SELECT clause execute before JOINS.
I thought the SELECT clause should execute only on the final resultset of the FROM clause, are there any exceptions to this?, I have changed CONVERT to TRY_CONVERT to get rid of this issue, but, is there a way to force evaluate JOINS before this conversion?
Here is the plan diagram:
and query, with conversion and join lines highlighted.
It generally tries to push expressions as far down the tree as it can, because this helps with index matching, statistics generation, and use of any computed columns that might be present. This is part of the reason SQL Server makes no guarantees about the timing or number of executions of scalar expressions in the query. The controversial element here is that this reordering can, in some circumstances, cause runtime errors (e.g. due to conversion failures) that logically could not happen if the query were executed literally as written.
While controversial for some, this is the way query optimization in SQL Server has worked for a very long time. It may change at some point in the future, but for the time being, query writers need to be aware of the possibility, and code for safety using CASE or TRY_CONVERT, for example.