פבר18
Written by:
ronen ariely
18/02/2023 05:38 
Background
Today I encounter a question at Microsoft QnA forums, regarding an error appears when creating a view. Unfortunately the original poster (OP) of the question, did not provided the full information to reproduce the issue. Reading the question, it was not clear to me at first that the user used the SSMS wizard in order to create the view.
First, I would like to HIGHLY emphasize that when you ask a question then you must provide all the information to reproduce the issue. Please include relevant screenshots, code, description and anything you can.
Secondly, I would like to HIGHLY recommend NOT to base your work on graphical wizards! From time to time a wizard can save coding time, but do not use it when you do not know coding! A wizard should be a tool and not a replacement for coding knowledge. This is true to any AI wizards including for example using ChatGPT and such.
OK, let's go back to the question
The issue
Full error message
Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.
Reproduce the scenario
This issue raises when we are using the SSMS Wizard to crate a new view, and the query include the use of ORDER BY clause.
1. Open SSMS Object Explore windows and navigate to the database where you want to create a VIEW.
2. Right click on Views and select the option "New View..."
By default the wizard ask you to select tables and views which you want to use in your new VIEW.
3. Close the window
4. In the query section in the izard windows paste the query which the OP tied to use
with CTE as (
SELECT *, row_number() OVER (PARTITION BY Field_1, Field_2
ORDER BY cast([Field_1] AS float), cast([Field_2] AS float) desc) AS [rn]
FROM T
)
SELECT *
FROM cte
WHERE [rn] = 1
When you click outside the query editor, you can notice that the wizard alert you that this query cannot be presented in the wizard grid. The reason is that this query uses a bit more complex and a different format from what the wizard "understand" (code is not fully supported by the wizard).
5. Click "Ignore" to continue
6. Save the new VIEW: Click on File -> Save As...
And the alert window that started this discussion will raise!
7. Click OK and continue read the explanation
Explanation
This is not an error but an alert (information only), and it is NOT coming from the SQL Server, but an alert raises by the SQL Server Management studio.
If you succeed to create the view (as happens in this case), then there is no issue.
The source of the alert is that the SSMS wizard "noticed" that the query includes the use of "ORDER BY" in the text, but the wizard did not "understood" what was it used for or where in the query.
There is not need to be concerned that the ROW_NUMBER function will not work.
The alert simply inform us about a potential very common case that people use "ORDER BY" in the views in order to make the VIEW return a set of rows in specific order. This cannot be done and will be ignored by the VIEW, since a VIEW returns the rows in an un-ordered SET.
In our case the "ORDER BY" is used as a parameter to configure the values of the new column using the function ROW_NUMBER. The alert informs us that we cannot use "ORDER BY" in order to sort the result SET returned by the view. No problem as we did not used it for this. The wizard also provides a sample case when "ORDER BY" can be used in a view, which is when we want to get specific set of rows using TOP clause.