7 Incorporated comment
source | link
  • If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
  • Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
  • The apply approach is optimal for relatively large groups. The estimate is 2 rows per iteration but the 'actual' is over all iterations (SSMS design decision). A very large number of iterations is bad for the apply.
  • If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
  • Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
  • If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
  • Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
  • The apply approach is optimal for relatively large groups. The estimate is 2 rows per iteration but the 'actual' is over all iterations (SSMS design decision). A very large number of iterations is bad for the apply.
6 Code comments
source | link
SELECT
    TF.a,
    TF.b,
    TF.c,
    TF.d,
    TF.e
FROM
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
                PARTITION BY a,b,c 
                ORDER BY d ASC, e ASC)
    FROM dbo.Test AS T
) AS TF
WHERE
    TF.rn = 1

UNION ALL

SELECT
    TL2.a,
    TL2.b,
    TL2.c,
    TL2.d,
    TL2.e
FROM 
(
    -- TOP (max bigint) to allow an ORDER BY in this scope
    SELECT TOP (9223372036854775807)
        TL.a,
        TL.b,
        TL.c,
        TL.d,
        TL.e
    FROM 
    (
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                    PARTITION BY a,b,c 
                    ORDER BY d DESC, e DESC)
        FROM dbo.Test AS T
    ) AS TL
    WHERE
        TL.rn = 1
    ORDER BY
        -- To allow the optimizer to scan the index backward
        -- (Workaround for PARTITION BY being assumed ASC)
        TL.a DESC,
        TL.b DESC,
        TL.c DESC,
        TL.d DESC,
        TL.e DESC
) AS TL2;
SELECT
    TF.a,
    TF.b,
    TF.c,
    TF.d,
    TF.e
FROM
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
                PARTITION BY a,b,c 
                ORDER BY d ASC, e ASC)
    FROM dbo.Test AS T
) AS TF
WHERE
    TF.rn = 1

UNION ALL

SELECT
    TL2.a,
    TL2.b,
    TL2.c,
    TL2.d,
    TL2.e
FROM 
(
    -- TOP (max bigint) to allow an ORDER BY
    SELECT TOP (9223372036854775807)
        TL.a,
        TL.b,
        TL.c,
        TL.d,
        TL.e
    FROM 
    (
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                    PARTITION BY a,b,c 
                    ORDER BY d DESC, e DESC)
        FROM dbo.Test AS T
    ) AS TL
    WHERE
        TL.rn = 1
    ORDER BY
        TL.a DESC,
        TL.b DESC,
        TL.c DESC,
        TL.d DESC,
        TL.e DESC
) AS TL2;
SELECT
    TF.a,
    TF.b,
    TF.c,
    TF.d,
    TF.e
FROM
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
                PARTITION BY a,b,c 
                ORDER BY d ASC, e ASC)
    FROM dbo.Test AS T
) AS TF
WHERE
    TF.rn = 1

UNION ALL

SELECT
    TL2.a,
    TL2.b,
    TL2.c,
    TL2.d,
    TL2.e
FROM 
(
    -- TOP (max bigint) to allow an ORDER BY in this scope
    SELECT TOP (9223372036854775807)
        TL.a,
        TL.b,
        TL.c,
        TL.d,
        TL.e
    FROM 
    (
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                    PARTITION BY a,b,c 
                    ORDER BY d DESC, e DESC)
        FROM dbo.Test AS T
    ) AS TL
    WHERE
        TL.rn = 1
    ORDER BY
        -- To allow the optimizer to scan the index backward
        -- (Workaround for PARTITION BY being assumed ASC)
        TL.a DESC,
        TL.b DESC,
        TL.c DESC,
        TL.d DESC,
        TL.e DESC
) AS TL2;
5 Incorporated comments
source | link

To summarize the comment discussion:

  • If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
  • Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.

To summarize the comment discussion:

  • If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
  • Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
4 Updated solution 2 based on Martin Smith's suggestion
source | link
3 Added solution 3
source | link
2 added 47 characters in body
source | link
1
source | link