Posted by Greg D. Moore \(Strider\) on 12/19/06 01:21

"Russ Rose" <russrose@hotmail.com> wrote in message
news:POidnUE3BrX-qRrYnZ2dnUVZ_hy3nZ2d@comcast.com...
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns989B7877F6E6EYazorman@127.0.0.1...
>> Russ Rose (russrose@hotmail.com) writes:
>>> "Hurricane" <mgreenway@gmail.com> wrote in message
>>> news:1166213549.389511.148520@t46g2000cwa.googlegroups.com...
>>>> When I create a view in SQL and include an ORDER BY clause i can see it
>>>> in Management Studio. However, when I call the same view from an ASP
>>>> page the order goes completely haywire.
>>>>
>>>>
>>>> Any ideas?
>>>>
>>>
>>> Does your view specify TOP 100 PERCENT?
>>>
>>> CREATE VIEW dbo.OrderByDateView
>>>
>>> AS
>>>
>>> SELECT TOP 100 PERCENT Field1, Field2, Date1
>>> FROM Table1
>>> ORDER BY Date1
>>
>> To clarify David's post: on SQL 2000 the above appears to work. That is,
>> if you say "SELECT * FROM Table1" the data comes back in the same order
>> as
>> the ORDER BY clause most of the time. However, that is mere chance, and
>> in
>> SQL 2005 it does not happen that often at all.
>>
>> Logically the TOP 100 PERCENT and the ORDER BY means nothing at all.
>
> Would it mean nothing at all if requesting 10%?

The problem (as Celko pointed out in his usual quite manner ;-) is taht a
VIEW is logically the same as a table.

SQL has one data structure, tables.

Tables are not ordered.

Therefor the fact that SQL 2000 allowed the above syntax is basically
"wrong".

Unfortuantely it's a "wrong" that many people relied on.

You're better off rewriting the VIEW to remove that and doing your ORDER BY
in your select.


>
>>
>> --
>> 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
>
>

[Back to original message]