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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.