March 22, 2007 at 1:41 pm
Hello,
I've created a View in SQL Server 2005 which sorts a table called People by its name.
select * from People order by name
After I saved the View, and then went back to "Open View", the results came out sorted by the PK instead of name. I then tried to "Modify" the view, and in the modify page, clicked the "Execute SQL" button. The results sorted by name this time around. How can I get the "Open View" to work as desired, or must I always have to run the view when editing it?
March 22, 2007 at 1:46 pm
Views should not contain an Order By.
If you need a resultset to be ordered a certain way, always use an Order By on your Select statement.
March 23, 2007 at 4:59 am
It's easy to be deceived. If you have an order by clause in a view, the view editor adds the top 100 % foolishness to the query. So when you execute the view from the editor it returns sorted records, but when you save and execute the view directly, the results are unordered. It seems the the top 100% was a workaround that worked in earlier versions, but not any more.
There seem to be a number of other places where the query editor/builder has not kept pace with the 2005 DB engine. Pivot queries are one example, and I have hit others.
March 23, 2007 at 6:44 am
You can "cheat" if you really need this. Your view can contain a TOP operator that is higher than the number of rows the table will ever have.
SELECT TOP 10000000 FROM MyTable ORDER BY 1
This will actually order the view. This can also be done in subqueries for certain performance reasons.
In a view, this is probably a bad idea - the above suggestion to order the results in your application is a much better approach. Another option would be to use a stored procedure or function for this type of action, but views are not a widely accepted appropriate solution for sorting data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply