|
|
Posted by Hugo Kornelis on 09/09/05 22:56
On 8 Sep 2005 18:55:04 -0700, Matik wrote:
>Hello 2 all,
>
>Thank You for response. Hugo, additional explenation: I posted just a
>part of view acctualy, in normal, it uses at the end pre defined order
>by. To use order by in a view, you need to specify a top :(
Hi Matik,
And even then, it doesn't serve any practical use.
The rows in a view, like the rows in a table, are UNordered _by
definition_. The only way to guarantee that you retrieve rows in a
predefined order is by using an ORDER BY clause on the final SELECT
statement that actually retrieves the data.
In the SQL-92 ANSI standard, it's not permitted to use ORDER BY in a
view definition for exactly this reason. And this is carried over to the
behaviour of SQL Server - with only one exception: when you add a TOP
constraint (which is not defined in SQL-92 either), you can (no, SHOULD)
use an ORDER BY clause to define what ordering to use **for the
evaluation of the TOP clause**. This is meaningful when the TOP clause
specifies a fixed number, or something less than 100 percent ("this view
should only containt the 10 most expensive articles" - TOP 10 ... ORDER
BY Price).
If you use TOP 100 PERCENT ... ORDER BY ... in the definition of your
view, you'll almost certainly see the results in the desired order
everytime you test it. But be warned - there is no guarantee that you'll
never have a different order. And since Murphy's law says that anything
that CAN go wrong, WILL go wrong, be prepared to do some serious
debugging sometime in the middle of the night, just when you need your
sleep most.
Or fix your code now - add ORDER BY to the SELECT statements that need
to return data in a specific order, remove the ORDER BY and the TOP 100
PERCENT from your view definition and fix any code that appears to
depend on the order in which rows are returned or processed, even in the
absense of any ORDER BY.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|