As most of you know the general rule in performance tuning is .....
"measure, measure, measure" and "measure again"
But it doesn't hurt to give out some warning signs from time to time. And here goes two:
1) be careful in doing anything fancy on JOIN predicates (that is the condition in the "ON" section)
most of the time anything more complex than comparing one columns from each of the tables being joined will have some performance impact.
again measure, but in experience I find that even creating a common table expression, or a subquery to come up with the derived column and use it to join is faster than doing it in the JOIN predicate.
also, the less columns involved in a JOIN the better.
2) always prefer set operations than row-by-row operations
for the same reason that cursors should be avoided
what many don't know however is that if you have a UDF (user-defined function) used in a SELECT query result set then that function will be called for every row and thus there will be an overheed. Again measure but for performance sensitive cases it will be significant.
so if you have to squeeze out any performance gain you can have then get rid of the function if you and although redundant have the logic on the query itself.
maintainability wise this is not advisable. so you have to feel the trade-off here
For example:
SELECT fullname = dbo.GetFullName(FirstName, LastName) FROM Employee (NOLOCK)
regardless of the complexity of the GetFullName function's implementation would be slower than having it handled in the query directly. say
SELECT fullname = FirstName + ' ' + LastName FROM Employee (NOLOCK)
Hope to share more soon. But again, don't forget, measure3x