View is not ordered. Why?

  • GSquared

    SSC Guru

    Points: 260824

    evald (12/14/2010)


    GilaMonster (12/14/2010)

    This is clearly documented. From Books Online:

    When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

    http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.90%29.aspx

    Yes, even bugs are well documented.

    Microsoft is saying: "Look man, for some obscure implementation reason of my engine i cannot guarantee you that the order by will work".

    It seems that Microsoft agree with me

    http://support.microsoft.com/kb/926292

    I'm going to verify if this hotfix is installed on my sql server and also i'll verify on a sql server 2008

    GilaMonster (12/14/2010)

    Views don't store data. They are only saved select statements. Tables have no defined order either. An order by on a view will have no effect at all on how data is stored in the tables that view depends upon. Order By is a clause in a select that orders rows returned by the query that it is part of. Nothing else.

    Yes, but i have noticed that it takes some times to "update" a view on sql server and i think that the only reason of this delay is that the view is somewhat cached in some place by the engine.

    I hate to say this, but it looks like ego is getting in your way here.

    "Yes, even the bugs are well documented." Really? If you honestly consider this a bug, if you really think that you know how this should work better than EVERY EXPERIENCED DBA who has replied to this thread, AND the devs at Microsoft, and even the devs at Oracle (yes, I checked your statements on that, and they are essentially incorrect, per Oracle), then you really need to check your ego at the door.

    I'm not going to grace this thread with any further coddling of you at this point. You are refusing to learn how to do the job correctly, and assuming that everyone who disagrees with you is wrong, despite the fact that EVERYONE disagrees with you. Until you are ready to actually learn how to do the job, instead of just whining about how things don't work the way you want them to, there's no point in trying to educate you. You can teach an old dog new tricks, but you can't teach anything to anyone who refuses to learn.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    GSquared (12/15/2010)


    evald (12/14/2010)


    GilaMonster (12/14/2010)

    This is clearly documented. From Books Online:

    When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

    http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.90%29.aspx

    Yes, even bugs are well documented.

    Microsoft is saying: "Look man, for some obscure implementation reason of my engine i cannot guarantee you that the order by will work".

    It seems that Microsoft agree with me

    http://support.microsoft.com/kb/926292

    I'm going to verify if this hotfix is installed on my sql server and also i'll verify on a sql server 2008

    GilaMonster (12/14/2010)

    Views don't store data. They are only saved select statements. Tables have no defined order either. An order by on a view will have no effect at all on how data is stored in the tables that view depends upon. Order By is a clause in a select that orders rows returned by the query that it is part of. Nothing else.

    Yes, but i have noticed that it takes some times to "update" a view on sql server and i think that the only reason of this delay is that the view is somewhat cached in some place by the engine.

    I hate to say this, but it looks like ego is getting in your way here.

    "Yes, even the bugs are well documented." Really? If you honestly consider this a bug, if you really think that you know how this should work better than EVERY EXPERIENCED DBA who has replied to this thread, AND the devs at Microsoft, and even the devs at Oracle (yes, I checked your statements on that, and they are essentially incorrect, per Oracle), then you really need to check your ego at the door.

    I'm not going to grace this thread with any further coddling of you at this point. You are refusing to learn how to do the job correctly, and assuming that everyone who disagrees with you is wrong, despite the fact that EVERYONE disagrees with you. Until you are ready to actually learn how to do the job, instead of just whining about how things don't work the way you want them to, there's no point in trying to educate you. You can teach an old dog new tricks, but you can't teach anything to anyone who refuses to learn.

    +1

  • evald

    SSCrazy

    Points: 2115

    GSquared (12/15/2010)


    I hate to say this, but it looks like ego is getting in your way here.

    "Yes, even the bugs are well documented." Really? If you honestly consider this a bug, if you really think that you know how this should work better than EVERY EXPERIENCED DBA who has replied to this thread, AND the devs at Microsoft, and even the devs at Oracle (yes, I checked your statements on that, and they are essentially incorrect, per Oracle), then you really need to check your ego at the door.

    I'm not going to grace this thread with any further coddling of you at this point. You are refusing to learn how to do the job correctly, and assuming that everyone who disagrees with you is wrong, despite the fact that EVERYONE disagrees with you. Until you are ready to actually learn how to do the job, instead of just whining about how things don't work the way you want them to, there's no point in trying to educate you. You can teach an old dog new tricks, but you can't teach anything to anyone who refuses to learn.

    Feel free and say whatever you want but i never said something about oracle. Read the whole discussion.

    I'm not saying that the experienced dba here are telling wrong things or ms.

    I'm just trying to say that if the order clause is not working in some cases for some reason than its useless and confusing give the possiblity to the people to use it. This is my point of view which could be wrong but you have to respect it.

    Anyway ,Thanks to all the other people that were trying to explain me the reasons.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    evald (12/15/2010)


    I'm just trying to say that if the order clause is not working in some cases for some reason than its useless and confusing give the possiblity to the people to use it. This is my point of view which could be wrong but you have to respect it.

    Your point of view is wrong.

    It's not a case where Order By sometimes works and sometimes doesn't for some odd and unexplained reasons. That would indeed be a bug.

    What's happening here is clearly documented and clearly defined.

    An Order By on the outer-most query (the one that actually returns data) enforces order.

    An Order By anywhere else (subquery, function, view) does not enforce order.

    Simple as that.

    No mystery. No confusion. No unexplained reasons.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • evald

    SSCrazy

    Points: 2115

    GilaMonster (12/15/2010)

    An Order By anywhere else (subquery, function, view) does not enforce order.

    I HAVE UNDERSTOOD THIS. I'M TRYING TO SAY THAT SINCE DOES NOT ENFORCE ORDER, IT IS CONFUSING!!!

    Regards

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    evald (12/15/2010)


    GilaMonster (12/15/2010)

    An Order By anywhere else (subquery, function, view) does not enforce order.

    I HAVE UNDERSTOOD THIS. I'M TRYING TO SAY THAT SINCE DOES NOT ENFORCE ORDER, IT IS CONFUSING!!!

    Regards

    Ok, then what are you still confused about? Don't know how to detect it in the future?

  • Paul White

    SSC Guru

    Points: 150442

    evald (12/15/2010)


    ...I'm just trying to say that if the order clause is not working in some cases for some reason than its useless and confusing give the possiblity to the people to use it...

    I have some sympathy with this.

    The following query plan includes a sort:

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1;

    This does not:

    SELECT SubQuery.AddressID,

    SubQuery.AddressLine1

    FROM (

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1

    ) SubQuery;

    Most experienced SQL Server people are not surprised by this (any more), but I can see how someone new to SQL might find it confusing.

    Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.

    Paul

  • evald

    SSCrazy

    Points: 2115

    Paul White NZ (12/15/2010)


    evald (12/15/2010)


    ...I'm just trying to say that if the order clause is not working in some cases for some reason than its useless and confusing give the possiblity to the people to use it...

    I have some sympathy with this.

    The following query plan includes a sort:

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1;

    This does not:

    SELECT SubQuery.AddressID,

    SubQuery.AddressLine1

    FROM (

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1

    ) SubQuery;

    Most experienced SQL Server people are not surprised by this (any more), but I can see how someone new to SQL might find it confusing.

    Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.

    Paul

    I would say Pual that a warning helps a lot.

    And i would say also that if you ask 10 dba about this maybe only one knows all this story.

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    evald (12/15/2010)


    Paul White NZ (12/15/2010)


    evald (12/15/2010)


    ...I'm just trying to say that if the order clause is not working in some cases for some reason than its useless and confusing give the possiblity to the people to use it...

    I have some sympathy with this.

    The following query plan includes a sort:

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1;

    This does not:

    SELECT SubQuery.AddressID,

    SubQuery.AddressLine1

    FROM (

    SELECT TOP (100) PERCENT

    A.AddressID,

    A.AddressLine1

    FROM AdventureWorks.Person.Address A

    ORDER BY

    A.AddressLine1

    ) SubQuery;

    Most experienced SQL Server people are not surprised by this (any more), but I can see how someone new to SQL might find it confusing.

    Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.

    Paul

    I would say Pual that a warning helps a lot.

    And i would say also that if you ask 10 dba about this maybe only one knows all this story.

    Actually the people above in this post DO KNOW WHAT THEY ARE TALKING ABOUT.

    Sure all 'bad coding warning' for this type of code should be given an 'ID-10-T' warning

    😛

    PEPCAK !

  • Paul White

    SSC Guru

    Points: 150442

    evald (12/15/2010)


    I would say Paul that a warning helps a lot.

    Perhaps so. Sadly it wasn't practical so we have to live with it.

    And i would say also that if you ask 10 dba about this maybe only one knows all this story.

    Probably less than that know the full story. I trust you will do your part in educating the people you meet, now you know 🙂

  • evald

    SSCrazy

    Points: 2115

    Paul White NZ (12/15/2010)


    Probably less than that know the full story. I trust you will do your part in educating the people you meet, now you know 🙂

    Rarely i meet real dba(i mean people that work only as dba) because i'm a developer but on the first occasion i will ask if they know the case when the "order by" is not guarantee that works.:-)

    Have a nice day.

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    evald (12/16/2010)


    Paul White NZ (12/15/2010)


    Probably less than that know the full story. I trust you will do your part in educating the people you meet, now you know 🙂

    Rarely i meet real dba(i mean people that work only as dba) because i'm a developer but on the first occasion i will ask if they know the case when the "order by" is not guarantee that works.:-)

    Have a nice day.

    You need to get out more 🙂

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    evald (12/16/2010)


    Paul White NZ (12/15/2010)


    Probably less than that know the full story. I trust you will do your part in educating the people you meet, now you know 🙂

    Rarely i meet real dba(i mean people that work only as dba) because i'm a developer but on the first occasion i will ask if they know the case when the "order by" is not guarantee that works.:-)

    Have a nice day.

    There are a lot of people that think that if you do a "select * from MyTable" that it will return data in order by the clustered index. It might and probably usually will, but it is not guaranteed to.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Michael Valentine Jones (12/16/2010)


    There are a lot of people that think that if you do a "select * from MyTable" that it will return data in order by the clustered index. It might and probably usually will, but it is not guaranteed to.

    Alright, I'll be the one to bite into that apple. SELECT * FROM MyTable performs a Clustered Index Scan, would not initiate a sort of any kind without outside influence, and thus would kick out records in the order read.

    AFAIK.

    Where's this from?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Gail Shaw

    SSC Guru

    Points: 1004474

    1) Parallelism

    2) Allocation order scan with a fragmented index.

    3) Advanced scan (merry go round). This one's hard and I've never personally been able to repo it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 63 total)

You must be logged in to reply to this topic. Login to reply