Column and Table Alias issue

  • 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.

  • In the upgrade process, are you setting up a test environment and putting the database and all its procs through their paces?

    If so, you'll know with total certainty that this isn't a problem, as soon as you get it into testing.

    If not, then minor issues like a warning from the upgrade advisor are likely to be the least of your worries.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I think I know exactly what "Column aliases in ORDER BY clause cannot be prefixed by table alias"?

    The advisor isn't smart enough to say that there IS a problem just that there MIGHT be a problem and you have to go look. In all the cases I have run accross it wasn't a problem and the warning was not relevant. But you can't just assume. It is important to realize that we are talking about column aliases NOT table aliases, column aliases are defined in the SELECT clause. Read below.

    This is from the upgrade advisor help file:

    New: 5 December 2005

    In SQL Server 2005, column aliases in the ORDER BY clause cannot be prefixed by the table alias.

    Component

    Database Engine

    Description

    For example, the following query executes in SQL Server 2000, but returns an error in SQL Server 2005:

    Copy Code

    USE AdventureWorks;

    GO

    SELECT FirstName AS f, LastName AS l

    FROM Person.Contact p

    ORDER BY p.l

    The SQL Server 2005 Database Engine does not match p.l in the ORDER BY clause to a valid column in the table.

    Exception

    If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error; in SQL Server 2005, the semantics of the statement might be different. For example, the column alias (id) specified in the following statement is a valid column name in the sysobjects table. In SQL Server 2000, when the statement executes, the CAST operation is performed after the result set is sorted. This means the name column is used in the sort operation. In SQL Server 2005, the CAST operation occurs before the sort operation. This means the id column in the table is used in the sort operation and returns the result set in an unexpected order.

    Copy Code

    SELECT CAST (o.name AS char(128)) AS id

    FROM sysobjects AS o

    ORDER BY o.id;

    Corrective Action

    Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause in either of the following ways:

    Do not prefix the column alias in the ORDER BY clause, if possible.

    Replace the column alias with the column name.

    For example, both of the following queries execute without error in SQL Server 2005:

    Copy Code

    USE AdventureWorks;

    GO

    SELECT FirstName AS f, LastName AS l

    FROM Person.Contact p

    ORDER BY l

    USE AdventureWorks;

    GO

    SELECT FirstName AS f, LastName AS l

    FROM Person.Contact p

    ORDER BY p.LastName

    CEWII

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply