Let's continue exploring some of the SQL Smells from Phil's list.   He has been putting together.
Datatype mis-matches in predicates that rely on implicit conversion.(Plamen Ratchev)
This is a great example poking holes in the whole theory of "If it works it's not broken"   Queries will this probably will generally work and give the correct response.   In fact, without careful analysis, you probably may be completely oblivious that there is even a problem.   This subtle little problem will needlessly complicate queries and slow them down regardless of the indexes applied.
Consider this example:
CREATE
			TABLE [dbo].[Page](
    [PageId] [int] IDENTITY(1,1)
				NOT
				NULL,
    [Title] [varchar](75)
			NOT
			NULL,
    [Sequence] [int]  NOT
			NULL,
    [ThemeId] [int]  NOT
			NULL,
    [CustomCss] [text]  NOT
			NULL,
    [CustomScript] [text]  NOT
			NULL,
    [PageGroupId] [int]  NOT
			NULL;
		
 CREATE
			PROCEDURE PageSelectBySequence
			(
      @sequenceMin smallint
			,
      @sequenceMax smallint
			)
AS
		
			BEGIN
			SELECT  [PageId] ,
                [Title] ,
                [Sequence] ,
                [ThemeId] ,
                [CustomCss] ,
                [CustomScript] ,
                [PageGroupId]
			FROM    [CMS].[dbo].[Page]
			WHERE   Sequence BETWEEN @sequenceMin AND @SequenceMax
			END
 Note that the Sequence column is defined as int while the sequence parameter is defined as a small int.   The problem is that the database may have to do a lot of type conversions to evaluate the query.  In some cases, this may even negate the indexes that you have in place.
Using Correlated subqueries instead of a join   (Dave_Levy/ Plamen Ratchev)
There are two main problems here.    The first is a little subjective, since this is a non-standard way of expressing the query, it is harder to understand.
The other problem is much more objective and potentially problematic.  You are taking much of the control away from the optimizer.  Written properly, such a query may well out perform a corresponding query written with traditional joins.    More likely than not, performance will degrade.  
Whenever you assume that you know better than the optimizer, you will most likely be wrong.   This is the fundmental problem with any hint.
Consider a query like this:
 SELECT  Page.Title ,
        Page.Sequence ,
        Page.ThemeId ,
        Page.CustomCss ,
        Page.CustomScript ,
        PageEffectParams.Name ,
        PageEffectParams.Value ,
				( SELECT    EffectName
			FROM      dbo.Effect
			WHERE     EffectId = dbo.PageEffects.EffectId
			)
			AS EffectName
FROM    Page
			INNER
			JOIN PageEffect ON Page.PageId = PageEffects.PageId
			INNER
			JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId
 This can and should be written as:
 SELECT  Page.Title ,
        Page.Sequence ,
        Page.ThemeId ,
        Page.CustomCss ,
        Page.CustomScript ,
        PageEffectParams.Name ,
        PageEffectParams.Value ,
        EffectName
FROM    Page
			INNER
			JOIN PageEffect ON Page.PageId = PageEffects.PageId
			INNER
			JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId
			INNER
			JOIN dbo.Effect ON dbo.Effects.EffectId = dbo.PageEffects.EffectId
 The correlated query may just as easily show up in the where clause.   It's not a good idea in the select clause or the where clause.
Few or No comments.
This one is a bit more complicated and controversial.  All comments are not created equal.  Some comments are helpful and need to be included.  Other comments are not necessary and may indicate a problem.
I tend to follow the rule of thumb that comments that explain why are good.  Comments that explain how are bad.   Many people may be shocked to hear the idea of a bad comment, but hear me out.
If a comment is needed to explain what is going on or how it works, the logic is too complex and needs to be simplified.  
Comments that explain why are good.   Comments may explain why the sql is needed are good.   Comments that explain where the sql is used are good.   Comments that explain how tables are related should not be needed if the sql is well written.   If they are needed, you need to consider reworking the sql or simplify your data model.
Use of functions in a WHERE clause. (Anil Das)
			Calling a function in the where clause will often negate the indexing strategy.  The function will be called for every record considered.   This will often a force a full table scan on the tables affected.
Calling a function will not guarantee that there is a full table scan, but there is a good chance that it will.  If you find that you often need to write queries using a particular function, you may need to add a column to the table that has the function already applied.