|
|
Posted by Erland Sommarskog on 12/20/06 22:38
Russ Rose (russrose@hotmail.com) writes:
> but your statement that clustered tables are not ordered is false.
The order of a clustered index is not such a trivial thing as it may
sound. It is sometimes claimed that the clustered index defines the
physical order the data is laid out, but this is not true if you look at
disk sectors. What is true is that if you physically follow page pointers
you will arrive a data pages in an order that agress with a clustered
index. And within the page, you will need use a row table, to access the
rows in order.
But what is really important to understand is that any physical order,
be that disk blocks, page pointers or whatever, has nothing to do with
the logical meaning of queries. These concepts are entirely disjunct
from each other, as long as we are only talking about the results of
queries. (When we talk performance, it is another issue.)
> True, if the compiler ignores the order by in the view, which it does
> not if the TOP statement is included.
ORDER BY is not permitted in a view, unless there also is a TOP clause.
And the sole meaning of the ORDER BY in a view, is to determine the
result of the TOP clause.
The syntax truely stinks. SQL 2005 includes OVER clause which is used
for the new analytical functions row_number(), rank() and dense_rank()
and also can be used with sum() & co. A syntax like:
TOP 10 PERCENT OVER (ORDER BY somecol)
would make it clear what it's all about: ORDER BY serves to determine
the outcome of TOP. Nothing else.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|