SQL order by and left outer join doesn't have correct order

Question

I have a view that is joining two tables and ordering by the first table. Except that the order isn't correct. It misses an occasional record, and then at the end, most of those records exist in order, and then at that end, the rest of the records exist in order. So it has records such as

1  (most of the records in order)
2
4
5
6
7
8
10
11
13
15
3  (the first set of missing records)
12
9 (the rest of the missing records)
14

My view is below. Do I need to do the order by before I do the join? What am I doing wrong? (I've acquired this view, and the exact same view in another db instance works correctly.)

CREATE VIEW [dbo].[SampleView]
AS
SELECT     TOP 100 PERCENT blp.*, ISNULL(YEAR(DATEADD(month, 2, tb.[End of D&D])), 0) AS DEMO_FY
FROM         dbo.Table1 AS blp LEFT OUTER JOIN
                      dbo.Table2 AS tb ON blp.FACIL_NAME = tb.[Structure ID]
ORDER BY blp.ID

(edit) The type for the sort field is [ID] [int] NOT NULL IDENTITY(1, 1),

Answer 1

Most likely the two SQL Servers are slightly different versions. There's a known hotfix for this:

http://support.microsoft.com/kb/926292/

Answer 2

Read Create a sorted view in SQL Server 2005 and SQL Server 2008 there is a hotfix and you HAVE to run in 2000 compatibility mode for it to work. Why not just do the order by when selecting from the view?

Answer 3

I would remove the ORDER BY clause from the view entirely. Instead, specify your order at the time you query the view.

Answer 4

A comment in this blog entry makes it pretty clear.

Just once again the text from BOL:

"When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself."

"Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly, as shown in the following query:

SELECT * FROM TopView
ORDER BY LastName

"

Answer 5

You should always "order" at the last possible point, since sorting is slow (N log N). When you perform a select on a view, you will usually include a where-clause. You only want to sort that result set.