Order In WHERE Clause Matter For SARGability?
In theory, and particularly in modern versions of SQL Server, the order of the WHERE clauses makes absolutely no difference. SQL Server will generally process the filters in the most efficient order, and will pick the index that it determines will allow for the most efficient query. A difference that may occur is when the WHERE clause includes JOIN criteria or applies filters to outer joined columns; other differences can come into play when options like FORCE ORDER or specific trace flags are in use.

Re-writing as a CTE to "force" SQL Server to process a filter inside the CTE first doesn't work - SQL Server is going to re-write it anyway, and process it in the order that it deems best. This is why, for example, you can't escape invalid conversion errors by using a CTE or a subquery to first filter out the bad data - SQL Server might still present the bad data to the conversion before any filter does anything.

(Erland complained about this in Connect #537419.)

So, again generally, don't re-write your SQL code to try to out-smart the optimizer or prevent it from doing something stupid. There are occasions where it can pick the wrong index that might be resolved by changing the query itself, but deal with those in situations where you already believe that the optimizer has picked the wrong index (and before solving those, eliminate more likely causes like parameter sniffing, bad statistics, etc.).