SQL Server 2008 Upgrade/compile - Column Alias and Table Alias

Question

One of databases I develop for is being upgraded to SQL 2008 (from SQL 2000).

The upgrade advisor is flagging an issue that I don't think is an issue. I was hoping that there is documentation that this is a known issue so that my DB team will just let it pass.

The error is saying that in SQL 2008 you cannot use a table alias and a column alias together. It also says that the sprocs that use these will not compile.

Here is the different SQL Scenario's that are causing this:

---

select 
     case
       when tblOneAlias.COLUMN_NAME is null then tblTwoAlias.COLUMN_NAME
       else tblOneAlias.COLUMN_NAME
     end as COLUMN_NAME
from tblOne tblOneAlias
     join tblTwo tblTwoAlias
       on tblOneAlias.JOIN_VALUE = tblTwoAlias.JOIN_VALUE
order by tblOneAlias.COLUMN_NAME, tblTwoAlias.COLUMN_NAME

---

select tblAlias.COLUMN_NAME as 'COLUMN_NAME'
from tblName tblAlias
order by tblAlias.COLUMN_NAME

---

select COLUMN_NAME = tblAlias.COLUMN_NAME
from tblName tblAlias
order by tblAlias.COLUMN_NAME

---

In each scenario an alias is created that matches the actual column name (not usually a good idea I agree).

However, they compile just fine in SQL 2008 (with compatibility level set to 10). I think the Upgrade Advisor is just confused because the alias is the same as the column name. I agree that there is some "less than desireable code" here. But I don't think it needs to be changed to upgrade to SQL 2008.

The fewer things we can change with this upgrade means the fewer things to look into if something breaks when when we roll out to production.

If anyone knows of any documentation saying this is a known limitation then please let me know.

Also, if I am wrong and these are not allowed in SQL 2008 somehow (though they compile just fine) then I would also like to know it.

Thanks...

Answer 1

From what I initially read only a column alias in the ORDER BY caluse can't be prefixed by a table alias and this will cause Upgrade Advisor to complain. If you capture a trace of the workload using Profiler, UA can analyze the tracefile and identify the offending SQL so you know where/what to fix.

I've also read that it does not seem to be an issue anymore and was possibly fixed but this hasn't been confirmed by MS from what I could find.

Hope this helps!