January 8, 2008 at 2:09 am
Hi,
I hope, that I am in the right forum for my question.
I have a database "moved" (backup & restore) from a sql-2000 server to a sql-2005 server. Within the database there are a lot of view´s - mote of them for a different sort-by - order. The application which uses these database is written in ACCESS 2003 and "foreign" - so i can´t change it.
Now my problem:
When I open the View from the SQL-2000 enterprise-manager I get the view as "executed" - and so I have the right sort-order. But when I "open" the view from SQL-2005 Server Management Studio I have only the selected colums within the view -> BUT NO SORT-Order !!! - I think the sort-order is the primary index - which is also a column for the view.
When I connect from Access to the same view from a SQL2000 and from a SQL2005 server I get the same behavior - sorted by the "primary Index" with sql2005 and sorted by the "sort by" column by sql2000 connection.
So is there a possibility to get the same behavior from the sql2005 - view?
Thank you in Advance
Andi
January 8, 2008 at 3:16 am
SQL 2005 ignores sorts in a view unless the number of rows is limited by a top statement.
Views shouldn't have sorts in them. The sort should only be done on the outermost select - the one that retrieves from the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2008 at 3:14 am
To save repeating myself, have a look at http://www.sqlservercentral.com/Forums/Topic240596-29-5.aspx#bm418021
The syntax of the TOP statement has changed.
January 9, 2008 at 3:23 am
It's not so much that the syntax has changed, but that SQL is now permitted to ignore the Top 100% ... order by construct where it appears anywhere other than in the outer query.
That means it ignores it in a view or in a sub query. You can do things like Select top 999999 .. order by ...
but it is a kludge and is not recommended.
Ordering should be done at the outer level (ie, when you select from the view) no where else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2008 at 8:15 am
The sad truth is that Microsoft changed the behavior of Views leaving lots of little *time bombs* out there like this. The quick solution, if you cant change the application, is to go with the Kludge.
Instead of: Select top 100% * from MyTable
--Select the top 2 billion (or some other large number)
Select top 2141590000 * from MyTable
Even though Gail is correct and the order by *should* be placed on the outermost query, there is no excuse for Microsoft to have changed the behavior like this. In the past and in the Query designer in 2005 they allow you to set the order by *time bomb* with no warnings. It's totally inexcusable. They easily could have kept it 100% backward compatible using compatibility modes.
What's worse is that when you preview the View in 2005 you get the order by on the outermost query and it appears to work. Then, after you save the view and select it, its FUBAR. This is completely misleading to the user and it creates a *time bomb* that wont get discovered until later.
Instead to saying that you *should have* put the order by on the outermost query, I'd love to see the Microsoft developers adopt a more pragmatic approach. They removed a useful feature, and I'm sure that many people took advantage of features the GUI query designer *encouraged* you to use. There will be lots of developers out there that will have the same problem (when they find it).
Good luck
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply