The LookupCTE trick described here is pretty slick. These days I find myself working with large databases where these things really matter and I found this post quite interesting.

Improving Query Performance by using correct Search Arguments – SQLServerCentral.