Design better indexes rather than re-write your queries
I seem to be having the same conversation over & over again recently with developers who believe that re-writing queries is the best way to improve performance. Maybe I'm just obsessing about this topic a little at the moment, but I think the problem really does run much deeper.
Whilst it's true that re-writing some queries can help performance, it's also true to say that it shouldn't. Any two properly expressed & equivalent queries should theoretically be "optimised" by the query optimiser to the same execution plan. Changing the way a query is expressed really shouldn't result in any change of execution plan (all other factors remaining equal).
The reason why re-writing some queries does actually improve performance is that the query optimiser isn't perfect & you can take advantage of knowledge of optimiser limitations when re-writing a query. For example, take the case of moving logic from a User Defined Function (UDF) into a set based expression - a very common performance tuning query re-write technique. This improves performance because the query optimiser lacks the smarts to do this automatically for itself (it runs each invocation of a UDF - for every row - the same way it runs a stored procedure). It's certainly possible for modularised procedure logic to be folded into the calling set based statement (as evidenced by the fact that you can do this yourself) - the TSQL optimiser's just not quite that smart.
Whilst it might seem that I'm contradicting my argument at this point (I'm showing that re-writing queries can help even though the topic of this post is that this isn't the best approach) the main point I'd like to make is that there are limited cases where re-writing queries can help performance & ultimately you need to ensure that you have the right indexes in place before spending any time re-writing queries. You'll generally get far better results by focussing on designing the best indexes possible before looking at re-writing queries, although ultimately you do need to turn to re-writing queries at times to deal with optimiser limitations..
Consider the case where you might have a large table with no indexes. No matter how you express a SQL SELECT statement, the only option available to the query optimiser is to scan that table (as there are no indexes available for more efficient access paths). Sure, this might seem obvious to some, but given the number of times I've had this discussion lately, I suspect it's not so obvious to everyone. Somehow, some developers seem to focus immediately on re-writing the query before checking the best indexes are available first.
SQL Server can only optimise access paths to data stored in tables using indexes which already exist. Your first concern with tuning queries should always be to ensure that your tables have indexes which at least support the filter criteria (WHERE clause, INNER joins) of your queries & then possibly also covering selected if many rows are involved. Without good indexes, it doesn't matter how you express your query, SQL Server will have no choice but to fully scan your underlying tables.
I believe there are reasons why developers tend to think about re-writing queries before thinking about indexes & that this lies somewhere in the fact that SQL contains no processing rules. Develoeprs tend to have a hard enough time getting their queries right correct let alone thinking about indexes, but this is a topic for another day.