Do unused CTEs in queries affect performance and / or alter the generated query plan?
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 );
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;
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;
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 to Erik, but wanted to add two things (which did not work well in a comment):
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
cte2not being selected from at all:
;WITH cte1 AS ( SELECT 1 AS [Bob] ), cte2 AS ( SELECT 1 / 0 AS [Err] FROM cte1 ) SELECT * FROM cte1;
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'. */