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.

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.

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.

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.

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)