Posted by Greg D. Moore \(Strider\) on 12/20/06 03:55

"Russ Rose" <russrose@hotmail.com> wrote in message
news:Qaidna78puaMDxXYnZ2dnUVZ_ompnZ2d@comcast.com...
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in
> message news:O6Rhh.914$yx6.162@newsread2.news.pas.earthlink.net...
>>
>> "Russ Rose" <russrose@hotmail.com> wrote in message
>> news:rIKdnRRxk8OS2BrYnZ2dnUVZ_s-rnZ2d@comcast.com...
>>>>
>>>> SQL has one data structure, tables.
>>>
>>> And indexes are what exactly?
>>
>> An index, not a data structure.
>
> You are saying it neither has structure nor data?

I'm saying it's not a datastructure.

>
> What value would a "covering" index be if that were true?

Again, an index doesn't guarantee the logically returned order.

First of all ask youself, what does order mean? Is it the Primary Key?
Your covering index? Or something else?

What if you have an IDENTITY column and a Primary Key, which is the correct
"order".

>
>
> Order of return has nothing to do with the physical order of a table.

Exactly. That was my point. A clustered index may insure physical order on
the disk to add performance in a scan using that index, but it doesn't mean
the table it ordered.


> If your statement were 'views (or cursors) have no intrinsic order' I
> might agree with you, but your statement that clustered tables are not
> ordered is false.

No, it's not. Look at the definition of a table. Don't focus on the
physical implementation on the disk.


>
>>
>> In theory if you did a select * from FOO and it already had the last 100
>> records physically stored last in cache it could return those before
>> returning the rest.
>>
>
> True, if the compiler ignores the order by in the view, which it does not
> if the TOP statement is included.

At the Orlando SQL Connections conference I believe Itzak-Ben Gahn has shown
a few good cases where this is NOT true, even in SQL 2000.


>
>>
>>>
>>>>
>>>> Therefor the fact that SQL 2000 allowed the above syntax is basically
>>>> "wrong".
>>>
>>> Yet it works...
>>
>> In SQL 2000. But not SQL 2005 or presumably later versions.
>>
>>
>
> Have I claimed otherwise?

You keep saying a table has an order. It doesn't. Take up it up with Codd
and Date.

>
>

[Back to original message]