1. Narrow Search Space :  Not considering the SQL Resources at all and considering them as limitless, often failing to narrow the search space from any operations, especially joins, cross apply and complex  probable expensive operations.
  2.  Joins on varchar(max) : Accidental or ignored varchar(max) column matches can be very expensive. Varchar(max) implies data is externally stored besides o the table page i.e any joins or  any set operation or any match then have to done by referencing to the data beside the table itself.
  3.  Joins on varchar : Int is the optimized and preferred mode, if the joins or matches are done on the varchar columns then such operations will certainly be expensive.