Once you've done a number of SQL code-reviews, you'll be able to identify signs in the code that indicate all might not be well. These 'code smells' are coding styles, some generic, and some particular to SQL Server, that, while not bugs, suggest design problems with the code.
In this PDF, Phil's put together 119 of those code smells so you can see what to avoid and why.

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.