How can CONVERT in SELECT clause execute before JOINS.

Faisal 2015-09-10 18:37:36

In the attached query plan, I am converting Object11.Column12 (alias Object4.Column12) in the select list. But, I was expecting the conversion to happen after Object11 is inner joined with Object12, but, in the attached plan it looks like that sql server is evaluating part of SELECT clause before the FROM clause.

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:

alt text

and query, with conversion and join lines highlighted.

alt text

/storage/attachments/1359-Plan_Anonymized.queryanalysis
Aaron Bertrand 2015-09-10 18:40:18
While there is an expected logical order of operations in a query, you can't control or predict how SQL Server will process this under the covers. This happens all the time – try to convert a string to datetime, and you get a conversion error, even though the where clause has WHERE ISDATE() = 1. Your TRY_CONVERT() workaround is one way (in older versions I would use a CASE expression).
SQLkiwi 2015-09-12 03:22:56
The query only defines the logical results – it is up to the optimizer to turn that into an efficient physical execution plan. The optimizer requires many freedoms in order to achieve that goal: it needs to be able to reorder joins, aggregates, and scalar computations for example.

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.

September 10, 2015
No Comments