November 25, 2011 at 8:07 am
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!
November 25, 2011 at 8:33 am
Hi,
I would suggest that you check the Server/Database collations. You may well find that they are different which may explain the anomaly you are seeing.
Thanks,
Simon
November 25, 2011 at 8:39 am
Having ORDER BY in your view definition does not guarantee that the results will be ordered when you SELECT from the view. The only way you can ensure that is to use another ORDER BY in your SELECT statement.
John
November 25, 2011 at 8:56 am
wuze_ (11/25/2011)
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 not an edition issue, it's a version issue.
SQL Server 2000 (v.8) honored ORDER BY predicates in views, SQL Server 2005 (v.9) and higher don't.
-- Gianluca Sartori
November 25, 2011 at 10:01 am
Gianluca Sartori (11/25/2011)
wuze_ (11/25/2011)
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 not an edition issue, it's a version issue.
SQL Server 2000 (v.8) honored ORDER BY predicates in views, SQL Server 2005 (v.9) and higher don't.
In 2000 - this was actually considered a bug and was fixed in 2005. Views should act the same as a table and tables are unordered sets. The ORDER BY in a view will only be used in relation to the TOP predicate and SQL Server can return the data in any order it chooses.
The only way to guarantee the order is to put the ORDER BY on the query using the view.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2011 at 11:02 am
Hello!
You are check both server and tables and columns collation.
November 25, 2011 at 11:54 am
salliven (11/25/2011)
Hello!You are check both server and tables and columns collation.
How will this change the fact that SQL Server will return rows in whatever order it deems is best - unless you put the order by on the calling query?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply