After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

Question

I have two tables

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

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

CREATE VIEW [dbo].[QueryLocation]
AS
  SELECT TOP (100) PERCENT 
       dbo.LocationData.LocationNo, dbo.LocationData.LocationCode, 
       dbo.LocationData.DDate, dbo.LocationData.BillNo, 
       dbo.LocationData.Model,              
       SUM(dbo.LocationData.Quantity) AS Quantity, 
       dbo.LocationMaster.LocationName
  FROM 
       dbo.LocationData 
  INNER JOIN 
       dbo.LocationMaster ON dbo.LocationData.LocationNo = dbo.LocationMaster.LocatioNo 
  GROUP BY 
       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

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

Answer 1

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.

Answer 2

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.

Answer 3

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.