SQL Server Performance Forum – Threads Archive

ORDER BY on ntext works on some 2000 servers ?!?

Tested on 3 different installs of SQL Server 2000 and it gives error 420 (correctly) on two and runs and sorts fine on the third. Same versions, same service packs(2). Why is the difference? Isn’t it not supposed to work by default? So why does it work on one install? Thanks.

---

You will need to provide some details before I can help out. What are you testing, and can you tell us more about your servers and databases?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

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 If this is what causes the issue I’d like to know why and how can it be modified? Thanks again.

---

I can’t explain why Enterprise Edition does not produce the same error you see with the other two versions of SQL Server. While I have not tried what you are doing, based on my research, the Enterprise Edition should work just like the other editions when it comes to using ORDER BY. Microsoft claims that ORDER by can’t be used for text or ntext datatypes, just as you have already mentioned above, I don’t know of any exceptions. If you goal is to use the ORDER BY to sort the data in the ntext datatype columns, do you really need to use the ntext datatype, can you use varchar instead? Varchar allows up to 8,000 or so characters in it, and it can be easily sorted using ORDER BY. Is this a possiblity for your particular application?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

Is your ntextfield part of the clustered index? This might explain why the sort is working even though no error is being reported.
"How do you expect to beat me when I am forever?"

---

Good question. How do I tell and how do I add it if it’s needed? To explain, I’m not the admin for the Enterprise Server and have no say in the configuration. I’d like to set up the development environment so it behaves the same as production. Something is obviously different in the configuration of the Enterprise Server from our development servers which are all default installs. Any clue on the "Compute Scalar" issue? Thanks again for your help.

---

To see if an index is clustered, and to add, delete, or modify indexes, one easy option is to use Enterprise Manager. In Enterprise Manager, go to the table in question, right-click, then choose "All Tasks" then choose "Manage Indexes".
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

Thanks. I’ve checked and it’s the same for all three installs and does not seem to make a difference. Puzzling…

---

]]>