Q:

What are examples of SQL Server Query features/clauses that should be avoided?

What are examples of SQL Server Query features/clauses that should be avoided?
Recently I got to know that the NOT IN clause degrades performance badly.
Do you have more examples?

A:

The reason to avoid NOT IN isn't really performance, it's that it has really surprising behaviour when the set contains a null.  For example:
select 1
where 1 not in (2,null)

This won't return any rows, because the where is interpreted like:
where 1 <> 2 and 1 <> null

First 1 <> null evaluates to unknown.  Then 1 <> 2 and unknown evaluates to unknown.  So you won't receive any rows.

