December 22, 2008 at 2:05 am
Maybe "beyond relational" should be read in the context "relational and more" ?
N 56°04'39.16"
E 12°55'05.25"
December 22, 2008 at 4:46 am
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
Change is inevitable... Change for the better is not.
December 22, 2008 at 5:21 am
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
December 22, 2008 at 5:25 am
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
Change is inevitable... Change for the better is not.
December 22, 2008 at 10:17 am
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.
December 22, 2008 at 12:59 pm
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]
December 23, 2008 at 10:27 am
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]
January 4, 2009 at 12:03 pm
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
January 5, 2009 at 10:23 am
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?
January 5, 2009 at 11:53 am
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.
January 5, 2009 at 7:44 pm
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
Change is inevitable... Change for the better is not.
January 6, 2009 at 5:40 am
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
January 6, 2009 at 11:12 am
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
January 6, 2009 at 11:27 am
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
January 6, 2009 at 11:41 am
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
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply