December 19, 2008 at 3:59 pm
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
Add an ORDER BY clause to the SELECT statement that is calling the function.
December 19, 2008 at 4:11 pm
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
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
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
December 20, 2008 at 7:42 pm
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
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
December 21, 2008 at 7:17 am
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
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
December 21, 2008 at 7:49 am
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
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
December 21, 2008 at 8:07 am
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
So, what is it, supposedly?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2008 at 4:59 pm
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
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
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply