ORDER BY in VIEW returns different results SQL

Question

This is my viww:

CREATE VIEW [STD_USER].[view_TransInvoice]
AS

SELECT TOP 999999 Customernr, Referensnr,'2' as a, InvoiceRowData, FileHead
    FROM [STD_USER].[Transexport]
    WHERE InvoiceRowData IS NOT NULL
    UNION 
SELECT TOP 999999 Customernr, Referensnr,'1' AS a , InvoiceHead , FileHead
    FROM [STD_USER].[Transexport]
    WHERE InvoiceHead IS NOT NULL
    UNION 
SELECT TOP 999999 Customernr, Referensnr,'3' AS a , InvoiceFoot , FileHead 
    from [STD_USER].[Transexport]
    WHERE InvoiceFoot IS NOT NULL
    ORDER BY Customernr, Referensnr, 3

When I run it on the server (Microsoft SQL Server Standard Edition v. 8.00.2055) x64 I get the result that I want in the right order.

But when I run it on (Microsoft SQL Server Standard Edition v.10.50.1702.0) x86 I do not get the same result. It’s likes it ignores the ORDER BY statement when I run the VIEW. If I just run the SELECT statements I do on the other hand get the RIGHT result with the right order. The databases are exactly the same and the scripts as well on both the servers.

Please help me!

Answer 1

If you need an ORDER BY for the results you need to put an ORDER BY in the SELECT from the view.

The ORDER BY inside the View only serves to control what the TOP applies to for the [STD_USER].[Transexport] branch not for the eventual order of results in select operations against the view.

See TOP 100 Percent ORDER BY Considered Harmful. for more explanation of this.

Edit It is quite interesting though that the role of the final ORDER BY changes depending upon whether it is in a View or not. When the SELECT is run outside of a View it serves to order the entire results and it's role in limiting the TOP for the final branch of the UNION disappears.

Edit 2 This odd behaviour is discussed in the comments of this recent Connect Item

CREATE TABLE A (C VARCHAR(100))
CREATE TABLE B (C VARCHAR(100))


SELECT TOP 1000 C 
FROM A
UNION ALL
SELECT TOP 1000 C
FROM B
ORDER BY C

GO

CREATE VIEW V
AS
SELECT TOP 1000 C 
FROM A
UNION ALL
SELECT TOP 1000 C
FROM B
ORDER BY C

GO

SELECT *
FROM V

GO

DROP TABLE A
DROP TABLE B
DROP VIEW V