4 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS11 clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

On a totally unrelated side-note; always specify the schema name when referencing object names, and end your statements with a semi-colon.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

On a totally unrelated side-note; always specify the schema name when referencing object names, and end your statements with a semi-colon.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

On a totally unrelated side-note; always specify the schema name when referencing object names, and end your statements with a semi-colon.

3 added 467 characters in body
source | link

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

On a totally unrelated side-note; always specify the schema name when referencing object names, and end your statements with a semi-colon.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

On a totally unrelated side-note; always specify the schema name when referencing object names, and end your statements with a semi-colon.

2 alias aligning
source | link

Anecdotally, I prefer to name the columns inside the CTE instead of inside the ;WITHWITH CTE (xxx) AS clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the ;WITH CTE (xxx) AS clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
        , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
    , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
        , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
    , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS clause since you'll never inadvertently mismatch the names vs the column contents.

Take for instance the following example:

;WITH MyCTE (x, y)
AS 
(
    SELECT mt.y
         , mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.x
     , MyCTE.y
FROM MyCTE;

What does this display? It shows the contents of the y column under the heading of x, and the contents of the x column under the heading y.

With this realization, I never specify the column names in the (xxx) AS clause, instead I do it like this:

;WITH MyCTE
AS 
(
    SELECT Alias1 = mt.y
         , Alias2 = mt.x
    FROM MySchema.MyTable mt
)
SELECT MyCTE.Alias1
     , MyCTE.Alias2
FROM MyCTE;

This removes all doubt about what the column definitions are.

1
source | link