SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

Recently, I was about the limitations of views. I started to make a list and realized that there are many limitations of the views. Let us start with the first well-known limitation.

Order By clause does not work in View. I agree with all of you  who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

Here is the quick example for the same. I have used sample database AdventureWorks for the example.

USE AdventureWorks
GO
-- First Run regular query and observe
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Create view with same T-SQL Script
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
CREATE VIEW vw_ViewLimit1
AS
SELECT
*
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
/*
Above Query will throw following error
Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
The ORDER BY clause is invalid in views, inline functions,
derived tables, subqueries, and common table expressions,
unless TOP or FOR XML is also specified.
*/
-- Create view with same T-SQL Script without ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
CREATE VIEW vw_ViewLimit1
AS
SELECT
*
FROM Sales.SalesOrderDetail
GO
-- Use Order by clause outside of the views
-- Create view with same T-SQL Script without ORDER BY
SELECT *
FROM vw_ViewLimit1
ORDER BY SalesOrderDetailID DESC
GO

If you try to include ORDER BY in View, it will throw the following error

Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Solarwinds

The above error itself explains how one can use ORDER BY in view. It suggests that if we use ORDER BY with TOP, we can surely use ORDER BY. If we want all the rows of the table, we can use TOP with 100 PERCENT. Let us try to modify our view with the usage of TOP 100 PERCENT and ORDER BY. This does not throw any error.

-- Create view with TOP 100 PERECENT and ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
CREATE VIEW vw_ViewLimit1
AS
SELECT TOP
100 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Select from view
SELECT *
FROM vw_ViewLimit1
GO

However, when you observe the resultset, you will notice that table is not ordered DESC, which is specified by SalesOrderDetailID column, as it should be. Let us examine the execution plan. You will not notice that there is no SORT operation at all.

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1 ViewLimit1

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1 ViewLimit2

I have heard many people talking about workaround, where they use some other number less than 100 in the TOP clause. Let us do a small test with 99.99 PERCENT and see the type of result we get.

-- Create view with TOP 99.99  PERECENT and ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
CREATE VIEW vw_ViewLimit1
AS
SELECT TOP
99.99 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Select from view
SELECT *
FROM vw_ViewLimit1
GO

Now let us check the result.We can clearly see that the result is Ordered by Column specified in the view.

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1 ViewLimit3

However, as we are using TOP and 99.99, there is very little chance that we may not get all the rows from the table. Let us check the count of the rows in original table and Views.

-- Match the counts
SELECT COUNT(*) ViewCount
FROM vw_ViewLimit1
GO
SELECT COUNT(*) OriginalCount
FROM Sales.SalesOrderDetail
GO

From the count, it is clear that View has not returned all the rows because of the TOP specified. If table was a small table with less than 10,000 rows, this view might have not missed any rows, but in this case, where there are lots of rows, the View has missed rows.

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1 ViewLimit4

Summary: It is not advisable to use ORDER BY in Views. Use ORDER BY outside the views. In fact, the correct design will imply the same. If you use TOP along with Views, there is a good chance that View will not return all the rows of the table or will ignore ORDER BY completely.

Please share your experience over here as comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Computed Columns – Index and Performance
Next Post
SQLAuthority News – Feedback Received for Virtual Tech Days Sessions on Spatial Database

Related Posts

63 Comments. Leave new

  • Rohit Bhardwaj
    October 29, 2013 5:55 pm

    Hi Pinal
    is there any way to find the number of columns in a table

    Reply
  • Then why does the SQL 2012 SMSS View Wizard have support for TOP in the View Properties windows???

    Reply
  • Just use select top (select count(*) from answers) FirstName, LastName from User Order by FirstName + ‘ ‘ + LastName – works and also honors the sorting.

    Reply
  • Hey people!! Few days ago i came across a strange problem with the Order By , While creating a new table i used
    Select – Into – From and Order By (column name)
    and When i open that table see tables are not arranged accordingly. I reverified it multiple times to make sure i am doing the ryt thing. one more thing i would like to add is till the time i dont use INTO , i can see the desired result but as soon as i create new table , i see there is no Order for tht coulumn. Plz help me !!!
    Thanks in Advance
    Sample code :
    Select
    a.Name ,
    a.class ,
    a.Roll No,
    b.Marks
    From Name As A inner join Marks as B
    on a.Name=b.name
    order b.marks

    Reply
  • excellent, you saved the day for me!

    Reply
  • No . I think it is possible ordering in View .

    I create a query for that

    Create VIEW Bill_V
    AS
    SELECT C.FName
    ,C.LNme
    ,P.ProductDesc
    ,B.DateOfBooking
    ,P.Price
    ,B.QTY
    ,(B.QTY*P.Price) AS TotalAmountPayable,
    ROW_NUMBER() OVER (ORDER BY P.ProductDesc) AS RowNum
    FROM BOOKING B
    INNER JOIN PRODUCTS P
    ON B.ProductID=P.ProductID
    INNER JOIN Customer C
    ON B.CustID=C.CustID;

    Reply
  • I am using Order By in the inner query with ROW_NUMBER() and then using this inner query result to get some info in select * from (inner query result) where condition;

    My inner query is working fine but when I am running the full query then I am getting below error.

    The ORDER BY clause is invalid in views, inline functions, derived tables, sub query, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    My query looks like :

    SELECT [id] , , [created_at]
    FROM ( SELECT [id] , , [created_at] , (ROW_NUMBER() OVER ( ORDER BY created_at DESC)) AS Row
    FROM [Code_tbl]
    WHERE [created_at] BETWEEN ‘2009-11-17 00:00:01’ AND ‘2010-11-17 23:59:59’ ORDER By created_at ASC
    ) Rows
    WHERE Row BETWEEN 10 AND 20;

    Can someone help me with this.?

    Reply
  • I have seen your example , that are very good example but i have tried 99.9999 percent on the view , i have got the full result and i have got the same count of both (view and table)

    Reply
  • Very good post pinal.. Thank you,it solved my problem

    Reply
  • It’s very much helpful in understanding the topic order by clause against the view.
    As always, it gives an amazing experience.
    Thank Pinal Sir.

    Reply

Leave a Reply

Menu