Tweeted twitter.com/StackDBAs/status/717767995083325441
2 added 24 characters in body; edited title
source | link

Whats What's the difference between ;WITHWITH CTE & ;WITHWITH CTE  (<column_names>)?

As givenshown in MSDNUsing Common Table Expressions docon MSDN, you can define a CTE as below:

;WITHWITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

&and use it like  :

SELECT <column_list> FROM expression_name;

LetsLet's say I have following 2 CTEs

;withwith cte1 as(
select name from Table1
)

;withwith cte2(name) as(
select name from Table1
)

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

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

So 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.)

Whats the difference between ;WITH CTE & ;WITH CTE(<column_names>)

As given in MSDN doc you can define CTE as below:

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

& use it like  

SELECT <column_list> FROM expression_name;

Lets say I have following 2 CTEs

;with cte1 as(
select name from Table1
)

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

Select query outputs same results for both CTEs as the inner query is same. The only difference between these two is cte2 have column name((name)) defined while declaring. When I execute both CTEs, I don't see any difference in execution plan.

So 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.)

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 <column_list> 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.)
1
source | link

Whats the difference between ;WITH CTE & ;WITH CTE(<column_names>)

As given in MSDN doc you can define CTE as below:

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

& use it like

SELECT <column_list> FROM expression_name;

Lets say I have following 2 CTEs

;with cte1 as(
select name from Table1
)

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

Select query outputs same results for both CTEs as the inner query is same. The only difference between these two is cte2 have column name((name)) defined while declaring. When I execute both CTEs, I don't see any difference in execution plan.

So 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.)