What's the difference between WITH CTE & WITH CTE (<column_names>)?

As shown in Using Common Table Expressions on MSDN, you can define a CTE as:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

and use it like:

SELECT  FROM expression_name;

Let's say I have following 2 CTEs

with cte1 as(
select name from Table1
)

with cte2(name) as(
select name from Table1
)

A query outputs the same results for both CTEs as the inner query is same. The only difference between these two is that cte2 has column name((name)) defined in its declaration.

When I execute both CTEs, I don't see any difference in the execution plan.

I am just curious to know:

• What difference does it make if I don't specify any column names in CTE definition?

• Why I should/should not specify column names while creating CTE?

• Does it affect query execution plan by any chance? (As far as I have seen, it doesn't make any difference.)

You nearly have the answer for one of your questions already.

In the MSDN page, there is a line directly after your quote that explains this:


The basic syntax structure for a CTE is:


WITH expression_name [ ( column_name [,...n] ) ]


AS


( CTE_query_definition )


The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

(Emphasis added)

This would mean that you would need to specify column names in a few situations:

-
This would work:

WITH [test_table] ([NoName], [CAST], [Function]) 
AS
(
    SELECT 
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

-
as would this:

WITH [test_table]  
AS
(
    SELECT 
        1 as [NoName]
      , CAST('1' AS CHAR(1)) as [CAST]
      , dbo.CastToChar(1) as [Function]
)
SELECT * FROM [test_table];

-
But this would not as it does not have distinct names for the columns:

WITH [test_table] 
AS
(
    SELECT 
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

StackExchange Database Administrators Q#134525, answer score: 31