Is the ‘WITH’ clause only syntactic sugar?

I’m trying to find the best way to iterate over the same set of data multiple times in a single query, and in the way I started wondering wether ‘with’ would save some execution time (by reducing the amount of times I have to query the data I need to work with) since some people claim that it creates only a reference (a subquery) to the source and others claim that it stores the result of the query in memory and then allows you to query them(that’s what I would like it to be) saving tons of execution time.

For better explanation here’s a resume of my code:

WITH MY_DATA AS(
   SELECT
      TABLE_A, TABLE_A.B, TABLE_B.C
   FROM TABLE_A
   JOIN
      TABLE_B
      ON TABLE_A.SOME_FILTER = TABLE_B.SOME_FILTER
   WHERE SOME_OTHER_CONDITION = 0
)
SELECT
  SUM CASE WHEN(A = 'SOME_VALUE') THEN '1' ELSE 0 END
FROM MY_DATA
UNION ALL
SELECT
  SUM CASE WHEN(B = 'SOME_OTHER_VALUE') THEN '1' ELSE 0 END
FROM MY_DATA
UNION ALL
SELECT
  SUM CASE WHEN(C = 'YET_ANOTHER_VALUE') THEN '1' ELSE 0 END
FROM MY_DATA

Would using subquerys in each union make any difference at all? Or am I just making it aesthetically pleasing?

Answer

SQL Server treats CTEs as code blocks that are inserted as code in the query each time the CTE is referenced. Hence, your interpretation of them as “syntactic sugar” does apply — in this case and in this database.

This is specific to SQL Server. Other databases materialize CTEs sometimes or always. In these databases, CTEs make it easier to remove common code (i.e. to re-use the materialized version of the CTE).

In addition, recursive CTEs cannot be expressed in any other ways using a single SELECT query. So, they serve an independent purpose as well.