Using row_number and over in a View generates a warning not to use Order By in a View - will it still work?

Question

I am using the attached sql to create a new view...

The view is created without issue - HOWEVER during creation, I get this warning:

My concern is that the row_number function will not work 100% as expected. I cannot have failure on this query.

So is this warning accurate even in my circumstance or is it just blindly triggering on the "order by" statement and since my "order by" is part of my OVER statement all is well?

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 [dbo].[View_2])

    SELECT     *
    FROM        cte
    WHERE     [rn] = 1

Answer 1

Good day @Scott Bond

I uploaded a full explanation in the following post:

https://ariely.info/Blog/tabid/83/EntryId/356/SSMS-The-ORDER-BY-clause-is-used-only-to-determine-the-rows-that-are-returned-by-the-TOP-clause-in-the-view-definition.aspx

The short answer is that 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 issue is that the SSMS wizard noticed that the query includes the use of "ORDER BY" in the text, but it 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 your case the ORDER BY is used as a parameter to configure the values of the new column using the function ROW_NUMBER.

The alert inform us that we cannot use "ORDER BY" in order to sort the result SET returned by the view. It 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.

Answer 2

The warning informs you that a view is an unordered object. The will return the data you want, but if you run

SELECT * FROM View_2

without an ORDER BY clause, there is no guarantee in which order you get the rows.

Answer 3

I did not get any warning when I created a view using a view inside CTE:

USE [AdventureWorks2019];
GO

CREATE VIEW [dbo].[MyTestView]
AS 
	WITH CTE AS (
		SELECT *, ROW_NUMBER() OVER(PARTITION BY [JobTitle] ORDER BY CAST([EmailPromotion] AS varchar) DESC) AS [RN]
		FROM [HumanResources].[vEmployee]
	)

	SELECT * FROM CTE WHERE [RN] =  1;
GO

SELECT * FROM [dbo].[MyTestView];

Answer 4

try to use a Primary key or unique key

Answer 5

Hi @Scott Bond

As Ronen answered above, it is an alert which I think it is not smart enough. I could reproduce this issue on my side, and I got the same error either.

To avoid this alert, you might create the view in a SQL Editor by clicking the button 'New Query'.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.