﻿<?xml version='1.0' encoding='UTF-8'?><rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/"><channel><title>SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis  / Divide by zero / Latest Posts</title><generator>InstantForum.NET v2.9.0</generator><description>SQLServerCentral</description><link>http://www.sqlservercentral.com/Forums/</link><webMaster>notifications@sqlservercentral.com</webMaster><lastBuildDate>Sun, 19 May 2013 14:36:39 GMT</lastBuildDate><ttl>20</ttl><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Koen Verbeeck (12/26/2012)[/b][hr]Checked 1 and 4 and then stopped thinking because I only had to select two :-D[/quote]Same thing happened for me also</description><pubDate>Thu, 02 May 2013 08:49:23 GMT</pubDate><dc:creator>malleswarareddy_m</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>The Correct answer should be View1,View4 and view 5.NULLIF(Divisor, 0) on view5 on the denominator will always return Null and hence the result should be always Null.</description><pubDate>Thu, 07 Feb 2013 23:05:40 GMT</pubDate><dc:creator>binod.soft</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Good One.</description><pubDate>Tue, 15 Jan 2013 13:19:52 GMT</pubDate><dc:creator>asifkareem</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I also answered 1, 4 and 5. Thanks for all the explanations.</description><pubDate>Tue, 15 Jan 2013 05:36:13 GMT</pubDate><dc:creator>Allan Kote</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Hugo Kornelis (12/23/2012)[/b][hr]While hunting for this information, I did find a few Connect items about errors with WHEN (something that's never true) THEN MIN(1/0) or WHEN (something that's always true) THEN 1 ELSE MIN(1/0) - they are the connect items that prompted this clarification to be added to Books Online.[/quote]I think this more a theoretical problem though.SQL Server seems short-cirquit constant expressions (1/0) first, but usually you wont have such an expression in your queries (unless generated by code).Example: The version with 1/0 returns the error, but the semantically identical version with 1/(x-x) does not return the error:[code="sql"]SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/0) ELSE 1 ENDFROM sys.objects GROUP BY object_id[/code][quote]-----------Msg 8134, Level 16, State 1, Line 1Divide by zero error encountered.[/quote][code="sql"]SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/(object_id-object_id))  ELSE 1 ENDFROM sys.objects GROUP BY object_id[/code][quote]...(1456 row(s) affected)[/quote]And we can even short-cirquit version 1 once more to make it work again:[code="sql"]SELECT CASE WHEN 1&amp;lt;&amp;gt;1 THEN MIN(1/0) ELSE 1 END FROM sys.objects GROUP BY object_id[/code][quote]...(1456 row(s) affected)[/quote]SQL Server knows that 1&amp;lt;&amp;gt;1 is never true, and therefore doesn't bother to evaluate MIN(1/0)</description><pubDate>Thu, 10 Jan 2013 03:12:43 GMT</pubDate><dc:creator>Christian Buettner-167247</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I answered 1,4,5 on this one.  It was a good refresher experience.  Thanks for the question!Andre</description><pubDate>Tue, 08 Jan 2013 12:04:51 GMT</pubDate><dc:creator>Andre Ranieri</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Hugo Kornelis (12/23/2012)[/b][hr]I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.[/quote]woah, that's a relief... Hopefully steve comes back soon.  I was able to deduce in my head that 1,4,5 would work, and then was a bit shocked when I was wrong :)All is good Hugo... sometimes the keyboard hates us is all.</description><pubDate>Wed, 02 Jan 2013 07:59:54 GMT</pubDate><dc:creator>mtassin</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>The "official" answer given is not accurate. Views 3 and 6 are not created due to syntax errors and from the rest 1, 4 and 5 don't return errors if the Divider is 0. I gave 1 and 5 as a result but the correct was given for 1 and 4. The question was to indicate 2 views that don't cause error and not the first 2 views that don't cause errors. Here is the code:[code="sql"]create table MyTable(KeyColumn int, Dividend int, Divisor int)goinsert into MyTable values(1,2,0)-- View 1CREATE VIEW dbo.View1ASSELECT KeyColumn, Dividend, Divisor,       CASE WHEN Divisor &amp;lt;&amp;gt; 0 THEN Dividend / Divisor END AS DivisionFROM   dbo.MyTable;go-- View 2CREATE VIEW dbo.View2ASSELECT KeyColumn, Dividend, Divisor,       NULLIF(Dividend / Divisor, 0) AS DivisionFROM   dbo.MyTable;go-- View 3CREATE VIEW dbo.View3ASSELECT KeyColumn, Dividend, Divisor,       IF (Divisor &amp;lt;&amp;gt; 0) THEN Dividend / Divisor AS DivisorFROM   dbo.MyTable;go-- View 4CREATE VIEW dbo.View4ASSELECT KeyColumn, Dividend, Divisor,       Dividend / CASE WHEN Divisor &amp;lt;&amp;gt; 0 THEN Divisor END AS DivisionFROM   dbo.MyTable;go-- View 5CREATE VIEW dbo.View5ASSELECT KeyColumn, Dividend, Divisor,       Dividend / NULLIF(Divisor, 0) AS DivisionFROM   dbo.MyTable;go-- View 6CREATE VIEW dbo.View6ASSELECT KeyColumn, Dividend, Divisor,       Dividend / IF (Divisor &amp;lt;&amp;gt; 0) THEN Divisor AS DivisorFROM   dbo.MyTable;goselect '1' vw,* from dbo.View1goselect '2' vw,* from dbo.View2goselect '4' vw,* from dbo.View4goselect '5' vw,* from dbo.View5go[/code]Results:vw   KeyColumn   Dividend    Divisor     Division---- ----------- ----------- ----------- -----------1    1           2           0           NULL(1 row(s) affected)vw   KeyColumn   Dividend    Divisor     Division---- ----------- ----------- ----------- -----------Msg 8134, Level 16, State 1, Line 1Divide by zero error encountered.vw   KeyColumn   Dividend    Divisor     Division---- ----------- ----------- ----------- -----------4    1           2           0           NULL(1 row(s) affected)vw   KeyColumn   Dividend    Divisor     Division---- ----------- ----------- ----------- -----------5    1           2           0           NULL(1 row(s) affected)</description><pubDate>Wed, 02 Jan 2013 07:29:40 GMT</pubDate><dc:creator>the sqlist</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]DugyC (12/24/2012)[/b][hr]Merde! If only I'd waited a bit longer... however Steve might be on hols with the family already.....Having determined 1,4 &amp; 5 were correct in SQL2008, thought maybe this question was over all versions of SQL. So hooked up to a SQL2000 box and tried that, which only allowed 4 &amp; 5. Hence my answers.....Wishing both Hugo and Tom, and all other SQL bods out there, a very Merry Christmas and a memorable New Year... hic! :w00t: :hehe:[/quote]+1Doesn't help that we haven't got an install of 2012 here either so any questions on 2012 have to be educated guesses for me anyway</description><pubDate>Wed, 02 Jan 2013 01:56:22 GMT</pubDate><dc:creator>Stuart Davies</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I really like the question - you had to carefully go through each example to get the right answers - shame about the 'choose 2' cock-up but it did make me check my reasoning several times :-). Learnt something new about dividing by NULL, and NULLIF.</description><pubDate>Fri, 28 Dec 2012 04:52:38 GMT</pubDate><dc:creator>sipas</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]SanDroid (12/27/2012)[/b][hr]:ermm: You do not know the difference between creating a new TSQL batch and a control flow function? :-P[/quote]I don't even know what a [i]control flow function[/i] is. All control flow language elements I know are statements: BEGIN...END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, THROW, TRY...CATCH, WAITFOR, and WHILE. (See [url=http://msdn.microsoft.com/en-us/library/ms174290.aspx]http://msdn.microsoft.com/en-us/library/ms174290.aspx[/url]).If you meant to write controol flow statements, then the answer is yes, obviously I do know the difference between creating a new batch and a control flow statement. I just don't see how that relates to this question of the day.[quote]With all the ";" followed by a "go" in your example code  I guess that is no suprise.[/quote]I fail to see the relation between properly terminating statements and control-flow language. And you make it sound as if a semicolon before the batch seperator is a bad thing. It is not.For the record, the semicolon is the statement terminator in T-SQL. Using it has always been allowed, but used to be optional. As of SQL Server 2005 (if I recall correctly), not terminating statements has been put on the deprecated list, meanning it is still supported now, but will not be supported in a future release. It is already to properly terminate every statement that precedes a CTE or a Service Broker query (anything that starts with WITH), and it is also already mandatory to terminate MERGE statements.Since 2005, I have forced myself to write all new code in the advised form - with semicolons terminating all statements.Sources: [url=http://msdn.microsoft.com/en-us/library/ms177563.aspx]Transact-SQL Syntax Conventions[/url] and [url=http://msdn.microsoft.com/en-us/library/ms143729.aspx]Deprecated Database Engine Features in SQL Server 2012[/url].[quote]Here is some infomration online you can read... [url]http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/280/sql-server-%E2%80%93-transact-sql-batches[/url][/quote]Thanks for the link. It didn't learn me anything I didn't already know, but it might be useful for other readers of this discussion.For the record, I always prefer to post links to Books Online or other official Microsoft documentation. If I can't find any of that, my second choice is a blog from one of the people who work on the SQL Server team. The third choice is then a blog, where I still try to limit myself to the most authorative SQL Server authors - people like Kalen Delaney, Paul Randal, Kimberly Tripp, Paul White, and a few others.[quote]Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch.[/quote]You're right, I should have said: "a view definition has to be a single [i]query[/i]". ("query" instead of "select"). Using subqueries, that single query can contain multiple occurrences of the keyword select.[quote]Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch... :hehe:[/quote]No, I don't. I do still need either code examples or a much better explanation to understand why you think that part of the explanation of the question "seems incomplete or missleading". So far, you only managed to confuse me more.</description><pubDate>Thu, 27 Dec 2012 08:22:26 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Hugo Kornelis (12/26/2012)[/b][hr][quote][b]SanDroid (12/26/2012)[/b][hr]Hugo, Great question that realy made me think.However part of your explination seems incomplete or missleading.  If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.[/quote]Hi SanDroid,Thank you for your kind words! (and thank you to other people who wrote kind words as well).I don't really understand the point you raise about the incomplete explanation. What you say is right - you cannot use control flow in a view definition; [color=#FF0000]in fact, a view definition has to be a single SELECT.[/color] But how does that relate to the question and explanation? Can you clarify (maybe with some sample code to illustrate the issue)?[/quote]:ermm: You do not know the difference between creating a new TSQL batch and a control flow function? :-P   With all the ";" followed by a "go" in your example code  I guess that is no suprise. Here is some infomration online you can read... [url]http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/280/sql-server-%E2%80%93-transact-sql-batches[/url]Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch. Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch... :hehe:Don't bait me... I'm no fish... :</description><pubDate>Thu, 27 Dec 2012 07:03:20 GMT</pubDate><dc:creator>SanDroid</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Checked 1 and 4 and then stopped thinking because I only had to select two :-D</description><pubDate>Wed, 26 Dec 2012 23:40:00 GMT</pubDate><dc:creator>Koen Verbeeck</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]SanDroid (12/26/2012)[/b][hr]Hugo, Great question that realy made me think.However part of your explination seems incomplete or missleading.  If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.[/quote]Hi SanDroid,Thank you for your kind words! (and thank you to other people who wrote kind words as well).I don't really understand the point you raise about the incomplete explanation. What you say is right - you cannot use control flow in a view definition; in fact, a view definition has to be a single SELECT. But how does that relate to the question and explanation? Can you clarify (maybe with some sample code to illustrate the issue)?</description><pubDate>Wed, 26 Dec 2012 16:03:18 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Post was removed cause poster did not understand the difference of values returned by NULLIF and ISNULL.</description><pubDate>Wed, 26 Dec 2012 11:33:37 GMT</pubDate><dc:creator>SanDroid</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Hugo, Great question that realy made me think.However part of your explination seems incomplete or missleading.  If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.</description><pubDate>Wed, 26 Dec 2012 11:30:45 GMT</pubDate><dc:creator>SanDroid</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Ugh, spent way too long trying to figure out which one of 1, 4, 5 wasn't reliable before simply guessing to see the explaination.  Glad to know I wasn't missing something obvious.</description><pubDate>Wed, 26 Dec 2012 09:14:20 GMT</pubDate><dc:creator>sestell1</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Hugo Kornelis (12/23/2012)[/b][hr]I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.[/quote]Wheeeww... I was getting worried.</description><pubDate>Wed, 26 Dec 2012 06:41:27 GMT</pubDate><dc:creator>mbova407</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I answered 1,4 &amp;5. Please get my point back.:-)</description><pubDate>Wed, 26 Dec 2012 03:57:22 GMT</pubDate><dc:creator>Dineshbabu</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Nice question and a good discussion indeed!</description><pubDate>Tue, 25 Dec 2012 21:52:50 GMT</pubDate><dc:creator>Lokesh Vij</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]SQLRNNR (12/24/2012)[/b][hr]Thanks to Hugo and Tom for the discussion.[/quote]+1, really enjoyed it. And, couldn't resist posting this response to get my 1,000th point for Christmas! Just really excited about round numbers. :hehe:</description><pubDate>Tue, 25 Dec 2012 07:46:46 GMT</pubDate><dc:creator>Thomas Abraham</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I was about to argue that 1,4 &amp; 5 should be the correct answer, but read your comment and realized that you've already said I was right - even though I'm flagged as wrong!</description><pubDate>Mon, 24 Dec 2012 17:16:13 GMT</pubDate><dc:creator>Derek Dongray</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Thanks to Hugo and Tom for the discussion.</description><pubDate>Mon, 24 Dec 2012 09:15:26 GMT</pubDate><dc:creator>SQLRNNR</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Toreador (12/24/2012)[/b][hr]That required rather more brain cells than I was expecting to need on Christmas Eve :hehe:Glad to see that option 5 really should be correct, I'd started to think I might have completely misunderstood something.Happy etc![/quote]+1I answered 1,4 and 5 despite qotd said choose 2!Please, my point back!For Steve: is it possible to have a preview of qotd for the author, to check the correctness of the question? Sometime, the final version of the question is different from what the author first published!</description><pubDate>Mon, 24 Dec 2012 09:12:08 GMT</pubDate><dc:creator>Carlo Romagnano</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>That required rather more brain cells than I was expecting to need on Christmas Eve :hehe:Glad to see that option 5 really should be correct, I'd started to think I might have completely misunderstood something.Happy etc!</description><pubDate>Mon, 24 Dec 2012 06:19:39 GMT</pubDate><dc:creator>Toreador</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Merde! If only I'd waited a bit longer... however Steve might be on hols with the family already.Haven't read all posts, just Hugo's first repy to Tom, which confirms my findings.Having determined 1,4 &amp; 5 were correct in SQL2008, thought maybe this question was over all versions of SQL. So hooked up to a SQL2000 box and tried that, which only allowed 4 &amp; 5. Hence my answers.Ah well, no hard feelings, I've got my point from this post anyway LOL! :-)Wishing both Hugo and Tom, and all other SQL bods out there, a very Merry Christmas and a memorable New Year... hic! :w00t: :hehe:</description><pubDate>Mon, 24 Dec 2012 02:50:50 GMT</pubDate><dc:creator>DugyC</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Hi Tom,You are right that there are indeed some issues with CASE. And I may not have remembered them all correctly. First, let's skip all older versions of the documentation and move to [url=http://msdn.microsoft.com/en-us/library/ms181765.aspx]the SQL Server 2012 version of Books Online[/url]:[quote]The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.(... code fragment snipped ...)You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.[/quote]Badly written, definitely. And not only because the use of the term "statement" instead of "expression". However, I read this as follows:1. There is some exception to the "evaluation order" rule.2. To be precise, that exception is that aggregates may be evaluated sooner.3. Therefore, you can not depend on the order of evaluation when aggregates are involved.4. (Concluded by my from point 3) Apparently, when aggregates are NOT involved, you CAN still rely on evaluation order.While hunting for this information, I did find a few Connect items about errors with WHEN (something that's never true) THEN MIN(1/0) or WHEN (something that's always true) THEN 1 ELSE MIN(1/0) - they are the connect items that prompted this clarification to be added to Books Online.I am very sure that I *also* recall a discussion about evaluation of a WHEN clause that logically was not needed, and Microsoft replying that they do guarantee not computing a THEN or ELSE that's not required, but not maing a similar guarantee about computing the WHEN conditions. But I was unable to find any reference for this. Maybe someone else can?</description><pubDate>Sun, 23 Dec 2012 15:52:21 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]Hugo Kornelis (12/23/2012)[/b][hr]I think you are confusing two things about processing and evaluation order in a CASE expression. Let's look at an example:[code="sql"]CASE  WHEN x &amp;lt;&amp;gt; 0 THEN y / x  WHEN (very complicated subquery expression) = 1 THEN (yet another very complicated subquery)  ELSE 42END[/code]SQL Server [b]guarantees[/b] that "y/x" will only be actually computed when "x &amp;lt;&amp;gt; 0" evaluates to True. It also guarantees that "yet another very complicated subquery" will only be actually computed when "x &amp;lt;&amp;gt; 0" [i]does not[/i], and "(very complicated subquery expression) = 1" [i]does[/i] evaluate to True.Some people also expect that SQL Server will not even process (very complicated subquery expression) when "x &amp;lt;&amp;gt; 0" evaluates to true. And while that [i]could[/i] have been the case (after all, the result of that subquery evaluation will not be used), this, in fact, is [b]not guaranteed[/b]. My suspicion after reading your comment is that you either once read, or once were bitten by this latter behaviour, and then erroneoulsy expanded into no longer trusting SQL Server not to evaluate the THEN if the WHEN does not evaluate to True.[/quote]As to whether the possible results are evaluated, there appears to be no documentation.  My memory tells me that I was bitten way back when by a zero divide error in a result branch that should not have been evaluated, but it was a long time ago.  Perhaps my memory is playing me false, or perhaps it was conditioned by seeing that "2" in the question.  Maybe it was in some other dbms than sql-server - I can't even remember whether my scratch pad variables were decorated with "@" or not; but of course in a dbms with case statements (in addition to case expressions) the execution of code in unwanted branches would be an pretty disastrous but, so that wouldn't have happened in, for example,  sql-anywhere.  But regardless of whether an error can occur through evaluation of an unneccessary result expression (and I believe it can't, because I know you are a reliable source of information on that sort of thing) my comment about the implementation being broken stands: if T-SQL is going to try some speculative execution for some reason, it must catch errors in that speculative execution internally and only throw them on to the user-visible level if the speculative execution turns out to have been needed, and it doesn't matter a bit whether what is being speculatively executed is a when-expression, and boolean when-expression, or a result branch.  It isn't particularly surprising that some people expect the evaluation of when clauses or boolean clauses to stop at first true, because the text of BoL explicitly states that for a searched case expression (which is what we have here, since the comparison is &amp;lt;&amp;gt; not =) the boolean expressions are evaluated in order.  Before SQL 2008 R2 it didn't say that evaluation of these expressions stops when true is met, but that's what most people would expect when it's stated that evaluation is done in order. Up to and including SQL 2008 BoL made no reference to the possibility of a when-boolean-expression being evaluated after the first true.  So either the documentation was misleading and should have been changed to say that they may all be evaluated even if a true is met before the last boolean, or the implementation should be changed to stop at the first true. In 2008 R2 BoL a new remark was introduced into the case page.  This begins "The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied."  That is plainly false.  It then goes on to say "In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input".  This must have been intended to say that there are exceptions to the preceding sentence, but since it is essential for an expression to be evaluated before anything receives its result[s] (since until it is evaluated its result[s] is[are] unknown) it doesn't actually say that, it's just a pointless tautology.  Of course it seems very unlikely that it was intended to mean what it does say (which can be paraphrased as "in some situations we have failed to to build a temporal paradox into the implementation of case statements") - whoever wrote that addition to BoL apparently wasn't very good at writing clear English, and also seems to have been unaware that T-SQL has case expressions but (unlike several other SQL implementations) no case statements.  Anyway, the documentation is now (for 2008 R2 and 2012) clearly wrong, not just misleading (worse than before 2008 R2), and definitely needs fixing (although it would be better to fix the implementation - documenting that the code can throw unneeded errors becauses it indulges in unprotected speculative execution would be a step in the right direction but not a proper fix).When it comes to trusting SQL Server (and the things that I associate with it, like ADO) I don't - I've seen too many things broken by a new service pack or a new release, or even by a critical update (and sometimes the change in behaviour was documented, more often not). I've seen the argument that the optimizer, not the semantics, is supreme rather too often to trust it, particularly since in some cases where I felt it was unjustifiable.  I do think SQL Server is better than its competition, though - it's what I recommend people to use.  But I do tell them to test everything, and have a good try at breaking their code before they believe it will be anything like reliable enough for production.</description><pubDate>Sun, 23 Dec 2012 06:33:12 GMT</pubDate><dc:creator>L' Eomot Inversé</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>[quote][b]L' Eomot Inversé (12/22/2012)[/b][hr]Good question.But the explanation, as it refers to nullif and to view5, is a bit of a mess - it appears to say that view5 will always return null, instead of an error, when the divisor is zero, which is wrong (and conflicts with the answer given, which is right).   The thing about nullif (as used in view5) is that it is equivalent to a two-branch case statement,  and almost always both branches will be executed in currently supported systems, even if the divisor is zero.Edit: the current implementation of CASE can (and does) use eager rather than lazy evaluation, which means that it can't be used to eliminate errors by having separate branches for error-producing and safe states; and since nullif is just a shorthand for a case statement, it has the same issue.  I regard this as an example of an opportunity for optimisation being used as an excuse to destroy the apparent semantics of the language, which is of course inexcusable.[/quote]As you can see from my previous reply to this topic, I disagree with your comment.I think you are confusing two things about processing and evaluation order in a CASE expression. Let's look at an example:[code="sql"]CASE  WHEN x &amp;lt;&amp;gt; 0 THEN y / x  WHEN (very complicated subquery expression) = 1 THEN (yet another very complicated subquery)  ELSE 42END[/code]SQL Server [b]guarantees[/b] that "y/x" will only be actually computed when "x &amp;lt;&amp;gt; 0" evaluates to True. It also guarantees that "yet another very complicated subquery" will only be actually computed when "x &amp;lt;&amp;gt; 0" [i]does not[/i], and "(very complicated subquery expression) = 1" [i]does[/i] evaluate to True.Some people also expect that SQL Server will not even process (very complicated subquery expression) when "x &amp;lt;&amp;gt; 0" evaluates to true. And while that [i]could[/i] have been the case (after all, the result of that subquery evaluation will not be used), this, in fact, is [b]not guaranteed[/b]. My suspicion after reading your comment is that you either once read, or once were bitten by this latter behaviour, and then erroneoulsy expanded into no longer trusting SQL Server not to evaluate the THEN if the WHEN does not evaluate to True.</description><pubDate>Sun, 23 Dec 2012 03:56:11 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.</description><pubDate>Sun, 23 Dec 2012 03:29:30 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item><item><title>RE: Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Good question.But the explanation, as it refers to nullif and to view5, is a bit of a mess - it appears to say that view5 will always return null, instead of an error, when the divisor is zero, which is wrong (and conflicts with the answer given, which is right).   The thing about nullif (as used in view5) is that it is equivalent to a two-branch case statement,  and almost always both branches will be executed in currently supported systems, even if the divisor is zero.Edit: the current implementation of CASE can (and does) use eager rather than lazy evaluation, which means that it can't be used to eliminate errors by having separate branches for error-producing and safe states; and since nullif is just a shorthand for a case statement, it has the same issue.  I regard this as an example of an opportunity for optimisation being used as an excuse to destroy the apparent semantics of the language, which is of course inexcusable.</description><pubDate>Sat, 22 Dec 2012 20:29:25 GMT</pubDate><dc:creator>L' Eomot Inversé</dc:creator></item><item><title>Divide by zero</title><link>http://www.sqlservercentral.com/Forums/Topic1399722-1328-1.aspx</link><description>Comments posted to this topic are about the item [B]&lt;A HREF="/questions/Views/95411/"&gt;Divide by zero&lt;/A&gt;[/B]</description><pubDate>Sat, 22 Dec 2012 20:18:58 GMT</pubDate><dc:creator>Hugo Kornelis</dc:creator></item></channel></rss>