sql-server add tag
j d (imported from SE)
Do unused CTEs in queries affect performance and / or alter the generated query plan?
Top Answer
meme (imported from SE)
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:

[![NUTS][1]][1]

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.

  [1]: https://i.stack.imgur.com/Ryx9a.jpg
Answer #2
Solomon Rutzky (imported from SE)
+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;

1. 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'.
    */

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.