Does the order of criteria in the WHERE statement matter at all?

Question

I have the following 2 statements:

declare @ID_Admin INT = 0, ID_Manager INT = 0
select *
from cases c
where (c.ID_Admin = @ID_Admin OR @ID_Admin = 0)
   and (c.ID_Manager = @ID_Manager OR @ID_Manager = 0)

AND

declare @ID_Admin INT = 0, ID_Manager INT = 0
select *
from cases c
where (@ID_Admin = 0 OR c.ID_Admin = @ID_Admin)
   and (@ID_Manager = 0 OR c.ID_Manager = @ID_Manager)

The intent is to tell the database that it doesn't need to filter on ID_Admin if the passed in parameter is 0 (same with ID_Manager). In one case, the first comparison is against the table (c.ID_Admin = @ID_Admin), following by comparison to zero (@ID_Admin = 0). In the second case, the criteria are reversed.

Does the order of criteria in the WHERE statement matter at all here? In other words, is SQL Server smart enough to say that it should do @ID_CaseAdmin = 0 comparison before trying to filter rows.

I ran both queries and it produced identical execution plans. However, this is a simple contrived example. So can I make a blanket statement that SQL Server will be smart enough in this type of situation not to start filtering actual rows.

Answer 1

Does the order of criteria in the WHERE statement matter at all here?

No. But SQL Server will evaluate the predicate for every row even when @ID_Manager is null unless you include OPTION RECOMPILE.