Do unused CTEs in queries affect performance and / or alter the generated query plan?
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]] 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. : https://i.stack.imgur.com/Ryx9a.jpg
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'. */