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

sql serversql-server-2008

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]
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

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

Best Answer

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.

Related Question