ORDER BY in VIEW returns diffrent results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ORDER BY in VIEW returns diffrent results

Author

Topic

wuze
Starting Member

1 Post

Posted - 2011-11-25 : 10:10:35

This is my VIEW:
USE [STD_DATABASE]
--
-- Kill the procedure if it exists
--
IF (EXISTS (SELECT [name] FROM [sysobjects]
WHERE (([name] = 'view_TransInvoice') AND ([type] = 'V'))))
DROP VIEW [STD_USER].[view_TransInvoice]
GO

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!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-11-25 : 10:13:04

A view has no order:

http://en.wikipedia.org/wiki/Relation_(database)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 11:01:26

If you look at the query plan I think this forces a sort.

CREATE VIEW [STD_USER].[view_TransInvoice]
AS
select top 100 percent *, seq = ROW_NUMBER() over (order by Customernr, Referensnr, a)
from
(
SELECT Customernr, Referensnr,'2' as a, InvoiceRowData, FileHead
FROM [STD_USER].[Transexport]
WHERE InvoiceRowData IS NOT NULL
UNION
SELECT Customernr, Referensnr,'1' AS a , InvoiceHead , FileHead
FROM [STD_USER].[Transexport]
WHERE InvoiceHead IS NOT NULL
UNION
SELECT Customernr, Referensnr,'3' AS a , InvoiceFoot , FileHead
from [STD_USER].[Transexport]
WHERE InvoiceFoot IS NOT NULL
) a
ORDER BY seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-26 : 18:02:43

SQL Server will ignore sorts in views since version 2005.
The only sort order that is guaranteed with views is the outermost ORDER BY (the view-consuming query).

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources