One very common mistake I see when reviewing code is the case where the select statement is returning multiple rows, but assigning a column to a scalar variable, like this:

select @ID = ID from SomeTable

It works, but the results aren't necessarily reliable. Probably the most common place I see this is inside a trigger, because far too many people expect a trigger to fire once per row (it fires once per batch). Sometimes the values returned are all the same so it doesn't really matter (cmon now, it will matter sooner or later), but I've also seen cases where it's choosing a value randomly. How'd you like that to be used on your checking account balance?

My rule of thumb is if Im assigning to a scalar is to always use Top 1 with an order by - which may not be perfect, but will return consistent results.