Subselect vs Group By

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N'[dbo].[AN_Anchor]') AND type in (N'U'))
DROP TABLE [dbo].[AN_Anchor]

CREATE TABLE AN_Anchor (
    AN_ID int NOT NULL,
    PRIMARY KEY(AN_ID)
);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N'[dbo].[ANAT1_AnchorAttribute1]') AND type in (N'U'))
DROP TABLE [dbo].[ANAT1_AnchorAttribute1]

CREATE TABLE ANAT1_AnchorAttribute1 (
    AN_ID int NOT NULL references [dbo].[AN_Anchor](AN_ID),
    ANAT1_AnchorAttribute1Value char(10) NOT NULL,
    PRIMARY KEY(AN_ID)
);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N'[dbo].[ANAT2_AnchorAttribute2]') AND type in (N'U'))
DROP TABLE [dbo].[ANAT2_AnchorAttribute2]

CREATE TABLE ANAT2_AnchorAttribute2 (
    AN_ID int NOT NULL references [dbo].[AN_Anchor](AN_ID),
    ANAT2_AnchorAttribute2Value char(10) NOT NULL,
    ANAT2_FromDate date NOT NULL,
    PRIMARY KEY(AN_ID, ANAT2_FromDate)
);

IF  EXISTS (SELECT * FROM sys.views WHERE object_id =
   OBJECT_ID(N'[dbo].[lAN_Anchor]'))
DROP VIEW [dbo].[lAN_Anchor]
GO

CREATE VIEW lAN_Anchor
AS
SELECT
    AN.AN_ID,
    ANAT1.ANAT1_AnchorAttribute1Value,
    ANAT2.ANAT2_AnchorAttribute2Value,
    ANAT2.ANAT2_FromDate
FROM
    AN_Anchor AN
LEFT JOIN
    ANAT1_AnchorAttribute1 ANAT1
ON
    ANAT1.AN_ID = AN.AN_ID
LEFT JOIN
    ANAT2_AnchorAttribute2 ANAT2
ON
    ANAT2.AN_ID = AN.AN_ID
AND
    ANAT2.ANAT2_FromDate = (
        SELECT
            MAX(sub.ANAT2_FromDate)
        FROM
            ANAT2_AnchorAttribute2 sub
        WHERE
            sub.AN_ID = AN.AN_ID
    );
GO


CREATE VIEW lAN_Anchor_Alt
AS
SELECT
    AN.AN_ID,
    ANAT1.ANAT1_AnchorAttribute1Value,
    ANAT2.ANAT2_AnchorAttribute2Value,
    ANAT2.ANAT2_FromDate
FROM
    AN_Anchor AN
LEFT JOIN
    ANAT1_AnchorAttribute1 ANAT1
ON
    ANAT1.AN_ID = AN.AN_ID
LEFT JOIN (
    SELECT
        AN_ID,
        MAX(ANAT2_FromDate) as ANAT2_MaxFromDate
    FROM
        ANAT2_AnchorAttribute2
    GROUP BY
        AN_ID
) ANAT2_max
ON
    ANAT2_max.AN_ID = AN.AN_ID
LEFT JOIN
    ANAT2_AnchorAttribute2 ANAT2
ON
    ANAT2.AN_ID = ANAT2_max.AN_ID
AND
    ANAT2.ANAT2_FromDate = ANAT2_max.ANAT2_MaxFromDate
GO


INSERT INTO AN_Anchor (
    AN_ID
)
VALUES
(1);

INSERT INTO AN_Anchor (
    AN_ID
)
VALUES
(2);

INSERT INTO AN_Anchor (
    AN_ID
)
VALUES
(3);

update statistics AN_Anchor;

INSERT INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(1, 'Green');

INSERT INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(2, 'Blue');

INSERT INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(3, 'Red');

update statistics ANAT1_AnchorAttribute1;

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(1, 'Car', '2008-01-01');

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(1, 'Bus', '2008-12-12');

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(2, 'Train', '2008-01-01');

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(2, 'Car', '2008-12-12');

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(3, 'Train', '2008-01-01');

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(3, 'Bus', '2008-12-12');


GO
SET STATISTICS PROFILE ON;
GO
SET SHOWPLAN_TEXT ON;
GO

-- tests comparing query cost using the two views
-- different execution plans
select * from lAN_Anchor;     -- 43%
select * from lAN_Anchor_Alt; -- 57%

-- same execution plan
select ANAT1_AnchorAttribute1Value from lAN_Anchor;     -- 50%
select ANAT1_AnchorAttribute1Value from lAN_Anchor_Alt; -- 50%

-- different execution plans
select ANAT2_AnchorAttribute2Value from lAN_Anchor;     -- 40%
select ANAT2_AnchorAttribute2Value from lAN_Anchor_Alt; -- 60%

-- same execution plan
select ANAT2_AnchorAttribute2Value from lAN_Anchor     -- 50%
where ANAT2_AnchorAttribute2Value = 'Bus';
select ANAT2_AnchorAttribute2Value from lAN_Anchor_Alt -- 50%
where ANAT2_AnchorAttribute2Value = 'Bus';

-- conclusion, in SQL Server, lAN_Anchor wins when no conditions are set
-- and is equally matched with lAN_Anchor_Alt otherwise

GO
SET SHOWPLAN_TEXT OFF;
GO
SET STATISTICS PROFILE OFF;
GO