I have created a simple view consisting of 3 tables in SQL.
By right clicking and selecting Design, in the Object explorer table, i modified my custom view. I just added sortby asc in a field.
The problem is that the changes are not reflected in the outout of the View. After saving the view, and selecting Open view the sort is not displayed in output.
So what is going on here?
Generally, Views cannot be sorted.
(As others mentioned, there's a hack to do it, but since you are using a visual query designer rather than writing your view definition in SQL, it's probably difficult to implement that hack.)
You didn't actually "modify" your view, you only changed the SELECT statement that EM was using to select from your view. Sort settings are not retained in the view definition.
When you close the tab, EM doesn't remember your sort preference for that view, so when you open the view again, it comes out in whatever order SQL Server decides.
Technically, it is possible to bake sorting into a VIEW but it is highly discouraged. Instead, you should apply sort while selecting from the view like so:
Select ...
From MyView
Order By SortByCol ASC
If you really wanted to know (but again, I would strongly recommend against this), you can use the TOP command to get around the limitation of sorting in the view:
Select TOP 100 PERCENT * Col1, Col2....
From Table1
Order By SortByCol ASC
It seems :
There is a restriction on the SELECT clauses in a view definition in SQL Server 2000, SQL 2005 and SQL 2008. A CREATE VIEW statement cannot include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement. The ORDER BY clause is used only to determine the rows that are returned 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.
There is also a hotfix that needs to be applied. After that you should use Top 100% to make sure that the order by works.
HTH