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 (http://blog.SQLAuthority.com)

About these ads

65 thoughts on “SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

  1. You left out the reason why ORDER BY isn’t allowed in a view, subquery, CTE, etc.

    The real reason why you can’t ORDER any of these things is because they are all sets. A set is an unordered collection of tuples. The reason that using ORDER BY with TOP works at all is because the use of TOP forces SQL Server to perform a cursor operation over the result set. Once you have a cursor, you can have an explicit order.

    Ben-Gan discusses this in T-SQL Querying and I suspect it’s buried somewhere in BOL as well.

    As an aside, the SELECT TOP x trick is scheduled to be deprecated in a future version of SQL Server, so it would be best to not to use it at all.

    Like

  2. Hey Pinal,
    Can you explain about Table spool/eager spool
    What is Table spool
    When the optimizer chooses table spool
    Its advantages and Disadvantages on performance

    Your examples will be easy to understand .
    Please provide info abt that

    thanks
    SANTOSH

    Like

  3. Hi Pinal,

    SQL Server honors the order by clause when we give it the number of rows to return

    for e.g. we can use a huge number in the top clause

    SELECT TOP 99999999999999
    Column1,
    Column2
    FROM
    dbo.Table
    Order by
    Column1

    Like

    • Thanks Harsu! I usually don’t have a problem with an unsorted view, but the need just came up. I had to laugh when I saw it sorted when I ran it in design mode, but not the saved view. I don’t know what that’s all about.

      Like

  4. Let me start out by thanking you for yet another good, useful and very practical article.
    I have heard about the TOP 100 PERCENT * workaround (and have also seen a couple of test views compile successfully), but always had the question that Tejas had – why does that not work as expected?
    Jeremiah’s reasoning does clear things out a bit, however, currently, it looks like SQL is simply ignoring the ORDER BY clause when it encounters the TOP x clause – and with that being the case, it should be deprecated.

    Like

  5. Thanks Pinal for this post. When we use View in FROM clause, the result is not guaranteed to be in ORDER as view is a SET, as per ANSI standard. TSQL follows that.

    The ORDER BY clause in query is just for aiding TOP clause as TOP clause does not have its own ORDER BY clause and depends on the ORDER BY clause of the whole query. It will be helpful in SELECT query. But, of no use in a view.

    Like

  6. Good and useful post.

    My understaning of the use of ORDER BY in views was only to make the TOP clause work and that SQL Server makes no garantee to the actual order of the result in the view. It only garentees that the correct records are in the TOP clause.

    I have only found this to actually work with a TOP [number], but not with a TOP PERCENT. I never knew exactly why, but the discussion from this post gave me a little more clarity.

    Thank you, Everyone!

    Like

  7. To rely on the ORDER BY clause in a view definition is a same kind of mistake like to rely on the physical order of records inserted into an empty table.

    Even when it could sound unbelievable (because many commands you’ve executed in the past were ordered as you supposed) SQL SELECT simply ignores the physical order of records in table/view and the only clause which can ensure proper records order on output is the ORDER BY in your SELECT statement.

    Like

  8. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts – August 2010 Journey to SQL Authority with Pinal Dave

  9. Just out of curiosity, I tried the following code:

    — Create view with TOP 99.9999 PERCENT 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.9999 PERCENT *
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID DESC
    GO
    — Select from view
    SELECT *
    FROM vw_ViewLimit1
    GO

    You get all 121,317 rows from the table returned and the order is descending. It worked!!! This appears to be a precision issue in the expression of the TOP (expression) [PERCENT] clause. If you expand the scale out far enough, all rows in the table can be returned. I’m not recommending this technique, but I found it interesting that it could be made to work.

    Like

  10. Views are unordered sets (or bags depending on your specific definitions). They should be used as unordered sets.

    Yes, you can order them using the TOP workaround.

    But if you are using a View as something it isn’t intended to be, it makes me wonder what other design flaws are built into the system you are building or maintaining.

    My primary question to you would be: WHY are you attempting to order data within a View?

    The usual answer is: to support reporting. If this is the case, there are two options:

    Order the data within a stored procedure and bind your report to the stored procedure.

    -or-

    Order the data within the report.

    Ordering large sets of data within SQL Server forces the data to be stored and sorted within tempdb… a huge performance bottleneck, especially on a large, multiuser system

    Using a stored procedure means you are using the features of the application (SQL Server) as they are intended to be used and is supported by Microsoft.

    Using a view, then sorting the results in the client application means you are using the strengths of each of the components within your system effectively and efficiently.

    Just because you can do something (with a workaround) doesn’t mean you should do something.

    The most successful systems are those that use the strengths of the components that make up that system. Using the components in a sub-optimal way will result in a sub-optimal system

    Like

  11. I don’t get it. Why is ORDER BY without TOP a limitation? Because you get an error? Or because the performance is bad (according to you)? If you’re selecting ALL (or almost all with 99.99%) of the records from a table, how can you expect good performance? You’re forcing the QP to read all the pages of the clustered index. Of course the perrformance will be poor, but it’s a view. It means that some other code will want to use it as a “table”, by trying to SELECT (and maybe INSERT/UPDATE/DELETE if the definition of it allows to). Views are used with WHERE/GROUP BY/HAVING and other clauses, let alone participate in JOINs of all flavors. How can you assign an attribute of “bad” to an object by examining it the way you did? I always used in the past an “ordered” view only when building a BCP…OUT process, until SSIS came along. Now, if I do need to use BCP, I use QUERYOUT clause, where my options are unlimited.

    Like

  12. Pingback: SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8 Journey to SQL Authority with Pinal Dave

  13. hw can we use order by wid view????
    whn i was givin d query in sql to make view thn i m getting error tht order by nt valid n cant b use wid view…..

    Like

  14. hi

    If you want to use order by in view without using TOP clause use view with Schema

    as

    i have view from two table

    CREATE VIEW vw_saggi
    AS

    SELECT dbo.Employees.FirstName, dbo.newt.LastName, dbo.Employees.EmployeeID
    FROM dbo.First_table CROSS JOIN dbo.Second_table order by FirstName desc;

    —–sort view using order by clause———————-

    SELECT * from dbo.vw_saggi

    order by FirstName desc;
    ———————————————————

    try this

    Satwinder Saggi

    DBA

    Like

  15. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  16. HI, i found your article very nice, and my friend and i suggest to create the view like this:

    SELECT TOP (SELECT COUNT(*) FROM Sales.SalesOrderDetail) *
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID DESC

    And like that the result shows 100 percent of the rows.

    Regards :)

    Like

  17. Thank you! I couldn’t figure out what I was doing wrong when my view sorted properly if I executed it in the “modify” window and not when when it was executed. You saved me a ton of time. I’ll sort in the stored procedure.

    Like

  18. Thanks for the article but I can’t agree that a bug in SQL can be treated as “well you shouldn’t do that anyway”. This IS a bug and has bee nresolved in a 2008 SP1. It also used to work fine in SQL 2000. There are many times you should be able to do this, for instance a view-on-a-view with an outer join and also with some third party reporting packages that require the data from a single view.
    If it is not allowed then the view should NOT syntax check o.k. and be creatable.

    Like

  19. Pingback: SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer « SQL Server Journey with SQL Authority

  20. Thanks,That’s great. i had same problem today. my view had a TOP 35000 in may Select statement. then it was working like order by clause. we have to omit TOP keyword from Views.
    And this the first time I realize this issue.
    SELECT *
    FROM vw_AvailableLocations
    WHERE UPC= ‘20612351’ AND Series= ‘00245’ — No returns

    View Querry
    SELECT Top 35000 INV.Item as Item ,INV.BinLoc as BinLoc,INV.Series as Supplier ,ISNULL((INV.Qty-ISNULL(ALLO.RQty,0)),INV.Qty) AS freeQty,INV.Qty as iQty
    ,UPC,Series
    FROM DirectDATAINE5.dbo.WSL_InvLocation AS INV

    LEFT JOIN (
    SELECT szItem,szBinLoc ,ISNULL(SUM(iReqQty),0) AS RQty
    FROM .dbo.tblAllocatedLocations
    WHERE boolNUsed=0 AND boolCollected=0
    GROUP BY szItem,szBinLoc

    ) AS ALLO
    ON(ALLO.szItem COLLATE DATABASE_DEFAULT =INV.Item COLLATE DATABASE_DEFAULT AND ALLO.szBinLoc COLLATE DATABASE_DEFAULT =INV.BinLoc COLLATE DATABASE_DEFAULT )

    LEFT JOIN (
    SELECT ISNULL(1,0) AS Report,WL.szBinLoc,MA.szUPC,MA.szSeries FROM tblLocWsl AS WL
    INNER JOIN dbo.tblJobsManifest AS MA
    ON(MA.iUid=WL.iUID)
    WHERE boolResolve = 0
    ) AS Rep
    ON(INV.BinLoc COLLATE DATABASE_DEFAULT =Rep.szBinLoc COLLATE DATABASE_DEFAULT AND INV.Series COLLATE DATABASE_DEFAULT =Rep.szSeries COLLATE DATABASE_DEFAULT AND INV.UPC COLLATE DATABASE_DEFAULT =Rep.szUPC COLLATE DATABASE_DEFAULT)

    WHERE INV.Qty>0 AND ISNULL((INV.Qty-ISNULL(ALLO.RQty,0)),INV.Qty)>0 –AND Modified>’2011-01-01 00:00:00.000′
    –AND SUBSTRING (INV.BinLoc,1,1) = ‘A’
    AND Report IS NULL AND UPC= ‘20612351’ AND Series= ‘00245’—-return Values

    Like

  21. This resolved my issue:

    CREATE VIEW vw_ViewLimit1
    AS
    SELECT TOP (select count(*) from Sales.SalesOrderDetail) *
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID DESC
    GO
    — Select from view
    SELECT *
    FROM vw_ViewLimit1
    GO

    Muirhec

    Like

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  23. actually i need to confirm something.. whats the idea behind the hack of adding order by into the views query.. because as per my knowledge, while fetching data using views you can use order by in the query..

    and if possible can someboday link me to the article of whats the use of views ? why its important and when its important ? because you can get the same result using query !!

    as per my knowledge views are for sharing data with 3rd party or something but m not sure about it.. i have googled about this but i never found simple and easy to understand information on this with little example.. hope to get reply from Mr. Pinal on this..

    thanks
    hs

    Like

    • You can link directly to a view using say ODBC to return just a recordset without using a select query. In this case you may want the order in the view.

      Like

  24. Well, I guess I am the black sheep in the discussion. First, I have been working with databases and SQL for over 25 years, going back to dBase. In all this time I have never seen any problem theoretically (not addressing any performance issues here) of using ORDER BY in views and philosophically I see nothing wrong with it if that is what the writer wants.

    My philosophy is that I see views as a presentation of data (a way of viewing the data) which involves lower level processing of data, sometimes simple in nature, sometimes more complicated. Along these lines, I view the use of ORDER BY in views (in part) as a means of customer service so that a user of the view doesn’t have to understand internals to determine how to sort. For that matter, based on some of the discussion points previously mentioned, I could argue that the WHERE clause should also not be used in views…just let the user determine how they want to work the the data returned from the view. In real life I don’t support this and think WHERE clauses have a place in views, and in the same vein, so do ORDER BY clauses.

    Also, sometimes I have wanted users to have a default view of something, which includes sorting. This comes in handy so I (and others) don’t have to take time to re-figure out how to sort the data each time we use the view for something. And views are useful (addressing a previous user’s question) because sometimes (when working with tools such as Access or InfoPath for example) complicated queries or SELECT statements cause problems and simplifying with a view is a slick and elegant solution.

    So, while apparently I speak heresy according to some, I see nothing wrong with ORDER BY in views and was very annoyed and perplexed when I saw views which worked as expected in SQL 2000 not work in SQL 2008 when we [finally] upgraded.

    Like

  25. Hello, great help this is.
    I’m currently learning in this whole database thing.
    I noticed that the order by does work in view. But ascending order only.
    As I just try with small data, I think it still looks allright.

    Can someone confirm this?

    Like

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  28. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  29. Just use select top (select count(*) from answers) FirstName, LastName from User Order by FirstName + ‘ ‘ + LastName – works and also honors the sorting.

    Like

  30. Hey people!! Few days ago i came across a strange problem with the Order By , While creating a new table i used
    Select – Into – From and Order By (column name)
    and When i open that table see tables are not arranged accordingly. I reverified it multiple times to make sure i am doing the ryt thing. one more thing i would like to add is till the time i dont use INTO , i can see the desired result but as soon as i create new table , i see there is no Order for tht coulumn. Plz help me !!!
    Thanks in Advance
    Sample code :
    Select
    a.Name ,
    a.class ,
    a.Roll No,
    b.Marks
    From Name As A inner join Marks as B
    on a.Name=b.name
    order b.marks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s