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