Order of interpretation in WHERE-clause

Question

Order of interpretation in WHERE-clause

julian-737414

SSC Enthusiast

Points: 140
More actions
May 7, 2008 at 5:58 am

#190297

Hello,
first, I'm new here, so hello to everybody 🙂
I've looked around in SQL Server 2005 documentation and in this forum but could not get a precise answer yet.
Is there a certain order the expressions in the WHERE-clause of a statement are processed?
For example:
1. In most programming languages a statement with two conditions and concatenated with AND is not fully interpreted if the first condition is FALSE.
2. Second case: [1 true] OR [2 true], condition 2 does not need to be interpreted because condition 1 is TRUE.
But this seems to be not the case in SQL Server. Is there a way to define the order? The official documentation only says that the order of interpretation can vary.
Or am I missing something?
Thank you in advance!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

Queries submitted to SQL Server go through a process called the optimizer. It makes decisions based on the data in the database as to what order to do certain processing and creates what's known as a query plan. This gets passed to the data engine which can use that plan or create one of its own. Either way, just becuse you wrote a query that says:

...WHERE x=1 AND y=2

There's no guarantee that it will evaluate 'x=1' first. The optimizer might recognize that the field represented by 'y' is more selective and will elminate records faster so it will choose to evaluate that value first.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning