They can occur in WHERE clauses and table JOINS and can be seen in the execution plan of a query as a CONVERT_IMPLICIT predicate. Data Type Precedence often hides these issues as there are no obvious errors when developing, queries still return results etc!
Further Readfing -
- MSDN : Data Type Precedence (Transact-SQL)
- Jes Schultz Borland : Identifying and Correcting SQL Server Implicit Conversion
- Tibor Karaszi : Match those types!
- Jonathan Kehayias : Finding Implicit Column Conversions in the Plan Cache