Ordered By Warning in Sql Server (1 Viewer)

[KitaYama]

Can anyone explain in simple words what does this warning mean?

I have a complicated query in Access that works fine and returns the expected results.
If I copy its sql to a view in SSMS, I receive the above message when I try to save the view.
Clicking OK bypasses the warning and the view saves. The result is correct, but it's not ordered based on Delivery field.

Here's a simplified version of the sql (which still returns the same error)

SELECT TOP (100) PERCENT
    o.Rec,
    p.DrawingNo,
    p.DrawingName,
    o.Delivery,
    o.SetName,
    o.OrderPK
FROM dbo.tblOrdersPr AS op
    INNER JOIN dbo.tblOrders AS o
        ON op.OrderFK = o.OrderPK
    INNER JOIN dbo.tblProducts AS p
        ON o.OrderProductFK = p.ProductPK
WHERE (o.Delivery > '2024/03/14 12:00:00')
      AND (o.SetName IS NULL)
      AND (o.Deleted = 0)
ORDER BY o.Delivery

From Warning:
"The ORDER BY clause does not guarantee ordered results when the view is queried, Unless ORDER BY is also specified in the query itself"
If I understand the error correctly, Delivery is already included in SELECT.

Note 1: The first TOP (100) PERCENT is added by SSMS
Note 2: If I ran the same sql in a query window in SSMS, The warning doesn't appear, the result is correct and in order.

Thanks

 

[Minty]

The warning is always prevalent when saving a view with an Order clause that doesn't include a TOP method of controlling the records returned. It is essentially because a view is treated like a table - it has no intrinsic order, and is designed to be queried.

So to save a View with an order by it has have a TOP clause for SQL Server to make it work.

There are cleverer people than me discussing it here:

Why TOP is required in ORDER BY clause? – SQLServerCentral Forums

Why TOP is required in ORDER BY clause? Forum – Learn more on SQLServerCentral

www.sqlservercentral.com

 

[KitaYama]

There are cleverer people than me discussing it here:

The problem is when clever people start talking, I don't understand what they mean.

from your link :

If you need the data ordered, the order by must be applied to the select statement that queries from the view.

Does it mean I have to remove the Order By From the view, import a linked view to Access, then use a query in Access to sort the linked view?

Thanks.

 

[Minty]

The problem is when clever people start talking, I don't understand what they mean.

from your link :

Does it mean I have to remove the Order By From the view, import a linked view to Access, then use a query in Access to sort the data?

Thanks.

Generally speaking that is the preferred method.
Or send a ordered query as a pass through query, if you don't need to edit it?

 

[ebs17]

TOP (100) PERCENT

This non-selection irritates me.
Without a reduced selection, sorting makes no functional sense.

As far as I know, a saved view is loaded unsorted into the Access frontend and only sorted there if necessary.

 

[KitaYama]

Generally speaking that is the preferred method.

Thanks. Done.
But still the main question remains. View design window contains two columns. Sort By & Order of Sort.
It means there are situations where using OrderBy in view's sql is possible. I hoped someone may know when these columns can be used.

This non-selection irritates me.
Without a reduced selection, sorting makes no functional sense.

I don't know why SMSS adds this. As soon as I add an ORDER BY column and try to save the view it's added.
If there's no ORDER BY, it's not added.

While I removed Order By and used an Access query to sort the result, I'm still waiting to see if anyone can decipher this section of the warning:
Unless ORDER BY is also specified in the query itself"
It seems that if ORDER BY field is included in SELECT section of sql, there should be no problem.

Thanks.

 

[Minty]

Be aware that I have seen SMSS or possibly SSMA add the following:

SELECT TOP 29856584515545(silly big number)  blah blah
FROM ....

Where the silly big number is the the maximum value of a Int or Big Int in SQL.
When it does this it's a lot less efficient than

TOP 100 Percent

Just in case you ever come across it.
It made the queries run significantly slower.

 

[KitaYama]

Just in case you ever come across it.
It made the queries run significantly slower.

Thanks for the warning.

 

[Pat Hartman]

This non-selection irritates me.

I don't understand why the Top predicate is there at all. But the joins and the where clause limits the selection and should not require Top to limit it further unless a large number of rows would be selected and the user only needs to see a few of them.

I've never seen this warning before but then I don't save my views with order by clauses. I don't remember why. Probably because I figured out the sort was not retained some time long ago and just forgot.

 

[KitaYama]

I don't understand why the Top predicate is there at all. But the joins and the where clause limits the selection and should not require Top to limit it further unless a large number of rows would be selected and the user only needs to see a few of them.

I've never seen this warning before but then I don't save my views with order by clauses. I don't remember why. Probably because I figured out the sort was not retained some time long ago and just forgot.

I personally don't care why the top 100 percent is added there. It may have had some reasons.
To me the greater problem is the message.
"UNLESS" is a joining word. At the left of "Unless", there's always a negative sentence and at its right side, there's a solution for bypassing that impossible task.

You can't go to America unless you ask for a visa from the embassy in your country.

I only wish the right side of the "Unless" in the warning was something logical.

 

[Pat Hartman]

It is logical. Views act much like tables.

In relational databases tables AND queries are unordered sets. That means that the sequence of the records returned to you is not defined UNLESS you include an Order By clause in your query. Think of it as if you were pulling marbles out of a bag. You reach in and get one, then you get the second, then you get the third, etc. The query engine retrieves data using whatever method is most convenient to the engine so it doesn't necessarily use an index. It pulls data from the tables probably in physical order but SQL Server is multi-threaded so multiple threads might be pulling data from different parts of the table at once and merging them together. Access (ACE and Jet) work differently but you can still see the "out of order" resultset. I can explain how to make it happen if you really want to do it. Jet and ACE fool us because every time you C&R, every table is rewritten into primary key sequence so when you open a table in DS view, it is unordered but it is single threaded and pulling data in physical order so it "looks like" the data is always sorted. Believe me when I tell you it is NOT. The ONLY time you can ensure records will be returned in a totally predictable order is if they are sorted on a UNIQUE field or group of fields. If you do not always sort your recordsets, you will get burned whenever order is relevant.

Since most of the time it is your final query that specifies the Order By, it is inefficient to embed an Order By in the view so SQL Server ignores it. This is exactly the same thing that Access does for your reports. If you bind a report to a query with an order by, the report ignores the query's Order By clause because it expects you to use the Order By property of the Report itself to control the sequence of the detail records.

It's the part about the EXCEPT when a Top predicate is included in the View that I didn't know and that also makes sense. It makes sense because the Order By controls the TOP. If you have a thousand rows and you sort them descending on price, the costliest items show up at the top of the list so Top 10 selects the 10 most expensive items.