I would like to inject here that people need to be very careful about the "test it yourself" mantra. What is optimal for one set of circumstances could be awful for another. Never forget that some constructs can be tested until the cows come home and there could still be situations/configurations that were untested and which perform horribly.
Note that I am not speaking here of the constant vs * scenarios we are discussing in this thread. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service