There is a major issue in SQL server 2008 when running queries from views having the ORDER by clause. The outcome is with random order. Example.

We create the following view:

CREATE VIEW [dbo].[ViewMaterials]
SELECT     dbo.Materials.CodeId, dbo.Materials.Master, dbo.Materials.MeasureUnit, dbo.Suppliers.Master AS Supplier, dbo.Materials.Category
FROM         dbo.Materials LEFT OUTER JOIN
                      dbo.Suppliers ON dbo.Materials.SupplierId = dbo.Suppliers.CodeId
ORDER BY dbo.Materials.Master

Where running a query like Select * from ViewMaterials the order of the list will be random and not ordered by the field master. Microsoft proposes some solutions and applying a service pack but with no result.

The only solution we found was to create the view with the usage of TOP (999999) instead of TOP (100) PERCENT that Sql server adds it automatically when constructing a view with order by clause.

Leave a Reply

Your email address will not be published. Required fields are marked *