|
|
Posted by Russ Rose on 12/19/06 01:49
"Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in message
news:c8Hhh.777$yx6.596@newsread2.news.pas.earthlink.net...
>
> "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.
And indexes are what exactly?
>
> Tables are not ordered.
Even clustered ones?
>
> Therefor the fact that SQL 2000 allowed the above syntax is basically
> "wrong".
Yet it works...
>
> 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.
Agreed. I rarely use views for any reason.
Personally I prefer sorting in my recordsets/datasets since that is more
often a presentation function.
>
>
>>
>>>
>>> --
>>> 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]
|