InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:20:28 GMT20http://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspx[url=http://msdn.microsoft.com/en-us/library/ms187956%28v=sql.90%29.aspx]CREATE VIEW (Transact-SQL) SQL Server 2005[/url] states the following: "The ORDER BY clause is used [i]only to determine the rows that are returned[/i] by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself." Most folks assume that the rules which apply to views apply to derived tables also.SELECT TOP (100 PERCENT) is optimised out as Dave B stated, it's meaningless. I think you can still cheat with SELECT TOP 2147483647 or some other number safely bigger than the anticipated rowcount of the result set.[quote]It looks like another part of the query relies on the records being a specific order so it can do some differential calcs.[/quote]Even if the ORDER BY worked, it would be unlikely to do anything except introduce an unnecessary sort; what would happen to the results from this derived table if the next join operator was anything other than a merge join without a presort? For the query to work as designed, you'd need merge joins (without presorts) all the way between this output and "another part of the query". So, if the query results are correct it's certainly not by design - which means it works by accident.Thu, 11 Oct 2012 01:45:35 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspx[quote][b]mark 4643 (10/10/2012)[/b][hr]Hi AllI'm trying to optimize a monstrous query in a report that a vendor supplied. Part of the query has SELECT TOP(100 PERCENT) which makes no sense to me.I see no point in the TOP (100 PERCENT) surely it'll just return all records, or am I missign something?RegardsMark[/quote]You cannot put order by clause alone inside a subquery; in order to do that you need to specify top 100 percent or you can use FOR XML clause. This fundamental holds true for views, inline functions, derived tables, subqueries, and common table expressions where the order by clause is invalid without TOP or FOR XML operators.Hope this helps:-)Wed, 10 Oct 2012 22:47:42 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspxBlurgh, sick in my mouth a little.Im no sql purist by any means, but this *cant* work. ie relying on top 100 percent to order ,(at least in the long term)Consider these two queries....[code="sql"]select *from (Select 1 as rown) aright join (select top(100) percent object_id,name from sys.objects order by name ) as b on a.rown<>b.object_id goselect *from (Select 1 as rown) aright join (select top(99.99) percent object_id,name from sys.objects order by name ) as b on a.rown<>b.object_id[/code]Look at the plans, the top 100 percent make so little sense it gets optimized out and the results come back in a 'random' order, or at least in SQL 2012 it does.Ordering within SQL can and should only be applied to the final result set.There is a 'fix' ( i hate to use that word for this issue ) in 2008http://support.microsoft.com/kb/926292At the back of my mind i think there may even be a trace flag to enable this too ( im sure someone will correct me if wrong)Wed, 10 Oct 2012 22:32:24 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspx[quote][b]Sean Lange (10/10/2012)[/b][hr]As Michael said you need the TOP when using a derived table and an ORDER BY.What I don't understand is why there is a need to have an order by in the derived table. The order is pointless in this case. It seems that you could remove the top 100 percent AND the order by in the derived table.[/quote]Thanks for replies. I may have figured it out. It looks like another part of the query relies on the records being a specific order so it can do some differential calcs. I'm sure there's a better way, jut got to try and figure out everything else the query is doingRegardsMarkWed, 10 Oct 2012 14:30:22 GMTmark 4643http://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspxAs Michael said you need the TOP when using a derived table and an ORDER BY.What I don't understand is why there is a need to have an order by in the derived table. The order is pointless in this case. It seems that you could remove the top 100 percent AND the order by in the derived table.Wed, 10 Oct 2012 14:09:20 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspxTOP is required when you have an ORDER BY in a derived table or view.Wed, 10 Oct 2012 14:06:49 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1371098-391-1.aspxHi AllI'm trying to optimize a monstrous query in a report that a vendor supplied. Part of the query has SELECT TOP(100 PERCENT) which makes no sense to me.[code="sql"]...RIGHT OUTER JOIN ( SELECT TOP ( 100 ) PERCENT ts445.Date ,ts445.WorkCentre ,sts1.OpCode ,MAX(sts1.CostRateNT) AS CostRateNT ,MAX(sts1.AssistantRate) AS AssistantRate ,sts1.JobNo FROM Live_Costing.dbo.Timesheet AS ts445 INNER JOIN Live_Costing.dbo.TSTrans AS sts1 ON ts445.TimeSheetNo = sts1.TSID GROUP BY ts445.Date ,ts445.WorkCentre ,sts1.OpCode ,sts1.JobNo ORDER BY ts445.WorkCentre ,ts445.Date ,sts1.OpCode ) AS crsq1 RIGHT OUTER JOIN Live_Orders.dbo.OrderHeader AS oh...[/code]I see no point in the TOP (100 PERCENT) surely it'll just return all records, or am I missign something?RegardsMarkWed, 10 Oct 2012 13:44:00 GMTmark 4643