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

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

2 Answers