I have two tables

  • Location Master ([LocatioNo],[LocationName],[Description])
  • Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo])

Table Contents
(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618850 "Click to view on Flickr")

and a view involving the 2 tables which was created in SQL Server 2000

CREATE VIEW [dbo].[QueryLocation]
       dbo.LocationData.LocationNo, dbo.LocationData.LocationCode, 
       dbo.LocationData.DDate, dbo.LocationData.BillNo, 
       SUM(dbo.LocationData.Quantity) AS Quantity, 
       dbo.LocationMaster ON dbo.LocationData.LocationNo = dbo.LocationMaster.LocatioNo 
       dbo.LocationMaster.LocationName, dbo.LocationData.LocationNo, 
       dbo.LocationData.LocationCode, dbo.LocationData.DDate, dbo.LocationData.BillNo, 
       dbo.LocationData.Model ORDER BY dbo.LocationData.BillNo

When the following query is execute in SQL Server 2000 the result in the grid was sorted on billno column.

SELECT *  FROM QueryLocation

Query Results
(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618858 "Click to view on Flickr")

After upgrade to SQL Server 2008 the same query when executed is not sorted on bill no field

Advice why this is not sorted on the group by column

Thanks in advance

  • 1
    You have no ORDER BY - therefore, SQL Server does not guarantee any order! If you need a specific sorting, then you need to specify that with an ORDER BY. If SQL Server 2000 did this - it was a coincidence which you should not rely on ... – marc_s Feb 25 '12 at 17:02
  • This behavior is described by the MS Support KB 926292. Generally a view definition should ignore the ORDER BY clause, but SQL Server 2000 did not. You can request a hotfix for 2005 or 2008, but your application really should not rely on it. – devio Feb 25 '12 at 17:17
  • 1
    Yes, you need ORDER BY on the outer query (not in the view). I've blogged about this (as have many others): sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/… – Aaron Bertrand Feb 25 '12 at 17:33
  • Great to know a hotfix is available – Qinnovator Feb 26 '12 at 3:30

ORDER BY within a view was only ever a broken "feature" (albeit one that enterprise manager seemed to encourage).

Views are meant, for the most part, to be indistinguishable from Tables. And Tables have no inherent order.

This means that the only place that an ORDER BY is guaranteed to affect the order in which results are returned is in the outermost SELECT - i.e. the one that references this view (assuming that that SELECT, in turn, is not within another view or UDF)

Within a view, UDF, etc, ORDER BY was only ever meant to work for its other/overloaded purpose - to make TOP make sense - i.e. without an ORDER BY, asking for the TOP 10 would be poorly defined, because, again, tables have no inherent order.

Unfortunately, there is a common mis-perception that this works because the Graphical view designers in Enterprise Manager and Management Studio actually encourage it - there are "sort" features included there, and if you use them, the tool inserts TOP 100 PERCENT and ORDER BY - despite this having been something that doesn't work since at least 2005.

Having just re-read the question - it was relying on the order of a GROUP BY? That's compounding one issue with another. There's no guarantee that the output from a statement involving a GROUP BY will have any particular order.

  • Ah, I forgot about Enterprise Manager back in the day. That was insane. I filed a bug on that back then, but they never fixed it in EM. – Dave Markle Feb 25 '12 at 17:04
  • @DaveMarkle - I believe the sort buttons even made it through to the designer in SSMS for 2005 (not sure about later versions), but by that time the engine had been updated to optimize TOP 100 PERCENT - making it a really poor experience for people unfamiliar with SQL. – Damien_The_Unbeliever Feb 25 '12 at 17:14
  • @DaveMarkle - AAAAAARGH. Just tried the graphical designer in 2008 R2. It still has Sort Order settings, and auto inserts TOP 100 PERCENT and ORDER BY. This hasn't worked for 7 years, and the designer has still not been updated to remove this mis-feature. – Damien_The_Unbeliever Feb 25 '12 at 17:17
  • 1
    The view designer in SQL Server 2012 also still allows you to specify 'Sort Order' columns, even though this makes absolutely no sense. We've complained about it connect.microsoft.com/SQLServer/feedback/details/317249/… connect.microsoft.com/SQLServer/feedback/details/249248/… and connect.microsoft.com/SQLServer/feedback/details/215076/… – Aaron Bertrand Feb 25 '12 at 17:37
  • Even though one of those says it was "fixed", I have yet to see it any any build of SQL Server 2012 or any hotfix or service pack for previous versions. But you at least get a warning when you try to save it. – Aaron Bertrand Feb 25 '12 at 17:38

In SQL Server 2012, if you try to create an ordered view with the view designer, here is the warning you get:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

I'm not sure if that happens in the latest builds of 2008 / 2008 R2.

While I think it's absolutely stupid to continue allowing the syntax, and having the view designer perpetuate the myth that this type of ordering means anything except a filter (excluding 0 rows when TOP = 100%), this is what they consider "fixed" according to Connect Item #249248. I've left a comment seeking confirmation, but mainly my point is that a warning on save is not good enough. What if I'm just using the view designer to generate the SQL, then I'm going to go paste it into a CREATE VIEW or ALTER VIEW script somewhere else?

Anyway the wording found in this and other bugs, as well as the other answers here, should confirm that your logic is wrong: ORDER BY / GROUP BY in a view does absolutely nothing to guarantee how the optimizer will return the rows from the view. And even in a query outside of a view uses GROUP BY, this also does not dictate ordering. If you want to guarantee order, you absolutely must apply ORDER BY to the outermost query. For some more reading see this blog post.

  • Excellent blog post. Throws more light on the issue – Qinnovator Feb 26 '12 at 3:30
  • It's good to see from the Connect post that Microsoft "greatly values" your feedback. Insane! – Dave Markle Feb 27 '12 at 15:52

It's not sorted because ordering by views something that is not legal. In fact, if you try to ORDER BY a column on the view without the TOP (100) PERCENT statement, you will get an error. Why does SQL Server allow TOP and ORDER BY? Because if you were to select anything other than 100% of the rows, your ORDER BY would essentially be used in conjunction with TOP to filter the rows.

Why does is this allowed in SQL 2000? It's a peculiarity of the SQL 2000 optimizer, and your noncompliant code was "getting away with it". Think of it this way -- the designers of SQL 2008 (2005 also exhibited this behavior) had to make a choice as to whether or not they were going to make views run fast and ignore code like this, or if they were going to slow down view execution just to support invalid coding constructs.

The moral of the story is that you can not ORDER BY on a view -- you never really could. All ORDER BY statements must be performed by the query which queries the view itself.

There are some tricks out there you could try, but they are only to be used if you absolutely cannot update the code which queries the view. I would not rely on them or recommend them for any future or current development.

  • +1: Agreed. Because the view encapsulates the logic, someone could specify an additional ORDER BY on the use of the view in queries -- the original ORDER BY becomes a waste of resources. – OMG Ponies Feb 25 '12 at 17:02
  • 1
    +1. There's also a trace flag, which you shouldn't use unless, as you suggest, there is absolutely no other option. But I'm not sure if it handles the GROUP BY scenario anyway. support.microsoft.com/kb/926292 – Aaron Bertrand Feb 25 '12 at 17:31
  • 1
    Great point on the trace flag... I didn't know that existed. I kind of wish I still didn't know it existed ;-) – Dave Markle Feb 25 '12 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy