Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

WHERE to begin

Many people, once they start getting comfortable writing SQL, begin asking the same questions. One such common question is “Does the order I put stuff in the WHERE clause make a difference?” This question usually means different things to different people, so let’s dig in a bit.

There are often odd exceptions to almost any generic rule in SQL Server. However, the short answer, with Microsoft SQL Server, is “No,” or at least “Not usually.” SQL Server will internally re-order where clause items to properly match indexes or other time saving devices. The most notable exception to this is if items are grouped with parentheses.

That being said, other Database Management Systems (DBMS) may be impacted by where clause order. I also work with Oracle, Sybase SQL Anywhere and Pervasive DBMs and I note that from one system to another WHERE clause order can make a difference. If you’re not working with SQL Server, take all this with a grain of salt and do your own testing.

Earlier I mentioned that this question means different things to different people. The following examples will highlight what I mean:

Using tools to evaluate how SQL Server works the query (which I’ll get into in future articles), I confirmed that these two statements are equivalent:

SELECT CUSTOMER_ANNOYACE_FACTOR
FROM CUSTOMERS
WHERE HAVE_CUSSED_ME_BEFORE = 1
AND STOLE_MY_GIRLFRIEND = 0

And

SELECT CUSTOMER_ANNOYACE_FACTOR
FROM CUSTOMERS
WHERE STOLE_MY_GIRLFRIEND = 0
AND HAVE_CUSSED_ME_BEFORE = 1

Notice that the first statement begins by filtering on “Have Cussed Me Before” and in the second statement, these are reversed.

Others may actually be asking about the following example. Again, these two are equivalent:

SELECT GEEK_LEVEL
FROM GEEK_RATINGS
WHERE GEEK_LEVEL = 25

And

SELECT GEEK_LEVEL
FROM GEEK_RATINGS
WHERE 25 = GEEK_LEVEL

For additional performance related-topics, feel free to peruse my other posting in the Performance Tuning Series.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.