One very specific no-no that I still see all the time:
Never use ISNULL() or NULLIF() in a WHERE clause or JOIN.
You should avoid any functions against columns whenever possible (because of "SARGability"), but on rare occasions it's not possible. However, you can always avoid using ISNULL(), and you should.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.