Confused with a change in t-sql behavior

I am really curious to know why a simple query like throws an error in SQL Server 2005 and not in SQL Server 2000?

On SQL Server 2005
-
select RowNumber, RowNumber, TextData from dbo.[Tempdb Profile]
Order By RowNumber

error

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'RowNumber'.

You can have two columns with the same name, try to give them an alias and the query will execute withtout any errors.

HTH, jens Suessmeyer.

|||

yeah right

select RowNumber, RowNumber as rownumberx, TextData from dbo.[Tempdb Profile]
Order By 1 --<< referreing to first column

|||Hi,

I wouldn′t rely on the numbered order, what if you have 25 columns, where you want to order for the 16 column, do you really want to count the columns in your select statement. OK you might say, therefore you would name the column.. you are right.. but regarding the other examples you would produce a inconcistency in your own SQL Syntax, so I would prefer better to stick to ONE style, rather numbering OR naming.

Just my 0.02$, Jens Suessmeyer.|||

Hi Jens

This is part of an update to the behaviour of ordering by aliases and can be found in the Upgrade Wizard: http://www.microsoft.com/downloads/details.aspx?familyid=451FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en

I've pasted the relevant documentation below:

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:

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.

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:

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

|||

Hi. I think you found it !

I have the exact same problem with a client with hundreds of ASP pages, with a lot of selects with this order by clause problem.

Is there any workaround for SQL 2005 behave as SQL 2000 and the queries return to work without having to change all the source code?

Thanks in advance.

MG

|||

Just in case this is useful to someone else, you can right click your database, choose Properties, select (left) the "Options" page and you can change the Compatibility Level of your database to whaterever you need (e.g SQL 2000). Pretty good for old code...

Rgds. MG

Marco Gouveia wrote:

Hi. I think you found it !

I have the exact same problem with a client with hundreds of ASP pages, with a lot of selects with this order by clause problem.

Is there any workaround for SQL 2005 behave as SQL 2000 and the queries return to work without having to change all the source code?

Thanks in advance.

MG