SQL. Sorting by a field

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, an开发者_高级运维d selecting Open view the sort is not displayed in output.

So what is going on here?

---

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

---

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.