locked
Regarding CTE usage RRS feed

  • Question

  • User264732274 posted

    see the below CTE sample. tell me this is necessary (EmpID, EmpName, EmpGrade) ?

    why we need to say fields name in CTE. if it has any significance then please discuss the  importance and reason for usage for saying fields name. thanks

    ;WITH EmployeeDetails
         (EmpID, EmpName, EmpGrade)
    AS
    (
        SELECT EmpID, EmpName, EmpGrade
          FROM   [dbo].[tbl_Employee]

    )

    Thursday, January 28, 2016 1:36 PM

All replies

  • User2053451246 posted

    The column definitions are not required.  Per MSDN:

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

    https://msdn.microsoft.com/en-us/library/ms175972.aspx?f=255&MSPPError=-2147217396

    You can simple write:

    WITH EmployeeDetails AS (
        SELECT
            Field1
            , Field2
        FROM Employees
    )

    Thursday, January 28, 2016 2:29 PM
  • User264732274 posted

    when people give column name....can u tell me any scenario?

    Thursday, January 28, 2016 5:55 PM
  • User2053451246 posted

    My first post answers that:

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

    So, if the SQL to get the data for the CTE has duplicate column names (the opposite of distinct) you need to specify the columns of the CTE.

    Thursday, January 28, 2016 6:05 PM
  • User753101303 posted

    Basically this is just a column name alias so it can be usefull if you have two column named the same way coming from two different tables or even if you just want to use an alias because the original name for the column is unclear to you. And maybe other cases I don't think about right now.

    In short when you have or when you want to use column aliases.

    For example :

    -- ERROR: No column name was specified for column 2 of 'CTE'
    WITH CTE AS (
    SELECT Data_Type,COUNT(*) FROM Information_SChema.Columns GROUP BY Data_Type
    )
    SELECT * FROM cte
    
    -- Works
    ;WITH CTE AS (
    SELECT Data_Type,COUNT(*) AS TypeCount FROM Information_SChema.Columns GROUP BY Data_Type
    )
    SELECT * FROM cte
    
    -- Works 
    ;WITH CTE(TypeName,TypeCount) AS (
    SELECT Data_Type,COUNT(*) FROM Information_SChema.Columns GROUP BY Data_Type
    )
    SELECT * FROM cte

    Thursday, January 28, 2016 6:27 PM