GilaMonster let me explain with a simple example because i'm not understanding you.
Suppose the view is created like this(which is the same as mine except that i'm using also some inner joins):
CREATE VIEW 'myview'
SELECT DISTINCT TOP 100 PERCENT id, service, ordercolumn
ORDER BY ordercolumn
the above statement is accepted by Sqlserver. Sql server is not complaining about anything and I'm expecting that if i do "SELECT * FROM myview" i get the ordered result but this is not happening!
No, you should not get an ordered result. SQL expands out the query you wrote to
SELECT * FROM
(SELECT DISTINCT TOP 100 PERCENT id, service, ordercolumn
ORDER BY ordercolumn)
SQL then looks it this and makes some simplifications so that it can optimise the query better.
The top 100 percent can go, because it does not limit rows.
The order by is within a subquery that does not contain a row limitation (ie TOP) Therefore it is not needed and can also be removed
So the result is this
SELECT * FROM
(SELECT DISTINCT id, service, ordercolumn
and that is what SQL executes
This is my point of view on this behaviour.
It may be your view, but it is incorrect. The only time SQL is guaranteed to return rows in the order specified by an order by is when that order by is on the outer select statement. So SELECT * FROM myView ORDER BY OrderColumn will always return rows ordered by OrderColumn
This is clearly documented. From Books Online:
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.
Now, correct me if i understood you wrong but you suggested to use in my application the statement "SELECT * FROM myview ORDER BY ordercolumn".
Absolutely correct. The order by needs to be in the outer select statement (the one submitted from the app)
The problem with this is that everytime my application is sending that statement to sql server, the last will always order the result instead if the "order by" works inside the view than sql engine will order only in case of insert,update or delete statements on the concerned tables.
Huh? Not sure I understand you.
Views don't store data. They are only saved select statements. Tables have no defined order either. An order by on a view will have no effect at all on how data is stored in the tables that view depends upon. Order By is a clause in a select that orders rows returned by the query that it is part of. Nothing else.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass