title: SQL Server 2008 Upgrade/compile - Column Alias and Table Alias
body: <p>One of databases I develop for is being upgraded to SQL 2008 (from SQL 2000).</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is the different SQL Scenario's that are causing this:</p>
  
  <hr>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <pre><code>select tblAlias.COLUMN_NAME as 'COLUMN_NAME'
  from tblName tblAlias
  order by tblAlias.COLUMN_NAME
  </code></pre>
  
  <hr>
  
  <pre><code>select COLUMN_NAME = tblAlias.COLUMN_NAME
  from tblName tblAlias
  order by tblAlias.COLUMN_NAME
  </code></pre>
  
  <hr>
  
  <p>In each scenario an alias is created that matches the actual column name (not usually a good idea I agree).</p>
  
  <p>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.  </p>
  
  <p>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.</p>
  
  <p>If anyone knows of any documentation saying this is a known limitation then please let me know.</p>
  
  <p>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.</p>
  
  <p>Thanks...</p>