Ordered By Warning in Sql Server

[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.

 

[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.