User Defined Function: order by won't work

Question

Post reply

User Defined Function: order by won't work

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

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

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

Maybe "beyond relational" should be read in the context "relational and more" ?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

GilaMonster (12/22/2008)
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

Ohhhhh.... that stuff..... gee... I wonder when they'll come out with an EDI data type? 😛

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Jeff Moden (12/22/2008)
Ohhhhh.... that stuff..... gee... I wonder when they'll come out with an EDI data type? 😛

CREATE TYPE EDI

EXTERNAL NAME EDI.[PointlessClrTypes.EDI] ;

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

GilaMonster (12/22/2008)
Jeff Moden (12/22/2008)
Ohhhhh.... that stuff..... gee... I wonder when they'll come out with an EDI data type? 😛
CREATE TYPE EDI
EXTERNAL NAME EDI.[PointlessClrTypes.EDI] ;

:hehe:

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

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 5

GilaMonster (12/21/2008)
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.

Thanks for the 'conversations' about this. My understanding (now) is that 2000 handled the order by in a function, but 2005/2008 does not, and that is by design - not just something I'm doing wrong. Since the result set needs to be sorted, and the application is what calls the function, it looks like the application is what will need to change. Thanks again to all who participated.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

kathyoshea (12/22/2008)
My understanding (now) is that 2000 handled the order by in a function, but 2005/2008 does not, and that is by design - not just something I'm doing wrong. Since the result set needs to be sorted, and the application is what calls the function, it looks like the application is what will need to change.

Just so.

[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]

J-440512 SSCertifiable Points: 6285 More actions · Answer 7

Maybe "beyond relational" should be read in the context "relational and more" ?

Yup. Rather than just limiting ourselves to relational, let's just add non relational things... On top of XML, let's add the PhoneBook type, movie type, music type, booktype, etc.

[font="Courier New"]SQRT(-1) -> pi : "be rational"

pi -> SQRT(-1): "get real"[/font]

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

For what it's worth, the only way I've been able to get around the ORDER BY problem in derived tables and functions is to create an index and then use an index hint in the query.

It's a very ugly solution and could result in bad performance. Ugh!

Todd Fifield

Andrew Gothard-467944 Hall of Fame Points: 3908 More actions · Answer 9

RBarryYoung (12/21/2008)
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.

Ah - going for the 'Agile' "durrhhh - - I goddanidea we can just have one table wid an identity and anudder XML column. Dat wud be gud. My unit test worked wid all 3 test lines and it wuz fast" market?

Chris Harshman SSC-Forever Points: 42179 More actions · Answer 10

Andrew Gothard (1/5/2009)
RBarryYoung (12/21/2008)
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.
Ah - going for the 'Agile' "durrhhh - - I goddanidea we can just have one table wid an identity and anudder XML column. Dat wud be gud. My unit test worked wid all 3 test lines and it wuz fast" market?

I doubt this type of thing will catch on very much, for the same or simmilar reasons that object oriented databases didn't catch on very much. You can represent everything that these models do in a relational model, but you can't represent everything that a relational model can do in an object oriented database or XML hierarchy.

Don't get me wrong, XML does have some purposes, especially when transferring hierarchichal data between different systems, but not as a permanant data storage mechanism. We are esentially breaking normal forms when we store XML in the database for any reason other than a log table that's holding XML messages for a system.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

tfifield (1/4/2009)
For what it's worth, the only way I've been able to get around the ORDER BY problem in derived tables and functions is to create an index and then use an index hint in the query.
It's a very ugly solution and could result in bad performance. Ugh!
Todd Fifield

Oh, be careful, Todd. Using an INDEX HINT to do a sort in SELECTs will eventually lead to bad data because it doesn't always work for SELECTs. For SELECTs, you really have to use ORDER BY to guarantee the correct return order.

For certain UPDATEs with a forced scan on the clustered index, it'll work. But for SELECTs it just isn't reliable even if it looks that way to you in the short term.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Jeff Moden (1/5/2009)
For certain UPDATEs with a forced scan on the clustered index, it'll work. But for SELECTs it just isn't reliable even if it looks that way to you in the short term.

And even with the updates, there are things that mess the order up (partitioned tables and parallelism are the main ones)

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

tfifield SSCrazy Eights Points: 9655 More actions · Answer 13

Gail,

Wow! I never thought about the parallel problem. It gets uglier all the time. I'd have to tweak MAXDOP in order to guarantee that it was done in sequence. I also hadn't thought about partitioned tables.

I'll have to come up with another solution. I've only used the technique a few times when I was in a bit of a bind to force the select order when concatenating string data.

Todd Fifield

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

tfifield (1/6/2009)
Gail,
Wow! I never thought about the parallel problem. It gets uglier all the time. I'd have to tweak MAXDOP in order to guarantee that it was done in sequence.

Even that won't guarantee it. The only way to absolutely guarantee an order is to use an order by.

You may be able to get a desired order using an index hint under very specific conditions on the current version of SQL, but that's just an 'accident' of the way the storage engine and query processor work. It may change in the next version or even next service pack.

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

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 15

This was my solution:

Create a new multi-statement table-valued function.

Select the data and use the order by in the loading of the table that is returned.

This works in all the testing I've done. I would guess that it's not good for performance, but the circumstances make sorted data a higher priority than speed. In most cases, I'm selecting from a view.

Any comments on this are welcome!

Thanks