What's the difference between WITH CTE & WITH CTE ()?
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];