When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail since both should have empty result sets. Any help/insight is appreciated.
create table #temp
(id int primary key)
create table #temp2
(id int)
select 1/0
from #temp
where id = 1
select 1/0
from #temp2
where id = 1
TopAnswers
Databases
Top Answer
Paul White
An initial look at the execution plans shows that the expression `1/0` is defined in the Compute Scalar operators:
![Graphical plans][1]
Now, even though execution plans do start executing at the far left, iteratively calling `Open` and `GetRow` methods on child iterators to return results, SQL Server 2005 and later contains an optimization whereby expressions are often only *defined* by a Compute Scalar, with [evaluation deferred until a subsequent operation requires the result][2]:
![Compute Scalar operators that appear in Showplans generated by SET STATISTICS XML might not contain the RunTimeInformation element. In graphical Showplans, Actual Rows, Actual Rebinds, and Actual Rewinds might be absent from the Properties window when the Include Actual Execution Plan option is selected in SQL Server Management Studio. When this occurs, it means that although these operators were used in the compiled query plan, their work was performed by other operators in the run-time query plan. Also note that the number of executes in Showplan output generated by SET STATISTICS PROFILE is equivalent to the sum of rebinds and rewinds in Showplans generated by SET STATISTICS XML. From: MSDN Books Online][3]
In this case, the expression *result* is only needed when assembling the row for return to the client (which you can think of occurring at the green `SELECT` icon). By that logic, deferred evaluation would mean the expression is never evaluated because neither plan generates a return row. To labour the point a little, neither the Clustered Index Seek nor the Table Scan return a row, so there is no row to assemble for return to the client.
However, there is a separate optimization whereby some expressions can be identified as [runtime constants][4] and so **evaluated once before query execution begins**. In this case, an indication this has occurred can be found in the showplan XML (Clustered Index Seek plan on the left, Table Scan plan on the right):
![Showplan XML][5]
I wrote more about the underlying mechanisms and how they can affect performance [in this blog post][6]. Using information provided there, we can modify the first query so both expressions are evaluated and cached before execution starts:
```sql
select 1/0 * CONVERT(integer, @@DBTS)
from #temp
where id = 1
select 1/0
from #temp2
where id = 1
```
Now, the first plan also contains a constant expression reference, and both queries produce the error message. The XML for the first query contains:
![Constant Expression][7]
More information: [Compute Scalars, Expressions and Performance][8]
[1]: https://i.stack.imgur.com/Kz8Kv.png
[2]: https://technet.microsoft.com/en-us/library/ms178082.aspx
[3]: https://i.stack.imgur.com/7BoZq.png
[4]: https://www.sqlskills.com/blogs/conor/rand-and-other-runtime-constant-functions-redux/
[5]: https://i.stack.imgur.com/d2R6e.png
[6]: https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html
[7]: https://i.stack.imgur.com/kcQkL.png
[8]: https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html