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.

This question already has an answer here:

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this kind of queries?

SELECT [Emp_ID],[Emp_Name],[Emp_Sal] FROM [gyh].[dbo].[Employee_Demo] where Emp_ID='12' And Emp_Name like '%ab%'
share|improve this question

marked as duplicate by Paul White, Mark Storey-Smith May 15 at 14:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

1 Answer 1

What you're talking about is Short Circuit evaluation.

Unlike in CASE statements (except certain scenarios using variables and aggregate functions), where the order is important, you have little control over the order in which the WHERE clause in SQL Server is evaluated (except with some grouping paratheses). So, even if it does short-circuit, SQL Server may choose to evaluate the second clause first, especially if that is referenced as the first column in the index it chooses to use for the query.

SQL Server will still attempt to parse the entire query to make sure it is logically correct, regardless of whether the first AND condition fails.

For example, if I use the AdventureWorks database and look for a City that I know doesn't exist:

USE AdventureWorks;
GO

-- obviously don't use SELECT * in production
SELECT 
   * 
FROM Person.Address AS a
WHERE City = 'Bournemouth' AND 1/0 = 1

Even though City = 'Bournemouth' will always be FALSE, I still get the error:

Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.
share|improve this answer
1  
FYI, CASE will sometimes not short circuit as well, with edge-case scenarios involving local variables and aggregate functions. –  JNK May 14 at 14:37
    
@JNK did not know that! Edited answer. Thanks –  Mark Sinkinson May 14 at 14:39
add comment

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