Does SQL check each And condition if first condition fails?

Question

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

Mark Sinkinson · Answer 1 · 2014-05-14T14:39:29.007

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.

Does SQL check each And condition if first condition fails?

1 Answers1