SQL Server 9.0.1399: The following SQL makes is used to create a view in the
database, pay specific attention to the ORDER BY CLAUSE. When this statement
is executed within the query modify window in Management Studio, it is
ordered correctly. When the saved view is opened or run the results are not
ordered correctly. I have tried modifying the query, saving and rerunning it,
but the order of the data is never correct. What's going on here? not this
view worked fine in SQL 2000.

SELECT TOP (100) PERCENT dbo.tblGrainContracts.VendorNum,
dbo.tblGrainContracts.VendorName, dbo.tblGrainContracts.ResNum,
dbo.tblGrainContracts.ResDesc,
dbo.tblGrainContracts.Destination, dbo.tblGrainOriginInfo.CarNum,
dbo.tblGrainOriginInfo.ContractNum,
dbo.tblGrainOriginInfo.Inspection,
dbo.tblGrainOriginInfo.Weights, dbo.tblGrainOriginInfo.TestWt,
dbo.tblGrainOriginInfo.Dockage,
dbo.tblGrainOriginInfo.Moisture,
dbo.tblGrainOriginInfo.Garlic, dbo.tblGrainOriginInfo.Damage,
dbo.tblGrainOriginInfo.SBK, dbo.tblGrainOriginInfo.IDK,
dbo.tblGrainOriginInfo.TD, dbo.tblGrainOriginInfo.HD,
dbo.tblGrainOriginInfo.BC, dbo.tblGrainOriginInfo.FM,
dbo.tblGrainOriginInfo.NetWt
FROM dbo.tblGrainOriginInfo INNER JOIN
dbo.tblGrainContracts ON
dbo.tblGrainOriginInfo.ContractNum = dbo.tblGrainContracts.ContractNum
ORDER BY dbo.tblGrainOriginInfo.CarNum

From the SQL 2005 Books Online (December 5th update):

<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm">

When ORDER BY is used in a view definition, the clause is used only to
determine the rows returned by the TOP clause. The ORDER BY clause does not
guarantee ordered results when the view is queried, unless ORDER BY is also
specified in the query itself.

</Excerpt>

Note that the SQL 2000 behavior was undocumented. Relying on undocumented
behavior is at your own risk.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lee" <Lee (AT) discussions (DOT) microsoft.com> wrote

This behavior was documented in SQL server 2000, below is the excerpt from
the SQL 2000 documentation section for Create View, note the restriction on
using the order by was that the TOP clause be used

[you cannot] "Include ORDER BY clause, unless there is also a TOP clause in
the select list of the SELECT statement."

Does it say that the rows are returned in that order when you select from the view (unless you have
ORDER BY in that SELECT)? That ORDER BY is only there to make sure you get the desired rows back
(say 5 most expensive books), not the order. A table is not ordered, and a view is supposed to
behave like a table.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Lee" <Lee (AT) discussions (DOT) microsoft.com> wrote