CODEWITHSUNDEEP
sql-servercommon-table-expressionwith-statement
E. L.
E. L.

Reputation: 522

Column name Arguments in the "With" Statement in SQL Server

From the Microsoft online document (https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15), it mentioned that the "With" statement can have a column name as an argument, and then it says that:

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

What is "if distinct names for all resulting columns are supplied in the query definition" actually means? I use the "With" statement very often, but I never specify column names in the argument.

I tried to go through the entire document but it appears nowhere have explained this in further detail.

Does anyone know under what situation I need to put specify the column name?

Thanks in advance!

Upvotes: 0

Views: 1620

Answers (1)

SMor
SMor

Reputation: 2882

Quite simply, the resultset of the query that defines the CTE must return a set of columns with distinct names. For example, the following will not work: 

with cte as (select 1 as x, 2 as x)
select * from cte;

The resultset has 2 columns named "x". In such a case, you MUST supply the column names in the definition of the cte since the query produces a resultset with duplicate names. So you would need to use the form: 

with cte(x, y) as (select 1 as x, 2 as x)
select * from cte;

As a general matter, it is a best practice for any resultset to NOT have duplicate column names.

Upvotes: 2

Related Questions