Issue: 6

Issue: 6, Column aliases in ORDER BY clause cannot be prefixed by table alias

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

Description

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

USE AdventureWorks;

GO

SELECT FirstName AS f, LastName AS l

FROM Person.Contact p

ORDER BY p.l

The SQL Server 2008 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 2008, 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 2008, 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.

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 2008:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s