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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".