Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Feb 13 at 20:20

This question came from our site for professional and enthusiast programmers.

    
@agentnega - yakshemash. focus on the question instead. –  Borat Sagdiyev Feb 13 at 18:42
2  
Good question...verified in SQL 2008. The first temp table created returns 0 rows, and the second temp table returns an error regardless of number of rows. –  Twelfth Feb 13 at 18:46
1  
Great question :) –  SchmitzIT Feb 13 at 19:01

2 Answers 2

up vote 30 down vote accepted

An initial look at the execution plans shows that the expression 1/0 is defined in the Compute Scalar operators:

Graphical plans

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:

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

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 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

I wrote more about the underlying mechanisms and how they can affect performance in this blog post. Using information provided there, we can modify the first query so both expressions are evaluated and cached before execution starts:

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

More information: Compute Scalars, Expressions and Performance

share|improve this answer
10  
+1 Great answer, Paul. –  Thomas Stringer Feb 13 at 21:16
1  
+1 . . . See, I did attract real guru ;) –  Gordon Linoff Feb 15 at 13:40

I am going to intelligently guess (and in the process probably attract a SQL Server guru who might give a really detailed answer).

The first query approaches the execution as:

  1. Scan the primary key index
  2. Look up the values in the data table needed for the query

It chooses this path because you have a where clause on the primary key. It never gets to the second step, so the query does not fail.

The second doesn't have a primary key to run on, so it approaches the query as:

  1. Do a full table scan of the data and retrieve the necessary values

One of those values is 1/0 causing the problem.

This is an example of SQL Server optimizing the query. For the most part, this is a good thing. SQL Server will move conditions from the select into the table scan operation. This often saves steps in the evaluation of the query.

But, this optimization is not an unmitigated good thing. In fact, it seems to violate the SQL Server documentation itself which says that the where clause is evaluated prior to the select. Well, they might have some erudite explanation for what this means. To most humans, though, logically processing the where before the select would mean (among other things) "do not generate select-clause errors on rows not returned to the user".

share|improve this answer
1  
+1 no clue if you are right, but best answer I can see given the only difference is the primary key. –  Twelfth Feb 13 at 18:49
1  
@GordonLinoff Paul Randal just confirmed via Twitter that your reply was bang on. –  SchmitzIT Feb 13 at 19:12
3  
@Still, the actual order of execution, however different, should not lead to error messages like that. –  ypercube Feb 13 at 21:00
5  
@ypercube Erland Sommarskog would agree with you (Connect item) –  Paul White Feb 13 at 21:04
2  
Thank you for the pointer -- I logged in and upvoted the request. –  Gordon Linoff Feb 13 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.