SQL same column name allowed

Question

When I run this query in SQL Server

SELECT custid AAA, companyname AAA
FROM Sales.Customers
WHERE country = 'USA'

It's running fine. But now the result set have duplicate column name(AAA). Why SQL server allow this to happen? How an application works if it needs to reference the column name? I know if you put this query as a derived table SQL will stop you. like

SELECT * 
FROM 
     (SELECT custid AAA, companyname AAA
      FROM Sales.Customers
      WHERE country = 'USA') BBB

SQL Server reports an error:

The column 'AAA' was specified multiple times for 'BBB'

What's the logic behind this?

Thanks

Answer 1

Reason for this problem is:

1st Query

SELECT custid AAA, companyname AAA
   FROM Sales.Customers
   WHERE country = 'USA'

Here, you are assigning column alias at the time of output, so AAA is as column name(alias actually) attached at the time of returning result, but i guess you will see only 1st column with AAA other will be removed because of possible confliction during further reference. so here you are not getting error.

---

2nd Query

SELECT * FROM (
    SELECT custid AAA, companyname AAA
    FROM Sales.Customers
    WHERE country = 'USA') BBB

Here you got error because, you are selecting records from Inline View with name BBB, here that Inner Query(inline view) is considered as a Table(for your SELECT * FROM statement), and as we know - basically Table can not have multiple same column name, because of that you are getting error that BBB has multiple AAA column.

Answer 2

This can be explained by understanding order of execution of different logical phases of query execution. Query Execution Order MSDN

In SQL Server the order is FROM > WHERE > SELECT i.e. first FROM clause is executed then WHERE clause and last is the SELECT list.

Now in your first query , all matching rows from table Sales.Customers are fetched and then then afterwards columns specified in SELECT list are pulled out and then Alias names are applied.

In your second query , the inner query is executed successfully as the first query but when the outer query's FROM clause tries to fetch columns from resultset returned by inner query , it finds duplicate columns and throws error.