Skip to main content

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

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.

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul