How can you force SQL Server 2008 R2 to accept an ORDER BY on a column that is not part of a table?

Question

While migrating a bunch of applications we inherited from SQL Server 2000 to SQL Server 2008 R2, I came across an ORDER BY style that now fail.

The queries are generated by an kind of SQL generation layer (if it were any better, would fix the layer ASAP, but it is bad). If possible I want to postpone fixing the SQL generation layer until I have built a decent testing harness for it.

Small example (it does not matter what kind of tables you use: regular or local/global temporary):

select
  #IDTABLE.*,
  #NAMETABLE.NAME as NAME -- column alias is mandatory for SQL Server 2000 to succeed
from #IDTABLE
left join #NAMETABLE on #IDTABLE.ID = #NAMETABLE.ID
order by #IDTABLE.NAME

The culprit is that #IDTABLE has no NAME column. SQL Server 2000 executes this, and takes NAME which refers to #NAMETABLE.NAME. SQL Server 2008 R2 fails with "Invalid column name 'NAME'."

A couple of questions:

• is it possible to force SQL Server 2008 R2 to accept this kind of queries and perform the SQL Server 2000 behaviour (so we can fix the SQL generation layer, and perform regression on it)?
• since what version of SQL Server is this kind of queries failing?
• why would SQL Server 2000 happily accept this kind of queries?

In case you want to see the behaviour, here is a full example:

create table #IDTABLE (
    ID Integer
)

create table #NAMETABLE (
    ID Integer,
    NAME VarChar(50)
)

insert into #IDTABLE values (0)
insert into #IDTABLE values (1)
insert into #IDTABLE values (2)

insert into #NAMETABLE values (0, 'FOO')
insert into #NAMETABLE values (1, 'BAR')
insert into #NAMETABLE values (2, 'SNAFU')

select *
from #IDTABLE

select *
from #NAMETABLE

select
  #IDTABLE.*,
  #NAMETABLE.NAME as NAME -- NAME alias is mandatory for SQL Server 2000 to succeed
from #IDTABLE
left join #NAMETABLE on #IDTABLE.ID = #NAMETABLE.ID
order by #IDTABLE.NAME

drop table #IDTABLE
drop table #NAMETABLE

And the output by OSQL:

(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
 ID
 -----------
           0
           1
           2

(3 rows affected)
 ID          NAME
 ----------- --------------------------------------------------
           0 FOO
           1 BAR
           2 SNAFU

(3 rows affected)
 ID          NAME
 ----------- --------------------------------------------------
           1 BAR
           0 FOO
           2 SNAFU

Answer 1

You can hack this to "work" temporarily:

ALTER DATABASE [your database] SET COMPATIBILITY_LEVEL = 80;

However you better fix your generation layer to produce valid SQL before you upgrade to SQL Server 2012, since that compat level is no longer valid there, and in 90 and above your code breaks. Never mind that setting the database to 80 may produce other undesired side effects - I talk about them here:

SQL Server 2000 to 2008 Migration - ORDER BY Issue when using DISTINCT

(As an aside, if ##IDTABLE doesn't have a NAME column, why on earth is it generating ##IDTABLE.NAME in the first place?)