#0241 – SQL Server – Interview question – Msg 209, Msg 8156 – Duplicate aliases in SELECT clause are NOT ambiguous column names

When SQL Server 2005 came about, the following error message was encountered by many developers so many times that the answer for it would be available if asked in the middle of the night:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name ‘BusinessEntityID’.

This message generally comes when one has the same column name/alias defined in an ORDER BY operation. Since ORDER BY operates on the SELECT-ed result set, it needs that the column names/aliases it operates on are unique and/or properly qualified if they are coming from different tables. Here’s an example which generates the message shown above:

USE AdventureWorks2012;

GO

–Query below results in error: “Ambiguous column name ‘BusinessEntityID'”

SELECT he.BusinessEntityID,

       heph.BusinessEntityID,

       heph.Rate,

       heph.PayFrequency,

       heph.RateChangeDate

FROM HumanResources.Employee AS he

INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID

ORDER BY BusinessEntityID;

GO

Recently, we were conducting an interview and one of the panel members came up with a modification to this query. Instead of having an ambiguous reference in the ORDER BY clause, the query was modified such that two columns had the same column alias in the SELECT statement:

–What will be the output of the query below?

–Will it return an error?

–Please explain your answers
USE AdventureWorks2012;

GO

SELECT he.BusinessEntityID AS EmployeeId,

       heph.BusinessEntityID AS EmployeeId,

       heph.Rate,

       heph.PayFrequency,

       heph.RateChangeDate

FROM HumanResources.Employee AS he

INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID;

GO

The question therefore are very simply:

What will be the output of the query? Will it return an error? Please explain your answers.

Here’s the correct answer to this question:

This query will succeed compilation and execution. The output will be similar to the one shown below – first two columns will be named “EmployeeId”, followed by “Rate”, “PayFrequency” and finally “RateChangeDate”.

The query succeeds because column aliases do not affect query evaluation in any way – they are only for representational purposes. The real problem would come when the aliases are used in an ORDER BY clause or in another query as a sub-query or CTE.

–Use duplicate column aliases in CTE results in an error

USE AdventureWorks2012;

GO

;WITH HumanResourcesPayCTE (EmployeeId, EmployeeId, Rate, PayFrequency, RateChangeDate)

AS (SELECT he.BusinessEntityID AS EmployeeId,

           heph.BusinessEntityID AS EmployeeId,

           heph.Rate,

           heph.PayFrequency,

           heph.RateChangeDate

    FROM HumanResources.Employee AS he

    INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID

    )

SELECT * FROM HumanResourcesPayCTE;

GO

Msg 8156, Level 16, State 1, Line 1
The column ‘EmployeeId’ was specified multiple times for ‘HumanResourcesPayCTE’.

This small twist in the question was enough to send the candidates scurrying to re-read the Books On Line!

I hope you found the post useful.

Until we meet next time,

Be courteous. Drive responsibly.

[EDIT: February 19, 2013, 14:20 IST]: Added clarification to the reason as to why ORDER BY returns an ambiguous column name error.

3 thoughts on “#0241 – SQL Server – Interview question – Msg 209, Msg 8156 – Duplicate aliases in SELECT clause are NOT ambiguous column names”

marc_jellinek@hotmail.com February 18, 2013 at 11:15 PM

The problem isn’t that the columns have the same name, the problem is that the query processor can’t know WHICH column to order by; HumanResources.Employee.BusinessEntityID or HumanResources.EmployeePayHistory.BusinessEntityID

The solution is to use the table aliases in the ORDER BY the same way they were used in the SELECT statement. This dis-ambiguates the column.

USE AdventureWorks2012;
GO
–Query below results in error: “Ambiguous column name ‘BusinessEntityID'”
SELECT he.BusinessEntityID,
heph.BusinessEntityID,
heph.Rate,
heph.PayFrequency,
heph.RateChangeDate
FROM HumanResources.Employee AS he
INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID
ORDER BY he.BusinessEntityID;
GO

LikeLike

Reply ↓

Nakul Vachhrajani February 19, 2013 at 3:05 AM

@Marc: Absolutely. For the ORDER BY clause, that’s the solution (in fact, I advocate the use of qualifying aliases in all queries – all objects, all columns). This clarification has been added as an edit to the post.

The twist to the tale in this post was whether using the same column alias would confuse the SELECT statement or not. Generally, novice developers tend to think that because the human mind would get confused by looking at two columns of the same name, SQL Server would too. But that’s not the case – it does not make a difference to SQL Server as to what name/alias a column is given for representational purposes in the result set as long as it’s coming from a properly qualified source. It does become a problem though when the result-set is used in another query as a CTE or sub-query.

LikeLike

Reply ↓