View Order Dissappearing

[Hurricane]

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?

[David Portas]

Re: View Order Dissappearing

Hurricane wrote:

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?

Views are unordered by definition. ORDER BY is useful in a view only to
define the subset of rows to be selected by the TOP clause - it doesn't
affect the expected ordering of a query against the view.

The right way to do it is to add ORDER BY to the SELECT statement that
queries the view.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Serge Rielau]

Re: View Order Dissappearing

Hurricane wrote:

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.

Good! views aren't called, they are selected from.
When you issue a SELECT it only has an ORDER if you specify it for that
SELECT.
Any order specified inside the view definition is irrelevant. Only TOP
cares for nested ORDERs

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[Russ Rose]

Re: View Order Dissappearing


"Hurricane" <mgreenway@gmai l.comwrote in message
news:1166213549 .389511.148520@ t46g2000cwa.goo glegroups.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.OrderByDate View

AS

SELECT TOP 100 PERCENT Field1, Field2, Date1
FROM Table1
ORDER BY Date1

[David Portas]

Re: View Order Dissappearing

Russ Rose wrote:

"Hurricane" <mgreenway@gmai l.comwrote in message
news:1166213549 .389511.148520@ t46g2000cwa.goo glegroups.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?
>

If it does then the OP should remove it and the ORDER BY clause. TOP
100 PERCENT is redundant and misleading. It achieves nothing useful.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: View Order Dissappearing

Russ Rose (russrose@hotma il.com) writes:

"Hurricane" <mgreenway@gmai l.comwrote in message
news:1166213549 .389511.148520@ t46g2000cwa.goo glegroups.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.OrderByDate View
>
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.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[--CELKO--]

Re: View Order Dissappearing

>Any ideas? <<

You might also want to get a book on RDBMS. You made this mistake
because you do not know what a table is. If you missed a concept that
fundamental, you most probably don't know enough about RDBMS to use it
proper.

[Russ Rose]

Re: View Order Dissappearing


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.orgwrote in message
news:1166260108 .097369.244780@ 16g2000cwy.goog legroups.com...

Russ Rose wrote:

>"Hurricane" <mgreenway@gmai l.comwrote in message
>news:116621354 9.389511.148520 @t46g2000cwa.go oglegroups.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?
>>

>
If it does then the OP should remove it and the ORDER BY clause. TOP
100 PERCENT is redundant and misleading. It achieves nothing useful.

Other than returning the rows in the desired order that is...

>
--
David Portas, SQL Server MVP
>
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
>
SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--
>

[Russ Rose]

Re: View Order Dissappearing


"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns989B787 7F6E6EYazorman@ 127.0.0.1...

Russ Rose (russrose@hotma il.com) writes:

>"Hurricane" <mgreenway@gmai l.comwrote in message
>news:116621354 9.389511.148520 @t46g2000cwa.go oglegroups.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.OrderByDate View
>>
>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%?

>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

Re: View Order Dissappearing


"Russ Rose" <russrose@hotma il.comwrote in message
news:POidnUE3Br X-qRrYnZ2dnUVZ_hy 3nZ2d@comcast.c om...

>
"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns989B787 7F6E6EYazorman@ 127.0.0.1...

>Russ Rose (russrose@hotma il.com) writes:

>>"Hurricane" <mgreenway@gmai l.comwrote in message
>>news:11662135 49.389511.14852 0@t46g2000cwa.g ooglegroups.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.OrderByDate View
>>>
>>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@sommarsk og.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pro...ads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodinf...ons/books.mspx

>
>

[Russ Rose]

Re: View Order Dissappearing


"Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co mwrote in message
news:c8Hhh.777$ yx6.596@newsrea d2.news.pas.ear thlink.net...

>
"Russ Rose" <russrose@hotma il.comwrote in message
news:POidnUE3Br X-qRrYnZ2dnUVZ_hy 3nZ2d@comcast.c om...

>>
>"Erland Sommarskog" <esquel@sommars kog.sewrote in message
>news:Xns989B78 77F6E6EYazorman @127.0.0.1...

>>Russ Rose (russrose@hotma il.com) writes:
>>>"Hurricane " <mgreenway@gmai l.comwrote in message
>>>news:1166213 549.389511.1485 20@t46g2000cwa. googlegroups.co m...
>>>>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.OrderByDate View
>>>>
>>>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@sommarsk og.se
>>>
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/pro...ads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodinf...ons/books.mspx

>>
>>

>
>

[Ed Murphy]

Re: View Order Dissappearing

Russ Rose wrote:

"Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co mwrote in message
news:c8Hhh.777$ yx6.596@newsrea d2.news.pas.ear thlink.net...

>Tables are not ordered.

>
Even clustered ones?
>

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

>
Yet it works...

Relying on behavior that just happens to work (but is not promised to
do so) is asking for trouble down the road. But you knew that.

Hypothetical example that I came up with a while back: the server
might assign multiple CPUs to a single query, each scanning a different
portion of the relevant index range and applying the WHERE conditions,
then shuffling the matching rows together to form the output.

[David Portas]

Re: View Order Dissappearing

Russ Rose wrote:

Tables are not ordered.

>
Even clustered ones?
>

Even clustered tables are not logically ordered. There is no way to
guarantee that a query against a clustered table will return a result
that respects the order of the clustered index key UNLESS you specify
ORDER BY in the query

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

>
Yet it works...
>

It does not "work" the way you think. When querying the view in some
cases you will get data returned in the same order as the ORDER BY
clause in the view. In some cases you will not. This is consistent with
the documented behaviour: the order is undefined unless you specify
ORDER BY.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[David Portas]

Re: View Order Dissappearing

Russ Rose wrote:

If it does then the OP should remove it and the ORDER BY clause. TOP
100 PERCENT is redundant and misleading. It achieves nothing useful.

>
Other than returning the rows in the desired order that is...
>

Evidently not.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: View Order Dissappearing

Russ Rose (russrose@hotma il.com) writes:

"Erland Sommarskog" <esquel@sommars kog.sewrote in message

>Logically the TOP 100 PERCENT and the ORDER BY means nothing at all.

>
Would it mean nothing at all if requesting 10%?

Yes.

SELECT TOP 10 PERCENT EmployeeID, Salary
FROM Employees
ORDER BY Salary DESC

means "Give the tenth of the employees with the highest salary".

However if you would put this in a view and say:

SELECT EmployeeID, Salary FROM myview

There is no guarantee that the employees would be listed in salary order.
Any SELECT statement without ORDER BY tells SQL Server that it's free to
to returns the rows in any order it feels like.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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