If a CTE is defined in a query and is never used, does it make a sound?

Question

Do unused CTEs in queries affect performance and / or alter the generated query plan?

Answer 1

It doesn't appear that they do, but this really only applies to nested CTEs.

Create two temp tables:

CREATE TABLE #t1 (id INT);
INSERT #t1 ( id )
VALUES ( 1 );

CREATE TABLE #t2 (id INT);
INSERT #t2 ( id )
VALUES ( 1 );

Query 1:

WITH your_mom AS (
    SELECT TOP 1 *
    FROM #t1 AS t 
),
also_your_mom AS (
    SELECT TOP 1 *
    FROM #t2 AS t
)
SELECT *
FROM your_mom;

Query 2:

WITH your_mom AS (
    SELECT TOP 1 *
    FROM #t1 AS t 
),
also_your_mom AS (
    SELECT TOP 1 *
    FROM #t2 AS t
)
SELECT *
FROM also_your_mom;

Query plans:

There is an overhead, but the unnecessary portion of the query is eliminated very early (during parsing in this case; the simplification stage in more complex cases), so the additional work is truly minimal, and does not contribute to potentially expensive cost-based optimization.

Answer 2

+1 to Erik, but wanted to add two things (which did not work well in a comment):

1. You don't even need to look at execution plans to see that they are ignored when not used. The following should produce a "divide by 0" error but does not due to cte2 not being selected from at all:

   ;WITH cte1 AS
   (
     SELECT 1 AS [Bob]
   ),
   cte2 AS (
     SELECT 1 / 0 AS [Err]
     FROM cte1
   )
   SELECT *
   FROM   cte1;
   

2. CTE's can be ignored, even if they are the only CTE, and even if they are selected from, if logically all rows would be excluded anyway. The following is a case where the query optimizer knows ahead of time that no rows could be returned from the CTE, so it doesn't even bother to execute it:

   ;WITH cte AS
   (
     SELECT 1 / 0 AS [Bob]
   )
   SELECT TOP (1) [object_id]
   FROM   sys.objects
   UNION ALL
   SELECT cte.[Bob]
   FROM   cte
   WHERE  1 = 0;
   

Regarding performance, the unused CTE is parsed and compiled (or at least compiled in the case below), so it is not 100% ignored, but the cost would have to be negligible and not worth being concerned about.

When only parsing, there is no error:

SET PARSEONLY ON;

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET PARSEONLY OFF;
GO

When doing everything just short of execution, then there is a problem:

GO
SET NOEXEC ON;
GO

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET NOEXEC OFF;
GO
/*
Msg 207, Level 16, State 1, Line XXXXX
Invalid column name 'NotHere'.
*/