I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
From Books Online 2005:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
When ORDER BY is used in the definition of a view, inline
function, derived table, or subquery, the clause is used
only to determine the rows returned by the TOP clause.
The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is
also specified in the query itself.
If you want to see the contents of the view in a particular
order, you must specify ORDER BY when you query the view.
The ORDER BY in the view definition here does nothing at all.
It's unfortunate that TOP (100) PERCENT is allowed, let alone
added by the view designer, and my suggestion is never use
TOP (100) PERCENT, because it is meaningless and leads you
to think you can create an "ordered view", which you cannot.
Steve Kass
Drew University
sg2000@.discussions.microsoft.com wrote:
> I have created view by jaoining two table and have order by clause.
>
> The sql generated is as follows
>
> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
> FROM dbo.UWYearDetail INNER JOIN
> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId
> = dbo.UWYearGroup.UWYearGroupId
> ORDER BY dbo.UWYearDetail.PlanVersionId,
> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,
> dbo.UWYearGroup.MandDFlag,
> dbo.UWYearGroup.EarningsMethod,
> dbo.UWYearGroup.EffectiveMonth
>
>
>
> If I run sql the results are displayed in proper order but the view only
> order by first item in order by clause.
>
> Has somebody experience same thing? How to fix this issue?
>
> Thanks,
>
>
>
>
|||Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.|||This is a common complaint, but if order by wasn't allowed, then TOP (anything other than 100%) would not be of much use. That is why you can't put an order by clause in the view definition without the TOP clause.
The bottom line is, like Steve said, there is no guarantee that tables have any order, including views with Order By clauses, Tables with clustered indexes, etc. This allows the optimizer to work with the hardware in the very fastest manner if you don't explicitly ask for rows in a given order.
It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)
|||We have lot of views in SQL server 2000. It will be big pain to convert to SQL 2005. i.e. we need to change views to remove 'order by' and then need to identify the calls to the view and add order by instead. This is going to be big problem in conversion from SQL Server 200 to SQL Server 2005 going forward. Do you see any easy way to acomplish this?
|||
Hello,
I dont know if its allowed here, but there is a tool that allows you to find references to SQL Server objects even in sourcecode. Take a closer loot at the apexsql suite of tools. They will help you to identify where calls to those views are made. I am not sure which product was able to do this, but i think it was ApexSQL Clean ( http://www.apexsql.com/sql_tools_clean.asp )
If tossing this add in here was not allowed post so and ill remove the link.
(Edit: Free No-Fuss trial Version is available for 30 days)
|||It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.
|||Yes the tools will be helpful but what if my application is calling view from dlls or externally. I don't think any tool will hepl here. I have application built with rapid application builder called Ironspeed and it generates code which is using view.
What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?
|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||Hi sg2000,
You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT
The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.
Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.
|||This still doesn't guarantee that rows will be returned in the same order to client. The whole point is not about use of TOP 100 PERCENT with ORDER BY. Any ORDER BY clause in derived table or view for example only applies within that scope. To present rows in a particular order to client, you HAVE to include ORDER BY clause in the outermost SELECT statement. This is the only way the query will produce expected results always. See blog post below also for ordering guarantees in SQL Server:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
|||OK, this is absolutely retarded.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
This says :
Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?
|||
Because of the last bit:
The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified
And yes, it is still a bit retarded, but the whole New View screen is pretty unpleasant if you ask me. And yes again, it is ironic that a tool that is made to make things easier for newer users often makes it less easy. Go figure :)
|||not trying to be harsh...i had a little wine in me at the time and things just came out. (this is my drunk apology haha) ...|||No problemo. I agree with you wholeheartedly that it should be better than it is, either way :)
No comments:
Post a Comment