SQL Server 2008 Query Editor changes the query logic

Question

I hand coded a simple SQL in SQL Server 2008 as below;

SELECT * FROM Tab1 WHERE A='1' AND (B='1' OR C='1');

Being lazy I opened this query in the Query Editor to validate the syntax and pressed OK on the dialog without making any changes.

I noticed that the Query Editor had changed my query to:

SELECT * FROM Tab1 WHERE A='1' AND (B='1') OR (C='1');

clearly this changes the logic of the SQL and returns different results depending on which one you execute.

I routinely use the Query Editor to validate my syntax on complex queries. So a little worried that the a subtle change like this would go unotice, but would change the outcome.

Is this a feature of the designer? Is there something I can do to change this behavior?

EDIT: Thanks for pointing out that the changes made by the editor is not quite the same as above, but still the query is modified although the results are the same.

Thanks

Answer 1

I tried to replicate this in the Query Designer and had a slightly different result. I typed the same as you:

SELECT * FROM Tab1 WHERE A='1' AND (B='1' OR C='1');

And got this:

SELECT     *
FROM         Tab1
WHERE     (A = '1') AND (B = '1') OR
                      (A = '1') AND (C = '1')

I have to say that the result is the same, but we can all see a dangerous road here. Also, I did not like the (A = '1') replication. Heck, I want the code how I coded it!

A word to the wise: I never format my queries in SQL Server Management Studio. Have you seen what it does to your view's code? I hate it. I just code somewhere else and paste in SMS when done.

Answer 2

The statement

SELECT * FROM Tab1 WHERE A='1' AND (B='1' OR C='1')

resolves for me to:

SELECT * FROM Tab1 WHERE (A='1') AND (B='1') OR (A='1') AND (C='1')

This is surprisingly correct, as in SQL Server TSQL the AND operator has precedence over OR. That means the above is the same like the following, because the AND-operator gets evaluated before the OR-operator:

SELECT * FROM Tab1 WHERE ((A='1') AND (B='1')) OR ((A='1') AND (C='1'))

And this is the same like the initial statement being used in the question.

See Operator Precedence (Transact-SQL) for details.