Column aliases

  • Comments posted to this topic are about the item Column aliases

  • Thank you Steve, but i want to suggest yet two ways

    select test

    from

    (

    select mynum

    from dbo.myTable

    ) x(test);

    select 15test

    from dbo.myTable;

    +

    Your queries #1 and #6 are identical ?!

    SELECT mynum AS test

    FROM dbo.myTable AS mt;

  • I think the same thing

    AS Alias And AS 'Alias'

    also i can write AS [Alias], or write CTE

    so there are only three ways: "=", "AS" and column

    I Have Nine Lives You Have One Only
    THINK!

  • SELECT mynum AS test

    FROM dbo.myTable AS mt;

    ...

    SELECT mynum AS test

    FROM dbo.myTable AS mt;

    Can anyone enlighten me how the first and the last differ?

  • 3 or 9 depends how you define different...

    column AS alias

    column AS 'alias'

    column AS [alias]

    column alias

    column 'alias'

    column [alias]

    alias = column

    'alias' = column

    [alias] = column

    And I didn't use double quotes.

    I only lost one point...

  • The right answer

    SELECT mynum AS test

    FROM dbo.myTable AS mt;

    SELECT test = mynum

    FROM dbo.myTable AS mt;

    SELECT 'test' = mynum

    FROM dbo.myTable AS mt;

    SELECT mynum test

    FROM dbo.myTable AS mt;

    SELECT mynum AS 'test'

    FROM dbo.myTable AS mt;

    SELECT mynum 'test'

    FROM dbo.myTable AS mt;

  • Hi Steve, the first and the sixth SELECT statement are the same: Should not be 5 is the correct answer?

    1st Query:

    SELECT mynum AS test

    FROM dbo.myTable AS mt;

    6th Query:

    SELECT mynum AS test

    FROM dbo.myTable AS mt;

    kind regards

    Julien

  • Louis Hillebrand (9/2/2015)


    3 or 9 depends how you define different...

    column AS alias

    column AS 'alias'

    column AS [alias]

    column alias

    column 'alias'

    column [alias]

    alias = column

    'alias' = column

    [alias] = column

    And I didn't use double quotes.

    I only lost one point...

    [] and "" regard "Naming convention for names" and not aliasing a column name.

  • "String literals as column aliases" are a deprecated feature!

  • No difference. Steve made a mistake. Can happen with anyone.

  • I've read the post and he says: "(I've ignored [alias] and "alias" variations for brevity.)". So even when he's talking about six different ways he was also admitting there are variations. That's why my reply was 7 instead of 6.

  • I didn't really care so just guessed and got it right.

    Most developers will only ever use one method. That may depend on in-house standards but I'd recommend that used in the first example.

    I imagine the sixth (duplicate) one should have had square brackets.

  • I wish you all a good afternoon. It is difficult to decide on the correct answer... 🙂

    https://technet.microsoft.com/en-us/library/ms179300(v=sql.105).aspx

    --==================================================================

    USE AdventureWorks2014;-- or AdventureWorks2008R2;

    GO

    SELECT TOP 10 BusinessEntityID [Employee Identification Number]

    FROM HumanResources.Employee;

    SELECT TOP 10 BusinessEntityID 'Employee Identification Number'

    FROM HumanResources.Employee;

    SELECT TOP 10 BusinessEntityID "Employee Identification Number"

    FROM HumanResources.Employee;

    ------------------------------------------------------------

    SELECT TOP 10 BusinessEntityID AS [Employee Identification Number]

    FROM HumanResources.Employee;

    SELECT TOP 10 BusinessEntityID AS 'Employee Identification Number'

    FROM HumanResources.Employee;

    SELECT TOP 10 BusinessEntityID AS "Employee Identification Number"

    FROM HumanResources.Employee;

    ---------------------------------------------------------------------------------------

    SELECT TOP 10 [Employee Identification Number] = BusinessEntityID, NationalIDNumber as NatId

    FROM HumanResources.Employee;

    SELECT TOP 10 'Employee Identification Number' = BusinessEntityID, NationalIDNumber as NatId

    FROM HumanResources.Employee;

    SELECT TOP 10 "Employee Identification Number" = BusinessEntityID, NationalIDNumber as NatId

    FROM HumanResources.Employee;

    ---------------------------------------------------------------------------------------

    SELECT TOP 10 SalesOrderID, ShipDate,

    DaysSinceShipped = DATEDIFF(dd, ShipDate, GETDATE() )

    FROM Sales.SalesOrderHeader

    WHERE ShipDate IS NOT NULL

    ----------------------------------------------------------------------------------------

  • handkot (9/1/2015)


    I think the same thing

    AS Alias And AS 'Alias'

    also i can write AS [Alias], or write CTE

    so there are only three ways: "=", "AS" and column

    +1


    Thanks,

    ToddR

  • Stewart "Arturius" Campbell (9/2/2015)


    Methinks thisis going to turn into an interesting discussion.

    Thanks fot the question, Steve

    I'm thinking that your thinking is right. 😉

    I wonder if this counts as an aliasing twice or three times:

    WITH cte(EmployeeID, Name) AS (

    SELECT ID UselessAlias, Name

    FROM dbo.Employees

    )

    SELECT EmployeeID SomeID, Name

    FROM cte;

    Inside the CTE, the ID column is being aliased as UselessAlias.

    Its being returned from the CTE as EmployeeID. Would you call this an alias?

    In the outer SELECT, its then being aliased as SomeID.

    So, would you call this 2 or 3 aliases?

Viewing 15 posts - 1 through 15 (of 59 total)

You must be logged in to reply to this topic. Login to reply