ORDER BY on ntext works on some SQL server installs?!?

[kutyafal]

I'm using the SQL Query Analyzer to test a simple query:

SELECT * FROM maintable ORDER BY ntextfield

ntextfield is type ntext. The servers are SQL 2000 developer edition, SQL 2000 Desktop Data Engine and SQL 2000 Enterprise. All three has SP2 installed. Dev. Ed. is installed on Win2000 SP3, DDE is installed on XP, Enterprise is on Win2k SP3 also. Dev. Ed. and DDE throw an error if I try to order by an ntext field. This is also mentioned in the documentation: "Note ntext, text, or image columns cannot be used in an ORDER BY clause."
Enterprise doesn't produce any error and works and orders correctly.

Using the graphic execution plan display of Query Analyzer I see one difference. The two installs that produce the error use Compute Scalar in the process chain:
SELECT -> Compute Scalar -> Sort -> Clustered Index Scan

The install that runs the query fine processes without the Compute Scalar step:
SELECT -> Sort -> Clustered Index Scan

I'd like to know why it works on the Enterprise install? Anybody has seen this? Thanks.

 

[lemosley01]

What error does it return?
I can't see how the execution plan should be different for different versions. If it is not supposed to work, then it shouldn't matter what the execution plan says.
Are you *sure* that the field it does work on is an ntext?

 

[kutyafal]

The error is the standard MS error as described above and in the server manual. It throws the error correctly in the two cases since this shouldn't work. I'm positive the field is ntext and that in the one instance the sorting actually work. It was all tested multiple times. The question is why does it work?

 

[lemosley01]

Ran this against an Enterprise Edition installation that has SP2+Latest security rollup applied, and it failed (as expected). Maybe there was a bug in a patch that you have applied that was fixed, it wouldn't be the first time, but I kind of doubt it.

Can you post the table DDL? Perhaps someone will notice something. You might want to consider calling Microsoft, too.

 

[lemosley01]

BTW - showing the plan or even trying to display the estimated execution plan does not work either. When I try to use display estimated plan it returns the 'cannot use ntext...in order by clause' error.

How exactly are you getting an execution plan against a statement that the server is apparently validating before it really begins execution?