How can you force SQL Server 2008 R2 to accept an ORDER BY on a column that is not part of a table? – Stack Overflow
Posted by jpluimers on 2014/02/04
I’m sure there are many organizations that only upgrade things until they absolutely have to (i.e. long after mainstream support has ended, often even after extended support has ended). This was from last year: upgrading away from SQL Server 2000 just before extended support ended. 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 failed. The queries are generated by an kind of SQL generation layer, so not easy to change. the main questions were:
- 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)?
- why would SQL Server 2000 happily accept this kind of queries?
First two possible fixes, then the full stack overflow question I posted about the migration.
Aaron Bertrand very quickly posted two fixes, which I paraphrased and extended.
Answer 2:
Fix the generator.
This was the shortest answer, but took more time. It is also what I did, it took 2 days, works for all SQL Server versions so is future proof.
It doesn’t have all the drawbacks of answer 1.
Answer 1:
Set the compatibility_level of the specific database to 80 (so it acts to be like SQL Server 2000).
Since SQL Server 2008 R2 still supports the SQL Server 2000 compatibility level, you can do this:
ALTER DATABASE [your database] SET COMPATIBILITY_LEVEL = 80;
This is a kind of “last resort” measurement and has drawbacks.
First if all: it is not future proof. Various versions of SQL Server have various compatibility levels. Over the versions there have been various ways of setting them.
A table trying to explain this:
| Level | Name | Also supports | Set it using (links are for specific SQL Server versions) |
|---|---|---|---|
| 60 | SQL Server 6.0 | ? | ? |
| 65 | SQL Server 6.5 | ? | ? |
| 70 | SQL Server 7.0 | 60, 65, 70 | execute sp_dbcmptlevel |
| 80 | SQL Server 2000 | 60, 65, 70, 80 | execute sp_dbcmptlevel |
| 90 | SQL Server 2005 | 60, 65, 70, 80, 90 | execute sp_dbcmptlevel |
| 100 | SQL Server 2008 SQL Server 2008 R2 |
80, 90, 100 | ALTER DATABASE database_name(2008 / 2008 R2) execute sp_dbcmptlevel (deprecated) (2008 / 2008 R2) |
| 110 | SQL Server 2012 | 90, 100, 110 | ALTER DATABASE database_nameexecute sp_dbcmptlevel (deprecated) |
Note there is no COMPATIBILITY_LEVEL 105 for SQL Server 2008 R2, probably because the basic engine is the same as SQL Server 2008.
setting the COMPATIBILITY_LEVEL to 80 may produce other undesired side effects; Aaron talks about them at SQL Server 2000 to 2008 Migration – ORDER BY Issue when using DISTINCT
Now the complete question with sample code, and the full answer by Aaron:
Question:
How can you force SQL Server 2008 R2 to accept an ORDER BY on a column that is not part of a table?
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:
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.NAMEThe culprit is that
#IDTABLEhas noNAMEcolumn. SQL Server 2000 executes this, and takesNAMEwhich 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 #NAMETABLEAnd 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:
Aaron:
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?)
Jeroen:
It generates it because the constructor of the generator has a table an order parameter, and while emiting the SQL it always appends it like table.order. I need to alter that mine-field so it understands relations properly. – Jeroen Wiert Pluimers Feb 28 at 16:19
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment