Monday, October 03, 2011

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

2 comments:

Aaron Bertrand said...

This is wrong. Even though you can get away with using TOP 100 PERCENT or TOP (n) in a view, the result is *ONLY* guaranteed to be ordered if you also include an ORDER BY when you select from the view. I realize this is what you recommend people do anyway, but showing the solutions with TOP/ORDER BY in a view certainly imply - and help to perpetuate the unfortunate myth - that SQL Server will obey that ordering when selecting from the view without ORDER BY. This may be observed a lot of the time but it is NOT guaranteed.

Vadivel said...

True Aaron. SQL Server makes NO guarantee to the actual order of the result in the view. Ordering the subquery/view itself will make no reliable difference to the order of the results. May be I should have pointed it out too explicitly to avoid any confusion. Thanks for pointing it out. I have updated the post now.