InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:06:05 GMT20http://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxNice article and nice work on the floating-point exception bug.Now, to give this dead horse the final whiplash, I was intrigued by the IN handling and modified the set (30,20,10,1) into (30,20,30,10,1). Sure enough, the IN expression was optimized down to 4 ordered OR subexpressions in the query plan like before. Distinct sort seems to be the easiest way to expand only distinct values in the set into OR expressions. It just happens to also sort the values as a benign side-effect.Sat, 22 Oct 2011 16:59:40 GMTArto Ahlstedthttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxThanks for the feedback, Bart.It's nice to see a problem fixed in such a short time.Tue, 22 Mar 2011 01:30:20 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxUpdate to anyone following this: this compile-time [url=http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/]exception to normal T-SQL CASE short circuiting[/url] is now scheduled to be fixed in an upcoming SQL release, thanks to Gianluca for filing the Connect bug. For now the problem behavior still exists in SQL2005 through SQL2008R2 -- and maybe SQL2000 -- so keep an eye out for it in existing releases as you use CASE for short-circuiting.Mon, 21 Mar 2011 19:44:04 GMTBart Duncanhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxThanks for clarifying, Bart.I filed a Connect item here: [u][url]https://connect.microsoft.com/SQLServer/feedback/details/649957/case-expression-evaluates-else-branch-at-compile-time[/url][/u].Let's see what happens.Mon, 07 Mar 2011 10:30:02 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxFWIW I think CASE's implementation actually does guarantee both order of execution and predictable short circuiting at execution time. That doesn't prevent this error, though, because in this case the error is occurring at compile time, not execution time. As you mentioned, Paul, this is the result of compile-time constant folding: the error occurs when the optimizer tries to evaluate "LOG10(@input)" at compile time to replace it with a constant. It’s not entirely clear to me whether compile-time simplifications like this one are expected to be blocked so that they don't effectively circumvent CASE's short circuiting behavior. But FWIW to me it does seem like a bug, and I already filed an internal workitem requesting that someone on the QO team investigate it. (But don't let that dissuade you from filing a Connect bug if you feel strongly that this should be fixed -- most of the time community-submitted bugs get more weight than an equivalent request filed by someone internal at MS...) Mon, 07 Mar 2011 09:08:49 GMTBart Duncanhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxAgree it's an edge case & not representative of typical CASE behavior. I just updated my post to clarify that.Fri, 04 Mar 2011 13:51:06 GMTBart Duncanhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]SQLkiwi (3/3/2011)[/b][hr]Just to be clear, Bart's example is compiled as:[code="sql"]SELECT CASE WHEN 0 <= 0 THEN 0 ELSE LOG10(0) END;[/code]..which also gives the error (when it should not). Reproduces on 2005 (build 0.05254) and 2008 (build 10.0.4272) for me.[/quote]And reproduced on 2005 SP3 (9.00.4035.00 X64) and 2008 R2 RTM (10.50.1734.0 Intel X86) for me.Fri, 04 Mar 2011 01:49:10 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxJust to be clear, Bart's example is compiled as:[code="sql"]SELECT CASE WHEN 0 <= 0 THEN 0 ELSE LOG10(0) END;[/code]..which also gives the error (when it should not). Reproduces on 2005 (build 0.05254) and 2008 (build 10.0.4272) for me.Thu, 03 Mar 2011 22:16:40 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Gianluca Sartori (3/3/2011)[/b][hr]It looks like that behaviour is determined by the fact that the code executes inside a TVF.[/quote]It is constant-folding at work. If you replace the literal constant zero with a variable, the problem no longer occurs. SQL Server expands the in-line TVF at optimization time and fully evaluates the CASE with the constant values available.[quote]I think it should be reported on connect as a bug. BOL is quite clear on that point.[/quote]I agree. Constant-folding should never cause an error condition (such as an overflow) at compilation time - there have been other bugs in this area fixed for the same reason. Bart, if you put this on Connect, please leave a link here so I can vote for it.Thu, 03 Mar 2011 22:12:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Bart Duncan (3/3/2011)[/b][hr]This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See [url=http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/]http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url].[/quote]Nice catch, Bart.It looks like that behaviour is determined by the fact that the code executes inside a TVF.Try this:[code]-- Autonomous T-SQL batch: everything runs just fine DECLARE @input intSELECT @input = 0SELECT calculated_value = CASE WHEN @input <= 0 THEN 0 ELSE LOG10 (@input) END -- Scalar function: runs fineCREATE FUNCTION dbo.test_case_short_circuit2 (@input INT)RETURNS intAS BEGINRETURN ( SELECT calculated_value = CASE WHEN @input <= 0 THEN 0 ELSE LOG10 (@input) END)ENDGOSELECT dbo.test_case_short_circuit2 (-1);GO[/code]I think it should be reported on connect as a bug. BOL is quite clear on that point.Thanks for sharingThu, 03 Mar 2011 14:32:00 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxThis post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See [url=http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/]http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url].Thu, 03 Mar 2011 12:56:09 GMTBart Duncanhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxTo make matters worse:[quote]Sometimes an expression in a select clause will be [b]evaluated before[/b] filtering expressions in the where clause.[/quote]This means that if we have an expression in the select clause that performs a division, where the right side value is queried from a table and a where clause exists to filter any zero's out, it can still go wrong due to a divide by 0!In such a case you need to harden your select expression by using a case construct to filter out the 0 values before doing the division. It does not matter what the result of the expression is in such a case, as the where clause will filter out the result [b]afterwards[/b] anyway.I doubt many SQL statements in existence that involve such sensitive expressions (and there are quite a few) are in fact hardened. I say this foremost as nearly all of the time code works just fine without, but then it can suddenly break after years of fine operation as some unseen threshold is reached. The second reason is that it generates complicated hard to maintain code, not to mention that it also works slower.I also think few wil be aware of how far the freedom of execution order in SQL stretches. Personally I think it goes too far as it results in unreliable code or complicated code and thus is counter productive to the things we all want. At a minimum where clause filtering should always happen before select expressions to provide a simple model that is just as good 99.9% of the time anyway.Thu, 06 Jan 2011 06:28:57 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxExcellent, really interesting article!Wed, 05 Jan 2011 06:57:33 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxGreat article. It should be read by anyone who codes T-SQL.Tue, 04 Jan 2011 10:25:53 GMTBill Kline-270970http://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]gabriele.acconcia (1/4/2011)[/b][hr]I would only sign out that I got an error when I tried to execute the following sample from the article:DECLARE @a int = 1DECLARE @b int = 0[/quote]Hello. That syntax actually started in SQL Server 2008. So it makes sense that it would error in SQL Server 2005.Tue, 04 Jan 2011 10:19:24 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxInteresting article.What I'll put in my bag is "never rely on short-circuiting in t-sql"!I would only sign out that I got an error when I tried to execute the following sample from the article:DECLARE @a int = 1DECLARE @b int = 0IF 1/0 = 1 AND @a = @b SELECT 'True'AS resultELSE SELECT 'False'AS resultThe error was "Cannot assign a default value to a local variable".I used sqlexpress 2005, and I changed the code this way:DECLARE @a intDECLARE @b intSET @a = 1SET @b = 0IF 1/0 = 1 AND @a = @b SELECT 'True'AS resultELSE SELECT 'False'AS resultBest regardsTue, 04 Jan 2011 10:00:08 GMTgabriele.acconciahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]ColdCoffee (1/1/2011)[/b][hr]Excellent article Gian.. very precise and elegant...[/quote]Thank you, Mr. Coffee.Sat, 01 Jan 2011 13:52:09 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Solomon Rutzky (12/31/2010)[/b][hr]Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.[/quote]Excellent! Thanks.Sat, 01 Jan 2011 13:49:24 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxExcellent article Gian.. very precise and elegant...Sat, 01 Jan 2011 04:06:19 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Gianluca Sartori (12/31/2010)[/b][hr]I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.I executed the following queries to discover the behaviour of the DB engine:[code]-- Does the engine short-circuit?SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;-- Does the engine detect contraddictions?SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;[/code][/quote]Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.Fri, 31 Dec 2010 08:57:00 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxThanks for that - I liked the way you cohesively demonstrated the point at hand. It requires engaging one's brain, but that is why I subscribed to the group!Thanks againTAPFri, 31 Dec 2010 07:15:53 GMTtobypankhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]magarity kerns (12/30/2010)[/b][hr]Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?[/quote]This is the output of my tests on other RDBMSs. I tested Oracle 11gR2, DB2/400 V5R4M0, Firebird 1.5 and PostgreSQL 8.3. I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.I executed the following queries to discover the behaviour of the DB engine:[code]-- Does the engine short-circuit?SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;-- Does the engine detect contraddictions?SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;[/code]I didn't have the time to test other particular things, but this is a good starting point.Here's the results:[code] DB2 Oracle Firebird PostgreSQL------------------------ ---- ------ -------- ----------Short-circuit no yes yes yesContraddiction detection no yes yes no[/code]Just a few words on Oracle's contraddiction detection: under some circumstances, the contraddiction is not detected at all. This doesn't surprise me, as Oracle's optimizer is full loaded of bugs (more features, more things that could go wrong). In particular, when the statement text is VERY big and contains a whole lot of literals, the optimizer goes quirks and tries to produce a plan (tries for several minutes...) even if the statement contains a contraddiction. This could mean that the contraddiction detection is applied as one of the last rules, that could make sense since it also checks for constraints contraddiction.Fri, 31 Dec 2010 01:31:39 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]CirquedeSQLeil (12/31/2010)[/b][hr]Well done Gianluca[/quote]Thank you, Jason.Fri, 31 Dec 2010 00:49:11 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]sknox (12/30/2010)[/b][hr][quote][b]Gianluca Sartori (12/30/2010)[/b][hr][quote][b]sknox (12/30/2010)[/b][hr]So you have to evaluate both sides of the XOR.[/quote][b]When first name is null and middle name is not null[/b], the second part of the or expression does not get evaluated.[/quote]So you short-circuited the OR, but at the expense of not short-circuiting the AND, and more to the point, in the original XOR (first_name is null XOR middle_name is null) both sides still had to be evaulated.In the end, you evaluated two boolean expressions, and you cannot properly perform an XOR without evaluating at least two boolean expressions. You can expand it to more expressions for languages that don't have a built-in XOR, but at least two will have to be evaluated, and they will correlate either directly or inversely to the two conditions in the original XOR.[/quote]Well, I must be wrong then. Have you seen my avatar picture? It should remind people (and myself) that I'm just throwing in my two cents. :-)However, the main point I'm trying to make in the article is that short-circuiting should not be considered part of a database developer's toolbag. Whether XOR shortcircuits entirely or partially should not be a concern, as for any other logical expression. This is the concept I wanted you to get away with. I hope I didn't fail at that.Thanks for your valuable feedback.Fri, 31 Dec 2010 00:48:01 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxWell done GianlucaFri, 31 Dec 2010 00:03:55 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Gianluca Sartori (12/30/2010)[/b][hr][quote][b]sknox (12/30/2010)[/b][hr]So you have to evaluate both sides of the XOR.[/quote][b]When first name is null and middle name is not null[/b], the second part of the or expression does not get evaluated.[/quote]So you short-circuited the OR, but at the expense of not short-circuiting the AND, and more to the point, in the original XOR (first_name is null XOR middle_name is null) both sides still had to be evaulated.In the end, you evaluated two boolean expressions, and you cannot properly perform an XOR without evaluating at least two boolean expressions. You can expand it to more expressions for languages that don't have a built-in XOR, but at least two will have to be evaluated, and they will correlate either directly or inversely to the two conditions in the original XOR.Thu, 30 Dec 2010 18:26:11 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]SQLkiwi (12/30/2010)[/b][hr][quote][b]sknox (12/30/2010)[/b][hr]I know how to write an XOR using AND/OR/NOT.[/quote]Yes, but Gianluca wasn't to know that. You might have been asking a sensible question, rather than just making the point that in the sentence "Any boolean expression is capable of being short-circuited, in the right circumstances." it would have been more accurate to say 'many' rather than 'any'...[/quote]Yes but my question was on short-circuiting an XOR, not on writing one in SQL.[quote][quote]But while you can write it, you can't short-circuit it:[code="sql"](first_name IS NULL AND middle_name IS NOT NULL) OR (first_name IS NOT NULL AND middle_name IS NULL)[/code]In that code, both first_name and middle_name have to be evaluated. First we must evaluate first_name. If it's not NULL, then we can, yes, ignore middle_name here and short-circuit the first AND. But then we return false to the first part of the OR so we must evaluate the second part. Since first_name is not NULL, we know we must evaluate the second part of second AND, which evaluates middle_name. So you have to evaluate both sides of the XOR.You can reorder the AND and OR operators, but since the two sides are mutually exclusive, you will always have to evaluate both of the original expressions. So not all boolean expressions can be short-circuited.[/quote]So, you're saying that an XOR written in T-SQL can be short-circuited? Or just a bit? ;-)[/quote]Absolutely NOT. The two sides of the XOR in this case are first_name and middle_name. Both of those must be evaluated in all cases. While some subsets of the expanded boolean expression can be short-circuited, you have to first expand the XOR to ( A AND NOT B) OR (B AND NOT A), and then evaluate both A and B, and then you can short-circuit some of the expanded logic, but you're still evaluating both sides of the original XOR.Thu, 30 Dec 2010 18:20:10 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]magarity kerns (12/30/2010)[/b][hr]Check this out: My friendly local Oracle DBA told me Oracle is backwards!select 'A' from dual where 1/0 = 1 AND 1=0;(at least on Oracle 10g) will give an empty result instead of an error. I tested this and it works in this order.[/quote]Also Oracle has a contraddiction detection feature in the optimizer. Your 1 = 0 gets intercepted by that feature and èrevents the statement from failing.Thu, 30 Dec 2010 15:01:07 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Solomon Rutzky (12/30/2010)[/b][hr][quote][b]Gianluca Sartori (12/30/2010)[/b][hr]As a side note, a CLR function can update data. ;-)[/quote]Hello Gianluca. Thanks for a great and thorough article :).This is slightly off topic, but how do you get a CLR Function to be able to alter the state of the DB? I have always seen this error:System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'INSERT' within a function.I certainly don't think this is a good idea (to alter the state of the DB in a function), but you mention it can be done so I was curious.Take care,Solomon...[/quote]Thank you, Solomon. To circumvent that limitation, you can update the database using a connection different from the context one. To do so, you have to give external access rights to the assembly.Thu, 30 Dec 2010 14:52:43 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Daniel Ruehle (12/30/2010)[/b][hr]Agree, but if the query plan says its going to scan the table, then it does come down to how long does it take to process each row. Since you aren't guaranteed the order that SQL Server will evaluate the conditions when just using boolean logic, it can choose to do then in an inefficient manner, which I believe was the jist of the article. In scenarios where it might matter, this gives you absolute control the order.[/quote]Agreed.With lots of rows and lots of WHEN branches it does indeed make a difference. Generally speaking (some million rows and at most 10 WHEN bracnhes) it isn't even noticeable.Thu, 30 Dec 2010 14:50:10 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspxCheck this out: My friendly local Oracle DBA told me Oracle is backwards!select 'A' from dual where 1/0 = 1 AND 1=0;(at least on Oracle 10g) will give an empty result instead of an error. I tested this and it works in this order.Thu, 30 Dec 2010 14:48:40 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]magarity kerns (12/30/2010)[/b][hr]Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?[/quote]Nice idea. Tomorrow I can try on Oracle, DB2, Firebird, MySql and Postgres.Thu, 30 Dec 2010 14:41:51 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote]You did the wrong test. You should have done "where 1=0 AND 1/0 = 1" or "where 1=1 or 1/0 = 1". Remember: AND short-circuits on false, OR short-circuits on true.[/quote]Oops, you're right... In fixing it I discovered that Oracle still gives a divide by zero error for "select 'A' from dual where 1=0 and 1/0 = 1;" but SQL Server behaves as described in the article. Perhaps Oracle can handle this in an IF statement when using a stored procedure?Thu, 30 Dec 2010 14:39:17 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]sknox (12/30/2010)[/b][hr][quote][b]Daniel Ruehle (12/30/2010)[/b][hr][code] ... case when Age > 90 then 1 when Age < 5 then 0 when Gender = 'Male' then 1 when LastName like 'SAM%' then 1 else 0 end = 1[/code]This gets records for all people over the age of 90, males of age 5 or more and anyone with a last name that starts with the letters SAM.[/quote]Not quite. This gets records for all people over the age of 90, males of age 5 or more, and anyone [b]age 5 or more[/b] with a last name that starts with the letters SAM. This will not retrieve a record for someone under age 5 with a last name starting with SAM.Your point about using CASE for explicit short-circuiting is good, but your explanation is a perfect example of how careful you have to be when using CASE, for the same reason.[/quote]Yes indeed, you need to be VERY careful when using this, especially when using an exclusionary case. With great power comes great responsibility!Thu, 30 Dec 2010 14:34:51 GMTDaniel Ruehlehttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]LutzM (12/30/2010)[/b][hr]Gianluca, thanx for sharing an EXCELLENT article!!![/quote]Thanks, Lutz.Thu, 30 Dec 2010 14:34:50 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]sknox (12/30/2010)[/b][hr]So you have to evaluate both sides of the XOR.[/quote]When first name is null and middle name is not null, the second part of the or expression does not get evaluated.Thu, 30 Dec 2010 14:30:56 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]sknox (12/30/2010)[/b][hr]I know how to write an XOR using AND/OR/NOT.[/quote]Yes, but Gianluca wasn't to know that. You might have been asking a sensible question, rather than just making the point that in the sentence "Any boolean expression is capable of being short-circuited, in the right circumstances." it would have been more accurate to say 'many' rather than 'any'...[quote]But while you can write it, you can't short-circuit it:[code="sql"](first_name IS NULL AND middle_name IS NOT NULL) OR (first_name IS NOT NULL AND middle_name IS NULL)[/code]In that code, both first_name and middle_name have to be evaluated. First we must evaluate first_name. If it's not NULL, then we can, yes, ignore middle_name here and short-circuit the first AND. But then we return false to the first part of the OR so we must evaluate the second part. Since first_name is not NULL, we know we must evaluate the second part of second AND, which evaluates middle_name. So you have to evaluate both sides of the XOR.You can reorder the AND and OR operators, but since the two sides are mutually exclusive, you will always have to evaluate both of the original expressions. So not all boolean expressions can be short-circuited.[/quote]So, you're saying that an XOR written in T-SQL can be short-circuited? Or just a bit? ;-)Thu, 30 Dec 2010 13:16:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]magarity kerns (12/30/2010)[/b][hr]Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?[/quote]You did the wrong test. You should have done "where 1=0 AND 1/0 = 1" or "where 1=1 or 1/0 = 1". Remember: AND short-circuits on false, OR short-circuits on true. I am, however, interested in what you find out -- and we shouldn't necessarily limit the testing to DBMSes -- any system or language that evaluates boolean expressions can be tested for this behavior.Thu, 30 Dec 2010 12:48:46 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Daniel Ruehle (12/30/2010)[/b][hr][code] ... case when Age > 90 then 1 when Age < 5 then 0 when Gender = 'Male' then 1 when LastName like 'SAM%' then 1 else 0 end = 1[/code]This gets records for all people over the age of 90, males of age 5 or more and anyone with a last name that starts with the letters SAM.[/quote]Not quite. This gets records for all people over the age of 90, males of age 5 or more, and anyone [b]age 5 or more[/b] with a last name that starts with the letters SAM. This will not retrieve a record for someone under age 5 with a last name starting with SAM.Your point about using CASE for explicit short-circuiting is good, but your explanation is a perfect example of how careful you have to be when using CASE, for the same reason.Thu, 30 Dec 2010 12:39:22 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1040686-2878-1.aspx[quote][b]Gianluca Sartori (12/30/2010)[/b][hr]As a side note, a CLR function can update data. ;-)[/quote]Hello Gianluca. Thanks for a great and thorough article :).This is slightly off topic, but how do you get a CLR Function to be able to alter the state of the DB? I have always seen this error:System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'INSERT' within a function.I certainly don't think this is a good idea (to alter the state of the DB in a function), but you mention it can be done so I was curious.Take care,Solomon...Thu, 30 Dec 2010 12:07:47 GMTSolomon Rutzky