If a CTE is defined in a query and is never used, does it make a sound?
+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 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;

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