0

I have following view

CREATE VIEW ...
    AS SELECT        TOP (100) PERCENT ...
FROM            ...
INNER JOIN ...
ON ...
WHERE    ...
ORDER BY datetime2 DESC

I also have a stored procedure with a SELECT TOP(1) ... FROM ... WHERE ... without an 'ORDER BY' declaration. (i am also fixing it). The programm, which is executing this usp, also writes a lot of informations in the dependent table. So the request can be executed, while the view got new informations.

First of, we executed some 'SELECT ... WHERE...' statements on the view, which ended unsorted. Why is it unsorted, the view should sort it by the datetime2-Column.

I am expecting mixed results by the 'SELECT TOP(1)' statement without the 'ORDER BY' append. But actually there are less wrong results as expected and they are on a specific period. 2 Days in April, 2 Days in November. The datas in the view are also mixed, there shouldn't be a statistical reason, why the result gives me some datas more often than other one.

I couldn't find any connection between the datas, also not in the environment. Is it just an unlucky situation? A 'trap' that lets me think, there is a connection?

Is there a way to reproduce the 'failure'. (actually it's a correct behaviour from the system)?

I hope you got enough information to help me out! If there are some questions, of course I'll answer them.

EDIT same day - 09:32 UTC: the important part of the usp is, that it insert into a table the result of the `SELECT TOP(1) ... FROM ... WHERE ... '. There are no conditions around.

Björn Ammon
  • 23
  • 6
  • 3
    Why do you have a `VIEW` with a `ORDER BY`? The ordering should be done *outside* of the `VIEW`, not inside it. – Thom A Nov 30 '21 at 09:24
  • 1
    As for your question, I'm not sure what it is? You seem to know that your procedure (which we don't have the definition for) is poorly written, so why not just fix it? – Thom A Nov 30 '21 at 09:25
  • @Larnu i didn't wrote the view, i am just trying to understand the problem behind this declaration. And have to fix it :P EDIT: yeah, if we just fix it, there shouldn't be anomalies anymore, but it would be nice, if there is the possibility to understand and reproduce the problem. – Björn Ammon Nov 30 '21 at 09:27
  • 3
    Reproduce what problem? Are you talking about your statement *"Why is it unsorted, the view should sort it by the datetime2-Column."*? Wrong, a `VIEW` isn't sorted; the `TOP (100)` with an `ORDER BY` is an undocumented kludge that has no guarantee of working. Order your data outside of the `VIEW`. – Thom A Nov 30 '21 at 09:31
  • Ok thank you for the Information. It's more about the anomaly, why we get some random data on 2 following days in april and 3 following days in november. It looks like a pattern, although it's obviously the `SELECT TOP(1) ... FROM ... WHERE ...` without `ORDER BY ...`. Is there a way to reproduce this? – Björn Ammon Nov 30 '21 at 09:37
  • If you have a `SELECT TOP(1)` without an `ORDER BY` then the row you get will be *completely* arbitrary. There is no guarantee what row you get, and thus no way to reproduce it. You *might* get the same row 99/100 times when you run the procedure, you might get 100 different ones. Of course, the fix is to add an `ORDER BY` (and remove the `TOP` and `ORDER BY` from your `VIEW`). – Thom A Nov 30 '21 at 09:41
  • 2
    The optimizer is smart enough these days to know that `TOP 100 PERCENT` can be computed without having to perform any sorting. Views are *inherently* unsorted (as are tables). – Damien_The_Unbeliever Nov 30 '21 at 09:41
  • Ok Thank you Larnu and Damien_The_Unbeliever. We tried to get the same wrong result as the procedure, but it was always correct. Many times. Think we were just unlucky. – Björn Ammon Nov 30 '21 at 09:54
  • I wouldn't say you were "unlucky"; the procedure is poorly written. Like I said, fix the procedure, and then you can't have unexpected behaviour. – Thom A Nov 30 '21 at 10:25
  • @Björn_Ammon you say "wrong result"... This is not true. The result is not wrong. But the result is not the one you attend... SQL Server is right, but your way to write and use query is wrong... – SQLpro Nov 30 '21 at 10:41
  • @Charlieface yes ty very much! very datailled answer! – Björn Ammon Dec 01 '21 at 09:25
  • @Charlieface ty, didn't see it at the first time :D – Björn Ammon Dec 02 '21 at 10:16

1 Answers1

1

ORDER BY is a "cosmectic" SQL Clause that only shows an order bettween rows after the complete SQL tratement of your query. Because the engine has no "order" to operate the different algorithms used in a query plan, rows are randomly placed in the result set.

View does not excepts this rule because, at every moment, you can add a ORDER BY clause on a SELECT from view(s).

So ORDER BY, whatever the trick used to set in inside the query will have no effect on the final SQL command : SELECT, INSERT, MERGE, UPDATE or DELETE.

The is absolutly no default order in a query ever when views or table's UDF are used...

SQLpro
  • 3,994
  • 1
  • 6
  • 14