Post reply

User Defined Function: order by won't work

  • December 19, 2008 at 3:59 pm

    #197622

    I have a table-valued user defined function that returns data selected from a view. In SQL 2000, the returned data was sorted by an 'order by' within the function, but in SQL 2005, the data is returned un-sorted.

    Thanks in advance for any help on this.

  • December 19, 2008 at 4:07 pm

    #915678

    Add an ORDER BY clause to the SELECT statement that is calling the function.

  • December 19, 2008 at 4:11 pm

    #915679

    The function is called from the application, which provides the input value. So, adding the order by at that point works (in testing) but can't be done in my actual situation. Is there something in 2005 that won't see the order by?

  • December 19, 2008 at 6:16 pm

    #915692

    Please post the calling and called code.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 20, 2008 at 12:38 am

    #915714

    kathyoshea (12/19/2008)

    Is there something in 2005 that won't see the order by?

    Order by is only valid in the outermost select statement, ie the one calling the function. If it's any other level, SQL 2005/2008 can and will ignore it. This is by design.

    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

  • December 20, 2008 at 7:42 pm

    #915798

    But it does have to respect the interior ORDER BY's for local TOP clauses, right? Granted, it can disorder the result set after that...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 21, 2008 at 2:07 am

    #915810

    RBarryYoung (12/20/2008)

    But it does have to respect the interior ORDER BY's for local TOP clauses, right?

    Yes, providing the TOP is row-limiting. Currently, that just means not TOP 100 %, but it's possible in the future that SQL will be smart enough to know that something like TOP 9999999999 is not going to limit rows because there are only 250 rows in the resultset at that point.

    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

  • December 21, 2008 at 7:17 am

    #915833

    Or perhaps, given SQL server's new emphasis on "Beyond Relational", in the future it will understand "TOP 100%" to mean that we really do want it to sort the data.

    Or maybe we just need a new hint for Order By: "DO_IT!" 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 21, 2008 at 7:25 am

    #915836

    RBarryYoung (12/21/2008)

    Or perhaps in the future it will understand "TOP 100%" to mean that we really do want it to sort the data.

    Nope. 2000 worked that way, 2005 and 2008 do not. All indications are that the optimiser will ignore order bys without row limitations in the future as well.

    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

  • December 21, 2008 at 7:49 am

    #915840

    Yes, but Microsoft is the one who started this "Beyond Relational" campaign with SQL Server 2008. And it is Christmas... 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 21, 2008 at 8:03 am

    #915843

    RBarryYoung (12/21/2008)

    Yes, but Microsoft is the one who started this "Beyond Relational" campaign with SQL Server 2008.

    Meaningless marketing drivel. I doubt the people who though that up even know what relational is.

    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

  • December 21, 2008 at 8:07 am

    #915846

    GilaMonster (12/21/2008)

    RBarryYoung (12/21/2008)

    Yes, but Microsoft is the one who started this "Beyond Relational" campaign with SQL Server 2008.

    Meaningless marketing drivel. I doubt the people who though that up even know what relational is.

    Yeah, but the people who were talking about it at PASS certainly do.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 21, 2008 at 3:01 pm

    #915909

    So, what is it, supposedly?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 21, 2008 at 4:59 pm

    #915914

    Well, IMHO it's just a way to positively spin why they think that it's OK to add hierarchical repeating group data BLOBs like XML into our nice relational database.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 12:22 am

    #915974

    Jeff Moden (12/21/2008)

    So, what is it, supposedly?

    Spatial, xml, hierarchy, etc. The 'rich' data types that some bright spark in marketing thought meant SQL was evolving beyond a relational database. I'd like to find the bright people that came up with that and point out the definition of 'relational database' to them

    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 - 1 through 15 (of 40 total)

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