The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,649 other followers

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
SET COMPATIBILITY_LEVEL =

(2008 / 2008 R2)
execute sp_dbcmptlevel (deprecated)
(2008 / 2008 R2)
110 SQL Server 2012 90, 100, 110 ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL =

execute 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.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:

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

via: 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.

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

 
%d bloggers like this: