TOP 100 PERCENT doesn't work within a VIEW

Way back in 2003, I wrote a blog post titled "Is sorting possible in views?". I came to know that by using TOP 100 PERCENT in our SELECT query we can make use of ORDER BY clause within a VIEW. I tested that out in a SQL Server 2000 machine. But till date I have never used that in any of the real project I have worked on :)

Recently I was trying out in a SQL Server 2005 machine using AdventureWorks database. I thought would make a blog post of that experience so that it might help someone someday!

USE AdventureWorks
GO


--Attempt 1: 
CREATE VIEW vw_OrderedAddress
AS 
SELECT 
AddressID, AddressLine1, 
AddressLine2, City, 
StateProvinceID, PostalCode, 
rowguid, ModifiedDate
FROM
Person.Address
ORDER BY 
AddressID DESC

If we try creating the above view it would throw this error:

Msg 1033, Level 15, State 1, Procedure vw_OrderedAddress, Line 12
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.

--Attempt 2: Using TOP 100 PERCENT
CREATE VIEW vw_OrderedAddress
AS 
SELECT 
TOP 100 PERCENT AddressID, 
AddressLine1, AddressLine2, 
City, StateProvinceID, 
PostalCode, rowguid, ModifiedDate
FROM
Person.Address
ORDER BY 
AddressID DESC
GO

As expected the above script got created without throwing up any error. When we execute the View it would display all records in the table in default ascending order itself. i.e., TOP 100 PERCENT doesn't throw up an error in SQL Server 2005 but the data didn't get sorted as well. Then with the help of search engines I found that this trick is not possible any more.

The possible workarounds are assigning a huge int/bigint value for TOP (which is bigger or equal to the count of records in the table). But how would we decide on a number? I decided to pick the MAX limit of Integer datatype which is 2147483647.

--Attempt 3: Using TOP with MAX integer value
CREATE VIEW vw_OrderedAddress_2
AS 
SELECT 
TOP 2147483647 AddressID, 
AddressLine1, AddressLine2, 
City, StateProvinceID, 
PostalCode, rowguid, ModifiedDate
FROM
Person.Address
ORDER BY 
AddressID DESC
GO

The other way to do it is to assign the value to TOP dynamically like the one shown below:

--Attempt 4: finding the value dynamically
CREATE VIEW vw_OrderedAddress_3
AS 
SELECT 
TOP (SELECT COUNT(AddressID) FROM Person.Address) AddressID, 
AddressLine1, AddressLine2, 
City, StateProvinceID, 
PostalCode, rowguid, ModifiedDate
FROM
Person.Address
ORDER BY 
AddressID DESC
GO

Though I have shown couple of workarounds to use ORDER BY clause within a VIEW I still feel its good to know this information but not the one to use everywhere. The main reason is the ORDERING is not guaranteed. Read the below extract taken from MSDN

"Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly," 

Also even if it works it doesn't make sense to use it! Why? once a View is created we would be making use of it in other queries and during that time what if we need sorting done based on a different column? Won't it be an overhead for the system to actually sort it based on the column mentioned within a View (which is not going to be used ALWAYS) and then again Sort it based on the new column mentioned now?

Rather I would recommend writing just the SELECT query and leaving the ORDER BY clause to be provided at the place where we call this View.

CREATE VIEW vw_OrderedAddress_4
AS 
SELECT 
AddressID, AddressLine1, 
AddressLine2, 
City, StateProvinceID, 
PostalCode, rowguid, ModifiedDate
FROM
Person.Address
GO


SELECT * FROM dbo.[vw_OrderedAddress_4]
ORDER BY PostalCode
GO