SQL View (with sort) as Linked Table looses sort on Access side - ODBC

[Rx_]

This is a fact. Stumbled across the internet article and posted it on the 3rd post.
Never Ever use an Order By in a SQL Server View.
The reason is explained on the post below.
It is not an ODBC thing. It is a SQL Server thing.

Question #1 Does your database linked table have this same results?
- please list Access / SQL / ODBC versions if yours keeps sort order -
See attachment: A SQL View has 2 sort orders. One the ID_Wells (first Column) and the 2nd sort (descending) on DT_APD_Sub
In SQL Server 2008 r2, it shows up perfectly.
Using vba to create a Linked Table using SQL Server Native Client 11.0
On MS Access, just double-click the linked SQL Server view.

As the attachment shows, it doesn't keep either of the sorted orders.

Question #2 "why did this astound me?"
Keep in mind, a can-opener still baffles me when it is my turn to cook. Me bang can with rock.

The ID_Wells are ascending order and the dates in descending order. On the SQL Server side this view looks perfect!
On the Linked Table side of Access - both the sorted fields act somewhat randomly.

The view is against a table with field of datetime data types.

Since I open a query against this view, I guess that the sorted order will be re-applied. Was just surprised that What-you-see on SQL view is NOT what-you-get on the Linked Table side.

 

[Rx_]

Next Wednesday at Microsoft, we have an Access Day seminar for developers.
I expect some old friends that work at Microsoft Access development to be there.
It is my intent to bring this up since Azure is the New Order.
If anyone has other questions about Access SQL, please send them to me.

 

[Rx_]

Never use a Sort Order when makeing a SQL Server View. The Linked Table in Access will ignore the Sort Order.

It is not ODBC, it is a SQL Server thing.
Here is an article with the step-by-step on what it does and why.

http://blog.sqlauthority.com/2010/0...does-not-work-limitation-of-the-views-part-1/

 

[ButtonMoon]

A view can have no predefined order. Just think of a view as working exactly like a table. Relational tables and views are logical sets, without any ordering to them. This is the principle followed by any implementation of SQL.

Some possible confusion arises because Microsoft SQL Server has a proprietary TOP syntax that uses (mis-uses!) the ORDER BY clause as a way of defining which subset of rows should be returned by the TOP operator. Although this is the one situation where SQL Server will allow you to use ORDER BY in a view, it doesn't alter the basic principle that such a view is logically unordered. The ORDER BY exists only for the purpose of defining the set of rows included in the view. It doesn't define the order of rows returned by queries against the view.

If you want to set the order of any query result, whether the query is against a view or a table, then you need to specify ORDER BY in that query. E.g.: SELECT * FROM your_view ORDER BY x. If you don't specify ORDER BY then the result of any such query is unordered. The server cannot and should not intepret the lack of ORDER BY in a query as implying anything other than a request for an unordered result set.

In general the result should be no different whether you issue the same query from Access or from SQL Server Management Studio - although ordering without using ORDER BY in the query is never guaranteed so you can't be certain of exactly the same ordering in successive executions.

 

[Rx_]

Thanks for the additional discussion for the TOP.
I had noticed the random order and suspected it was my SQL Native Client 11 - ODBC driver. This is totally logical that it would not have any order so that the final consumer can apply the sort order for its use.

There was a point where we migrated the Access DB back end into SQL Server.
This might be a Conversion consideration for anyone else going through the process.

 

[jdraw]

Thanks Button, Haven't worked with SQL Server, but certainly agree with the unordered set generally.
Thanks Rx for highlighting the issue.