[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Order clause in a view differences between 2000 and 2005

E®!k Visser

3/23/2007 8:18:00 AM

Hi all,

I have a problem with setitng an order clause in a view.

SELECT ID, SURNAME FROM CUSTOMERS
1 Visser
2 Lurker
3 Gates

CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS ORDER
BY SURNAME

SELECT ID, SURNAME FROM CUSTOMERS

Now SqlServer 2000 gives
3 Gates
2 Lurker
1 Visser

But on SqlServer 2005 I get this
1 Visser
2 Lurker
3 Gates

I had to look several times to be sure I was not having a bad dream or
someting.. did not take drugs from more than a week <g> and tried again but
got the same result. So what is wrong here. Any suggestions would be
appreciated.


Erik
10 Answers

mladjo

3/23/2007 8:26:00 AM

0

Hi.

Try to use ASC or DESC statement:

CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS ORDER
BY SURNAME ASC


Mladen.


E®!k Visser

3/23/2007 8:37:00 AM

0

Mladen,

Thanks very much for you reply.
Unfortunataly if I add ASC to the order clause, it is removed by the
querybuilder of SQL managemnt Studio when I prress save.
But also adding DESC does not affect the result in any way.

Erik


"mladjo" <mladjocelik@yahoo.com> schreef in bericht
news:eu0336$b2b$1@news.metronet.hr...
> Hi.
>
> Try to use ASC or DESC statement:
>
> CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS
> ORDER
> BY SURNAME ASC
>
>
> Mladen.
>


Tibor Karaszi

3/23/2007 8:41:00 AM

0

A view is not ordered, and has never been (a view is supposed to behave like a table, which isn't
ordered). The only purpose of ORDER BY in a view is in conjunction with TOP to return the correct
rows (like 5 most expensive books), not in any particular order. The optimizer in 2000 wasn't smart
enough to figure out that both ORDER BY and TOP can be eliminated when you have TOP 100 PERCENT. The
optimizer in 2005 is smarter. In other words, you need ORDER BY when you select *from* the view.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"E®!k Visser" <burod004@planet.nl> wrote in message news:O%23zT6OSbHHA.1244@TK2MSFTNGP04.phx.gbl...
> Hi all,
>
> I have a problem with setitng an order clause in a view.
>
> SELECT ID, SURNAME FROM CUSTOMERS
> 1 Visser
> 2 Lurker
> 3 Gates
>
> CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS ORDER
> BY SURNAME
>
> SELECT ID, SURNAME FROM CUSTOMERS
>
> Now SqlServer 2000 gives
> 3 Gates
> 2 Lurker
> 1 Visser
>
> But on SqlServer 2005 I get this
> 1 Visser
> 2 Lurker
> 3 Gates
>
> I had to look several times to be sure I was not having a bad dream or
> someting.. did not take drugs from more than a week <g> and tried again but
> got the same result. So what is wrong here. Any suggestions would be
> appreciated.
>
>
> Erik
>
>
>

E®!k Visser

3/23/2007 9:07:00 AM

0

Tibor,
Thank you for your response. So basicly what you are telling me is that
right now I do not do anything wrong, I do not have a configuration problem,
but
I was doing this wrong for the past 4-5 years and when my customers upgrade
to 2005 there will be major problems, even if they run on Compatibility
level 80

I cannot say I am very amused by this......

Erik


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef
in bericht news:es5L6bSbHHA.1216@TK2MSFTNGP03.phx.gbl...
>A view is not ordered, and has never been (a view is supposed to behave
>like a table, which isn't ordered). The only purpose of ORDER BY in a view
>is in conjunction with TOP to return the correct rows (like 5 most
>expensive books), not in any particular order. The optimizer in 2000 wasn't
>smart enough to figure out that both ORDER BY and TOP can be eliminated
>when you have TOP 100 PERCENT. The optimizer in 2005 is smarter. In other
>words, you need ORDER BY when you select *from* the view.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "E®!k Visser" <burod004@planet.nl> wrote in message
> news:O%23zT6OSbHHA.1244@TK2MSFTNGP04.phx.gbl...
>> Hi all,
>>
>> I have a problem with setitng an order clause in a view.
>>
>> SELECT ID, SURNAME FROM CUSTOMERS
>> 1 Visser
>> 2 Lurker
>> 3 Gates
>>
>> CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS
>> ORDER
>> BY SURNAME
>>
>> SELECT ID, SURNAME FROM CUSTOMERS
>>
>> Now SqlServer 2000 gives
>> 3 Gates
>> 2 Lurker
>> 1 Visser
>>
>> But on SqlServer 2005 I get this
>> 1 Visser
>> 2 Lurker
>> 3 Gates
>>
>> I had to look several times to be sure I was not having a bad dream or
>> someting.. did not take drugs from more than a week <g> and tried again
>> but
>> got the same result. So what is wrong here. Any suggestions would be
>> appreciated.
>>
>>
>> Erik
>>
>>
>>
>


Tony Rogerson

3/23/2007 9:17:00 AM

0

> I was doing this wrong for the past 4-5 years and when my customers
> upgrade to 2005 there will be major problems, even if they run on
> Compatibility level 80
>
> I cannot say I am very amused by this......

Unfortunetely you have relied on undocumented behaviour.

There are a few other undocumented behaviours people are 'relying' on as
well, for instance the string concatenation stuff.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to... (Ramblings from the field from a
SQL consultant)
http://sqlser... (UK SQL User Community)


"E®!k Visser" <burod004@planet.nl> wrote in message
news:%23mvjOqSbHHA.3272@TK2MSFTNGP03.phx.gbl...
> Tibor,
> Thank you for your response. So basicly what you are telling me is that
> right now I do not do anything wrong, I do not have a configuration
> problem, but
> I was doing this wrong for the past 4-5 years and when my customers
> upgrade to 2005 there will be major problems, even if they run on
> Compatibility level 80
>
> I cannot say I am very amused by this......
>
> Erik
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef
> in bericht news:es5L6bSbHHA.1216@TK2MSFTNGP03.phx.gbl...
>>A view is not ordered, and has never been (a view is supposed to behave
>>like a table, which isn't ordered). The only purpose of ORDER BY in a view
>>is in conjunction with TOP to return the correct rows (like 5 most
>>expensive books), not in any particular order. The optimizer in 2000
>>wasn't smart enough to figure out that both ORDER BY and TOP can be
>>eliminated when you have TOP 100 PERCENT. The optimizer in 2005 is
>>smarter. In other words, you need ORDER BY when you select *from* the
>>view.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://sqlblog.com/blogs/tib...
>>
>>
>> "E®!k Visser" <burod004@planet.nl> wrote in message
>> news:O%23zT6OSbHHA.1244@TK2MSFTNGP04.phx.gbl...
>>> Hi all,
>>>
>>> I have a problem with setitng an order clause in a view.
>>>
>>> SELECT ID, SURNAME FROM CUSTOMERS
>>> 1 Visser
>>> 2 Lurker
>>> 3 Gates
>>>
>>> CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS
>>> ORDER
>>> BY SURNAME
>>>
>>> SELECT ID, SURNAME FROM CUSTOMERS
>>>
>>> Now SqlServer 2000 gives
>>> 3 Gates
>>> 2 Lurker
>>> 1 Visser
>>>
>>> But on SqlServer 2005 I get this
>>> 1 Visser
>>> 2 Lurker
>>> 3 Gates
>>>
>>> I had to look several times to be sure I was not having a bad dream or
>>> someting.. did not take drugs from more than a week <g> and tried again
>>> but
>>> got the same result. So what is wrong here. Any suggestions would be
>>> appreciated.
>>>
>>>
>>> Erik
>>>
>>>
>>>
>>
>
>

David Portas

3/23/2007 9:32:00 AM

0

On 23 Mar, 09:06, "E®!k Visser" <burod...@planet.nl> wrote:
> Tibor,
> Thank you for your response. So basicly what you are telling me is that
> right now I do not do anything wrong, I do not have a configuration problem,
> but
> I was doing this wrong for the past 4-5 years and when my customers upgrade
> to 2005 there will be major problems, even if they run on Compatibility
> level 80
>
> I cannot say I am very amused by this......
>
> Erik
>

You were doing something wrong. You were assuming that a query like
this:

SELECT id, surname FROM YourView;

would return a predictable order, even though it's clear from the
documentation and implicit in SQL's data model that the ordering of
queries without ORDER BY is undefined.

I admit that the TOP syntax is confusing by design but at least
Microsoft documented the correct behaviour and it has remained
consistent in 2000 and 2005. Unfortunately not everyone reads
documentation and apparently too many people jumped to a bogus
conclusion about what "ORDER BY" means in a 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/librar...(en-US,SQL.90).aspx
--

E®!k Visser

3/23/2007 9:50:00 AM

0

Ton,
Thanks you for your comment !

> Unfortunetely you have relied on undocumented behaviour.
I do consider this a cheap answer ( I mean not from you, but from MS )
The top 100 percent is added automaticly if you use the querybuilder. I
would never have come to that idea myself. So it might be undocumented,
they have suggested to use it themselfs!

And OK, MS can change undocumented behaviour. But at least make sure running
in compatibility mode 80 works like SQL2000 on this.

Erik

>
> There are a few other undocumented behaviours people are 'relying' on as
> well, for instance the string concatenation stuff.
>
> Tony.
>
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/to... (Ramblings from the field from
> a SQL consultant)
> http://sqlser... (UK SQL User Community)
>
>
> "E®!k Visser" <burod004@planet.nl> wrote in message
> news:%23mvjOqSbHHA.3272@TK2MSFTNGP03.phx.gbl...
>> Tibor,
>> Thank you for your response. So basicly what you are telling me is that
>> right now I do not do anything wrong, I do not have a configuration
>> problem, but
>> I was doing this wrong for the past 4-5 years and when my customers
>> upgrade to 2005 there will be major problems, even if they run on
>> Compatibility level 80
>>
>> I cannot say I am very amused by this......
>>
>> Erik
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
>> schreef in bericht news:es5L6bSbHHA.1216@TK2MSFTNGP03.phx.gbl...
>>>A view is not ordered, and has never been (a view is supposed to behave
>>>like a table, which isn't ordered). The only purpose of ORDER BY in a
>>>view is in conjunction with TOP to return the correct rows (like 5 most
>>>expensive books), not in any particular order. The optimizer in 2000
>>>wasn't smart enough to figure out that both ORDER BY and TOP can be
>>>eliminated when you have TOP 100 PERCENT. The optimizer in 2005 is
>>>smarter. In other words, you need ORDER BY when you select *from* the
>>>view.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/d...
>>> http://sqlblog.com/blogs/tib...
>>>
>>>
>>> "E®!k Visser" <burod004@planet.nl> wrote in message
>>> news:O%23zT6OSbHHA.1244@TK2MSFTNGP04.phx.gbl...
>>>> Hi all,
>>>>
>>>> I have a problem with setitng an order clause in a view.
>>>>
>>>> SELECT ID, SURNAME FROM CUSTOMERS
>>>> 1 Visser
>>>> 2 Lurker
>>>> 3 Gates
>>>>
>>>> CREATE VIEW Test AS SELECT TOP 100 PERCENT ID, SURNAME FROM CUSTOMERS
>>>> ORDER
>>>> BY SURNAME
>>>>
>>>> SELECT ID, SURNAME FROM CUSTOMERS
>>>>
>>>> Now SqlServer 2000 gives
>>>> 3 Gates
>>>> 2 Lurker
>>>> 1 Visser
>>>>
>>>> But on SqlServer 2005 I get this
>>>> 1 Visser
>>>> 2 Lurker
>>>> 3 Gates
>>>>
>>>> I had to look several times to be sure I was not having a bad dream or
>>>> someting.. did not take drugs from more than a week <g> and tried again
>>>> but
>>>> got the same result. So what is wrong here. Any suggestions would be
>>>> appreciated.
>>>>
>>>>
>>>> Erik
>>>>
>>>>
>>>>
>>>
>>
>>
>


E®!k Visser

3/23/2007 10:02:00 AM

0

David,

Thans for your reaction.

>> Tibor,
>> Thank you for your response. So basicly what you are telling me is that
>> right now I do not do anything wrong, I do not have a configuration
>> problem,
>> but
>> I was doing this wrong for the past 4-5 years and when my customers
>> upgrade
>> to 2005 there will be major problems, even if they run on Compatibility
>> level 80
>>
>> I cannot say I am very amused by this......
>>
>> Erik
>

>You were doing something wrong. You were assuming that a query like
>this:
>
>SELECT id, surname FROM YourView;
>
>would return a predictable order, even though it's clear from the
>documentation and implicit in SQL's data model that the ordering of
>queries without ORDER BY is undefined.

I did not come up the idea to use TOP 100 PRECENT, but it is inserted by the
querybuilder automaticly. So MS suggests to use it!


>I admit that the TOP syntax is confusing by design but at least
>Microsoft documented the correct behaviour and it has remained
>consistent in 2000 and 2005. Unfortunately not everyone reads
>documentation and apparently too many people jumped to a bogus
>conclusion about what "ORDER BY" means in a view.

I more or less do agree with you on this, but , there is a lot of
documentation (and not only on SQL Server, but also on the .NET framework,
on the Win32 SDK etc) and if I have to read everything around, I will not
have time to make a living. On the other hand, documentation is always
behind the features. There will always be features not documented on various
reasons but there are very little documented features that do not exists in
practice.

Everything stops if a querybuilderautomaticly inserts 'ondocumented
behaviour'

regards,

Erik Visser




--
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/librar...(en-US,SQL.90).aspx
--

Dan Guzman

3/23/2007 1:10:00 PM

0

> I did not come up the idea to use TOP 100 PRECENT, but it is inserted by
> the querybuilder automaticly. So MS suggests to use it!

Just one more reason why real database developers shouldn't use Enterprise
Manager ;-)

Seriously though, the query builder is mostly intended to assist novice
users build SQL statements. There are many Transact-SQL constructs that the
query builder doesn't support. I recommend that serious developers use
Query Analyzer rather than EM for development tasks. As David mentioned, it
is not intuitive that the view's ORDER BY clause applies only to the TOP
clause and not the result set sequence.

Consider this a lesson learned that the only way to guarantee result set
sequence is to specify ORDER BY in the outermost query. I'm sorry that you
had to learn the hard way.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"E®!k Visser" <burod004@planet.nl> wrote in message
news:ugs5AJTbHHA.3960@TK2MSFTNGP04.phx.gbl...
> David,
>
> Thans for your reaction.
>
>>> Tibor,
>>> Thank you for your response. So basicly what you are telling me is that
>>> right now I do not do anything wrong, I do not have a configuration
>>> problem,
>>> but
>>> I was doing this wrong for the past 4-5 years and when my customers
>>> upgrade
>>> to 2005 there will be major problems, even if they run on Compatibility
>>> level 80
>>>
>>> I cannot say I am very amused by this......
>>>
>>> Erik
>>
>
>>You were doing something wrong. You were assuming that a query like
>>this:
>>
>>SELECT id, surname FROM YourView;
>>
>>would return a predictable order, even though it's clear from the
>>documentation and implicit in SQL's data model that the ordering of
>>queries without ORDER BY is undefined.
>
> I did not come up the idea to use TOP 100 PRECENT, but it is inserted by
> the querybuilder automaticly. So MS suggests to use it!
>
>
>>I admit that the TOP syntax is confusing by design but at least
>>Microsoft documented the correct behaviour and it has remained
>>consistent in 2000 and 2005. Unfortunately not everyone reads
>>documentation and apparently too many people jumped to a bogus
>>conclusion about what "ORDER BY" means in a view.
>
> I more or less do agree with you on this, but , there is a lot of
> documentation (and not only on SQL Server, but also on the .NET framework,
> on the Win32 SDK etc) and if I have to read everything around, I will not
> have time to make a living. On the other hand, documentation is always
> behind the features. There will always be features not documented on
> various reasons but there are very little documented features that do not
> exists in practice.
>
> Everything stops if a querybuilderautomaticly inserts 'ondocumented
> behaviour'
>
> regards,
>
> Erik Visser
>
>
>
>
> --
> 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/librar...(en-US,SQL.90).aspx
> --
>
>

Hugo Kornelis

3/23/2007 11:59:00 PM

0

On Fri, 23 Mar 2007 10:50:05 +0100, E®!k Visser wrote:

>Ton,
>Thanks you for your comment !
>
>> Unfortunetely you have relied on undocumented behaviour.
>I do consider this a cheap answer ( I mean not from you, but from MS )
>The top 100 percent is added automaticly if you use the querybuilder.

Hi Erik,

A very good point. The query designer is crap, and this is but one of
the reasons I write this.

What's worse - despite many comments on this awful "functionality", the
query designer STILL adds the "TOP 100 PERCENT" in SQL Server Management
Studio (the SQL Server 2005 version of the tool), even though it doesn't
work anymore!!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...