All geek questions in one place

ORDER BY in VIEW returns different SQL results

This is my link:

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 correct order.

But when I run it (Microsoft SQL Server Standard Edition v.10.50.1702.0) x86, I do not get the same result. He likes to ignore the ORDER BY statement when I run VIEW. If I just run the SELECT statements, on the other hand, I get the correct result with the correct order. The databases are exactly the same as on both servers.

Please help me!

+4
source share
3 answers

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

ORDER BY inside a view is used only to control what TOP is used for branching [STD_USER].[Transexport] , and not for the final order of results in the choice of operations against the view.

See TOP 100 PERCENT OF ORDER UNDER AUTHORITY. for a more detailed explanation of this.

Change Interestingly, however, the role of the final ORDER BY varies depending on whether it is in the View or not. When SELECT run outside the view, it serves to organize all the results, and this role in the TOP constraint for the final UNION branch disappears.

Edit 2 This strange behavior is discussed in the comments of this recent connection 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

Plan

+9
source

I do not think you should set ORDER BY to VIEW to organize the data. The view displays a set of data that can then be queried.

This must be done when requesting a view.

 SELECT * FROM [view_TransInvoice] ORDER BY Customernr, Referensnr, a
+1
source

Perhaps you did not receive the patch on one of the servers?

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

0
source

Source: https://habr.com/ru/post/1383087/

More articles:


All Articles