SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY

Today we will go over a very simple, but interesting subject. The following error is quite common if you use ORDER BY while creating any view:

Msg 1033, Level 15, State 1, Procedure something, 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 error also explains the solution for the same – use of TOP. I have seen developers and DBAs using TOP very causally when they have to use the ORDER BY clause. Theoretically, there is no need of ORDER BY in the view at all. All the ordering should be done outside the view and view should just have the SELECT statement in it.

It was quite common that to save this extra typing by including ordering inside of the view. At several instances developers want a complete resultset and for the same they include TOP 100 PERCENT along with ORDER BY, assuming that this will simulate the SELECT statement with ORDER BY.

In SQL Server 2008, this behavior is corrected by ignoring the ORDER BY clause when TOP 100% is used. Let us see a quick script for the same.

USE AdventureWorks
GO

-- SELECT from Table Without TOP or ORDER BY
SELECT *
FROM HumanResources.Shift
GO
-- SELECT from Table With TOP and ORDER BY DESC
SELECT TOP 100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Create View with same as above statement
IF EXISTS (SELECT * FROM sys.views
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vwShift]'))
DROP VIEW [dbo].[vwShift] GO
CREATE VIEW vwShift
AS
SELECT TOP 100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Select From View
SELECT *
FROM vwShift
GO


Let us see the result of above script here. It is quite clear from the result that in the view, ORDER BY clause is ignored and result is returned as similar as that of a regular SELECT statement without ORDER BY.

Note that SQL Server 2008 view with ORDER BY clause does not throw an error; moreover, it does not acknowledge the presence of it as well.

If you have any similar ideas as the above one, please leave a comment here.

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