I have a question about CTEs. You can start of a sql statement by WITH and then you can construct one or multiple CTE queries, which you then in the end can make (in this case) a select on.
My question is: will all CTE queries be executed or only the ones who are being used?
E.g.
WITH cte_1
as (
Select * from table1
),
cte_2
as (
Select * from table2
)
Select * from cte_1
Will this mean that a select will be executed on table1 and table2?
To complete Master Gordon's answer (I dare) :
if you declare 2 cte, and use none of them, you also get that error message
with cte as (select 1/0 as val), cte2 as (select 1/0 as val)
select 1
Msg 422 Level 16 State 4 Line 2
Common table expression defined but not used.
But if you use at least one, the statement is accepted :
with cte_divide_by_zero as (select 1/0 as val), cte_legit as (select 'it works' as val)
select * from cte_divide_by_zero
Msg 8134 Level 16 State 1 Line 1
Divide by zero error encountered.
And if you select the other CTE, it proves that your unused CTE is never executed as no error occurs :
with cte_divide_by_zero as (select 1/0 as val), cte_legit as (select 'it works' as val)
select * from cte_legit
val
--------
it works
I get this message:
Msg 422 Level 16 State 4 Line 2
Common table expression defined but not used.
when running:
with cte as (select 1/0 as val)
select 1
Here is a db<>fiddle.