Impact Of Ordering Of Correlated Subqueries Within A Projection

I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query p

Solution 1:

With the TOP operator coming into play here, the Query Optimizer is remarkably blind about the statistics, so it will look for other clues about how best to work it, such as instantiating relevant parts of the CTE first.

And it's an outer join because the subquery will be used as NULL if nothing is returned, and the system is instantiating it first. If you were using an aggregate instead of TOP, you'd probably get a slightly different but more consistent plan.

Solution 2:

Here is an alternate version that might perform better:

With Colors As
    (
    Select Id, [Color]
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDERBY [LastModified] DESC ) As Num
    From Preference
    Where [Color] IsNot Null
    )
    , Names As
    (
    Select Id, [FirstName]
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDERBY [LastModified] DESC ) As Num
    From Preference
    Where [FirstName] IsNot Null
    )
SelectFrom Person As P
    Join Colors As C
        On C.Id = P.Id
            And C.Num = 1
    Left Join Names As N
        On N.Id = P.Id
            And N.Num = 1Where C.[Color]= 'Grey'

Another solution which is more concise but may or may not perform as well:

With RankedItems
    (
    Select Id, [Color], [FirstName]
        , ROW_NUMBER() OVER ( PARTITIONBY ID ORDERBYCaseWhen [Color] IsNotNull1Else0EndDESC, [LastModified] DESC ) As ColorRank
        , ROW_NUMBER() OVER ( PARTITIONBY ID ORDERBYCaseWhen [FirstName] IsNotNull1Else0EndDESC, [LastModified] DESC ) As NameRank
    From Preference
    )
SelectFrom Person As P
    Join RankedItems As RI
        On RI.Id = P.Id
            And RI.ColorRank =1LeftJoin RankedItems As RI2
        On RI2.Id = P.Id
            And RI2.NameRank =1Where RI.[Color]='Grey'