<a href="https://bytes.com/topic/sql-server/answers/577795-view-order-dissappearing">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/m...S,SQL.90).aspx
--

Dec 15 '06 #2

Serge Rielau

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/Fo...Forum2006.html

Dec 15 '06 #3

Russ Rose

"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.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

Dec 16 '06 #4

Russ Rose wrote:

"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.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/m...S,SQL.90).aspx
--

Dec 16 '06 #5

Russ Rose (ru******@hotmail.com) writes:

"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 16 '06 #6

--CELKO--

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

Dec 16 '06 #7

Russ Rose

"David Portas" <RE****************************@acm.orgwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Russ Rose wrote:
>"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googleg roups.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/m...S,SQL.90).aspx
--

Dec 19 '06 #8

Russ Rose

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Russ Rose (ru******@hotmail.com) writes:
>"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googleg roups.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%?

>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 19 '06 #9

"Russ Rose" <ru******@hotmail.comwrote in message
news:PO******************************@comcast.com. ..

>
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>Russ Rose (ru******@hotmail.com) writes:
>>"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.google groups.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, es****@sommarskog.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

Dec 19 '06 #10

Russ Rose

"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in message
news:c8***************@newsread2.news.pas.earthlin k.net...

>
"Russ Rose" <ru******@hotmail.comwrote in message
news:PO******************************@comcast.com. ..
>>
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>>Russ Rose (ru******@hotmail.com) writes:
"Hurricane" <mg*******@gmail.comwrote in message
news:11**********************@t46g2000cwa.googl egroups.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, es****@sommarskog.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

Dec 19 '06 #11

Ed Murphy

Russ Rose wrote:

"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in message
news:c8***************@newsread2.news.pas.earthlin k.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.

Dec 19 '06 #12

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/m...S,SQL.90).aspx
--

Dec 19 '06 #13

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/m...S,SQL.90).aspx
--

Dec 19 '06 #14

Russ Rose (ru******@hotmail.com) writes:

"Erland Sommarskog" <es****@sommarskog.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, es****@sommarskog.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

Dec 19 '06 #15

"Russ Rose" <ru******@hotmail.comwrote in message
news:rI******************************@comcast.com. ..

>>
SQL has one data structure, tables.

And indexes are what exactly?

An index, not a data structure.

>
>>
Tables are not ordered.

Even clustered ones?

Not even clustered ones.

The engine is free to return data in whatever order it wishes.

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.

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

Dec 19 '06 #16

Russ Rose

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Russ Rose (ru******@hotmail.com) writes:
>"Erland Sommarskog" <es****@sommarskog.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.

'No guarantee' = 'means nothing' ? Fascinating...

>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 20 '06 #17

Russ Rose

"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in message
news:O6***************@newsread2.news.pas.earthlin k.net...

>
"Russ Rose" <ru******@hotmail.comwrote in message
news:rI******************************@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?

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

>>
>>>
Tables are not ordered.

Even clustered ones?

Not even clustered ones.

The engine is free to return data in whatever order it wishes.

Order of return has nothing to do with the physical order of a table. 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.

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

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

Dec 20 '06 #18

"Russ Rose" <ru******@hotmail.comwrote in message
news:Qa******************************@comcast.com. ..

>
"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in
message news:O6***************@newsread2.news.pas.earthlin k.net...
>>
"Russ Rose" <ru******@hotmail.comwrote in message
news:rI******************************@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.

>

Dec 20 '06 #19

"Russ Rose" <ru******@hotmail.comwrote in message
news:tI******************************@comcast.com. ..

>
'No guarantee' = 'means nothing' ? Fascinating...

If you're writing correct code, yes. You want to have code that you can
guarantee always returns the correct results, not "sometimes" or even "most
of the time"

>>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 20 '06 #20

Russ Rose (ru******@hotmail.com) writes:

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>Russ Rose (ru******@hotmail.com) writes:
>>"Erland Sommarskog" <es****@sommarskog.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.

'No guarantee' = 'means nothing' ? Fascinating...

Fascinating and fascinating... TOP 100 PERCENT ORDER BY in a view
definition means nothing because the ORDER BY in the view definition
is only there to give a specification to the TOP clause. But since
TOP 100 PERCENT means "all rows", it is redudant. And then there is
no meaning left to carry for the ORDER BY clause in the view
definition.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 20 '06 #21

Russ Rose (ru******@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, es****@sommarskog.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

Dec 20 '06 #22

Russ Rose

"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in message
news:pu****************@newsread2.news.pas.earthli nk.net...

>
"Russ Rose" <ru******@hotmail.comwrote in message
news:Qa******************************@comcast.com. ..
>>
"Greg D. Moore (Strider)" <mo****************@greenms.comwrote in
message news:O6***************@newsread2.news.pas.earthlin k.net...
>>>
"Russ Rose" <ru******@hotmail.comwrote in message
news:rI******************************@comcast.co m...
>
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.

Is it different stating as one word?

It has structure, defines structure, and contains data. Just like a table.

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

The physical order of the rows is maintained by the clustered index. Without
that it is an unordered "heap" table, which is what you seem intent on
describing all tables as.

Interesting, yet pointless discussion. Going on vacation. Hope you all have
a great holiday!

Dec 21 '06 #23