SSDN - MS SQL Server - Disable order by clause in the generated SQL

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Yi Chen

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Yi Chen
Date: 21 Jan 2013 04:38 AM

When we use XQuery (DataDirect) to retrieve data from the SQLServer Database, XQuery always adds the order by clause to the generated SQL no matter if we specified a column for sorting. By default the generated SQL will order by the first column of the table. However, what we want is ordering by the physical sequence of the records in the table.

Is there any way to disable the automatically generated order by clause in the SQL?

Thanks!

Ivan Pedruzzi

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Ivan Pedruzzi
Date: 21 Jan 2013 12:40 PM

Hi Yi Chen,

While SQL does not require a specific order when the ORDER BY clause is absent, the XQuery specification requires one.

If you have a primary key on the table, you may try to use it in the order by like in the following example

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

<books>
{
for $books in collection("acme.dbo.books")/books
order by $books/id
return
<book>
<title>{$books/title/text()}</title>
</book>
}
</books>

Yi Chen

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Yi Chen
Date: 21 Jan 2013 08:57 PM

Thanks, Ivan.

Unfortunately, there is no such a primary key or column we can add order by onto it. We have to use the physical order of the rows in the table.

If XQuery standard requires an order, that's fine. But I dont see any reason that why we have to add an order by to the generated SQL.

Is there any configuration can disable the order by in the generated SQL or can we somehow remove the order by in a programetic way?

Thanks again!

Minollo I.

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Minollo I.
Date: 21 Jan 2013 10:13 PM

Just to be clear: there is no such thing as "physical order of rows" in a SQL databse; unless you order the return of a SQL expression, you may end up with 87 differently ordered result in 87 executions of the same expression; the order of the resultset of an unordered SQL expression is entirely in the hands of what the SQL engine decides to do, and can change in time.
Which is exactly why DataDirect XQuery issues ordered SQL expressions: XQuery relies on the document order of the processed inputs, and such order must be stable, i.e. it can't change during the execution of the same query (http://www.w3.org/TR/xquery/#id-document-order).

Minollo I.

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Minollo I.
Date: 21 Jan 2013 10:24 PM

...hit "post" too quickly...

...that said, I'm pretty sure there is an option in DataDirect XQuery to skip the "order by" in the submitted SQL expressions (can't remember the name, but I'm sure Ivan can dig it up); while that might lead to "non standard" behaviors (and potentially unexpected results) for what commented above, it will work just fine in the vast majority of XQueries.

Yi Chen

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Yi Chen
Date: 22 Jan 2013 06:03 AM

Hi Minollo I.

Thanks for your reply.

However, in our database (MS SQL), we always get the same order when querying a table without order-by clause. Also, I believe that a clustered-index will change the physical order of the rows in a table to speed up the query.

Is the option you mentioned "sql-order-by-on-values"? We did try that, but it makes no difference in our case....

Thanks again.

Minollo I.

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Minollo I.
Date: 22 Jan 2013 06:08 AM

Yes, that's the option I had in mind; I can only suggest contacting DataDirect support then.

Ivan Pedruzzi

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Ivan Pedruzzi
Date: 21 Jan 2013 10:50 PM

DataDirect XQuery is an XQuery engine which provides access to relational data. The way the data is rendered has to follow the XQuery specification, which means that data returned by the collection function has to have predictable sorting.

In SQL, the orting is undefined, which means that a SELECT without "ORDER BY" clause is not guarantee to return records in the order in which the have been created.

I am not aware of any switch in DataDirect XQuery that does what you want.

Ivan Pedruzzi
Stylus Studio Team

Ivan Pedruzzi

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Ivan Pedruzzi
Date: 22 Jan 2013 09:23 AM

sql-order-by-on-values does something different:
<<”When set to no, values or expressions on which to sort are always added to the Select list; although this typically decreases performance it is required by some databases.”>>

It turns out that there is a way to disable ordering which is built-in in XQuery adding the following declaration to the query prolog.

declare ordering unordered;

Thanks to Marc Van Cappellen and Minollo for pointing this out.
Hope this help
Ivan Pedruzzi
Stylus Studio Team

Yi Chen

Subject: MS SQL Server - Disable order by clause in the generated SQL
Author: Yi Chen
Date: 24 Jan 2013 11:03 AM

This works! Thanks Ivan.

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.