Derived Table, CTE or View: Choose the Scope

A derived table, CTE and ordinary view can express the same grouping while providing different naming scopes. Choose the scope you need before assuming a performance difference. The spelling alone does not promise faster execution.

Compare results using duplicates and NULLs

The example has eight made-up payments and four groups. Group A contains two equal payments and a NULL amount. Two payments have a NULL group name. Group C contains only a NULL amount, while Group B combines positive and negative values.

Create the table and its eight rows first. Later in the post an ordinary view is added, and a cleanup block at the end removes everything.

DROP TABLE IF EXISTS dbo.Payments;

CREATE TABLE dbo.Payments
(
    Id int NOT NULL PRIMARY KEY,
    GroupName varchar(10) NULL,
    Amount decimal(9,2) NULL
);

INSERT dbo.Payments VALUES
    (1, 'A', 10), (2, 'A', 10), (3, 'A', NULL),
    (4, 'B', 25), (5, 'B', -5),
    (6, NULL, 5), (7, NULL, 5),
    (8, 'C', NULL);

A derived table names an inline query

SELECT *
FROM (
    SELECT GroupName, COUNT_BIG(*) AS RowsInGroup,
           COUNT(Amount) AS NonNullAmounts, SUM(Amount) AS TotalAmount
    FROM dbo.Payments
    GROUP BY GroupName
) AS d
ORDER BY GroupName;

The alias d names this query inside the surrounding statement. It creates no saved database object. Keep the final ORDER BY where you need a defined presentation order. The grouping itself is unchanged by the alias.

A CTE names a result for one following statement

;WITH t AS (
    SELECT GroupName, COUNT_BIG(*) AS RowsInGroup,
           COUNT(Amount) AS NonNullAmounts, SUM(Amount) AS TotalAmount
    FROM dbo.Payments
    GROUP BY GroupName
)
SELECT * FROM t ORDER BY GroupName;

A CTE name is visible only to the one statement that follows it. This CTE is not a stored copy of the grouped rows. A later statement that uses the CTE name returns error 208 because the name is unavailable there.

SELECT * FROM t ORDER BY GroupName;
-- Msg 208, Invalid object name 't'.

An ordinary view saves the query definition

CREATE VIEW dbo.Totals AS
SELECT GroupName, COUNT_BIG(*) AS RowsInGroup,
       COUNT(Amount) AS NonNullAmounts, SUM(Amount) AS TotalAmount
FROM dbo.Payments
GROUP BY GroupName;

CREATE VIEW must start its own batch, so run it by itself or after a GO. The ordinary view remains addressable by subsequent statements until removed. Saving this definition does not mean the grouped output is stored as a separate table.

SELECT * FROM dbo.Totals ORDER BY GroupName;

Check meaning before comparing plan shapes

All three queries produced these same four rows on SQL Server 2025. COUNT_BIG(*) includes each input row. COUNT(Amount) counts non-NULL amounts. Group C therefore has one input row, zero non-NULL amounts and a NULL sum.

The equal A payments both contribute to its total of 20. The NULL group contains two payments totaling 10. This comparison preserves duplicate inputs and NULL meaning. Replacing an ordinary view with an indexed view would introduce different requirements beyond this example.

The three plans have the same sequence of physical operator types in this small example. They include a clustered index scan, sort, stream aggregate and compute scalar. These plans establish no general timing or memory advantage. Choose readability and naming scope, then measure the actual workload.

When you are done, remove the demo objects.

DROP VIEW dbo.Totals;
DROP TABLE dbo.Payments;

Pick the scope that fits the job, and measure before you worry about speed.

A derived table, CTE or view is not a different answer, it is a different scope.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.