SQL same column name allowed

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.

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.

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.

You are giving two same aliases name to different column that will render as duplicate column in one table.

That's why it is giving error.

If you will make it different alias name then error will not occur.just try it and let me know whether it will work or not.