T/SQL Efficiency and Order of Execution

Question

In regards to the order of execution of statements in SQL, is there any difference between the following performance wise?

SELECT * FROM Persons
WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen')

And:

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen') AND UserType = 'Manager'

If there is any difference, is there perhaps a link etc. that you may have where one can learn more about this?

Thanks a ton,

Kyle

Answer 1

The optimizer, since it uses a cost based optimizer, will decide which "route" is best: it will cost the options based on statistics and then go from there. The order of terms should make no difference (although how you nest things, might make a difference).

EDIT: Oracle used to have - until fairly recently - a rule-based optimizer (RBO), but that has now been phased out. As the RBO didn't work off statistics, it was possible to see differences in query plans depending on the order of predicates.

Answer 2

There will be no difference. You can pull the query up in SQL Server and click on the Display Estimated Execution Plan icon and check the execution plan for the two queries. They should be identical.

Answer 3

The optimizer is free to rearrange and execute predicates as it finds most efficient/cost effective to retrieve the data.

Does the order of criteria the WHERE clause matter?