<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Sunny
»
Gain Space Using XML data type
38 posts, Page 2 of 4
««
1
2
3
4
»
»»
Gain Space Using XML data type
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, August 04, 2009 8:24 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526,
Visits: 13,864
It's an interesting look from a developer point of view. Compared to the power and speed of the relational engine, I think space savings are a minimal thing to worry about. Disk prices fall at a rate far beyond that of the improvements in CPU and memory when you compare price/performance.
I agree with many of the comments, that a well designed relational system is probably better, but this is an interesting solution and idea. If disk space matters a lot, perhaps it's worth considering, but I would recommend that you investigate some of the other suggestions first.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #764841
David McKinney
David McKinney
Posted Tuesday, August 04, 2009 8:37 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 2:02 AM
Points: 583,
Visits: 1,612
I think the merit in this article lies more in the techniques that are covered, rather than in the premise that it ultimately failed to prove.
The article does show a couple of good xquery examples in context - how to select a value from a node and incorporate it in a resultset, and how to modify the value of a node.
SELECT Rainfall.value('(/RT/RN[@H=15]/@H)[1]','tinyint') AS Hour
,Rainfall.value('(/RT/RN[@H=15]/@V)[1]','numeric(10,8)') AS Rainfall
UPDATE TP SET Rainfall.modify('replace value of (/RT/RN[@H="20"]/@V)[1] with "8.12345678" ')
All in all, not a bad article, well written and clear explanations, even if in some respects fundamentally flawed!
Post #764862
Jeff Moden
Jeff Moden
Posted Tuesday, August 04, 2009 3:51 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
Paul White (8/4/2009)
This script is closely based on that in the article.
I would strongly encourage the author of this article to try it out
Nice idea for an article, but size and speed are not XML strengths!
Paul
MythBuster extraordinaire... that script goes into my "Pork Chop Hall of Fame". Nice job, Paul.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #765157
Jeff Moden
Jeff Moden
Posted Tuesday, August 04, 2009 3:54 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
Steve Jones - Editor (8/4/2009)
It's an interesting look from a developer point of view. Compared to the power and speed of the relational engine, I think space savings are a minimal thing to worry about.
Space savings are certainly important to me especially when it comes to backups and restores but according to Paul's script... what space savings?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #765158
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, August 04, 2009 4:54 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526,
Visits: 13,864
Didn't test it, and perhaps there are none. In any case, I think it's been a nice debate/discussion of the technique.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #765174
Paul White
Paul White
Posted Tuesday, August 04, 2009 7:04 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990,
Visits: 10,578
Steve Jones - Editor (8/4/2009)
Didn't test it, and perhaps there are none. In any case, I think it's been a nice debate/discussion of the technique.
Hey Steve!
CTRL+C, CTRL+V, F5
(The test results from a run on my machine are also embedded in the comments).
Seriously though, XML is cool an' all - and I have been known to dabble some with XQuery, XPath and the rest, but it is easy to get carried away with it and apply XML to everything.
@G
2
: Good point. Sparse columns have some advantages in 2K8 that make them well worth a look too. The column_set column that gives you formatted XML based on just the columns with values in is very cool.
@Jeff: Thanks so much - have you noticed how many of my scripts have at least a passing resemblance to some you have posted in the past? Not entirely co-incidental
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #765191
Jeff Moden
Jeff Moden
Posted Tuesday, August 04, 2009 8:11 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
Paul White (8/4/2009)
@Jeff: Thanks so much - have you noticed how many of my scripts have at least a passing resemblance to some you have posted in the past? Not entirely co-incidental
Heh... oboy have I noticed. Thank you for the high compliment. The embedded comments are the berries, too! If someone can't figure out what you've done by looking at your code, it's because they simply can't read. Nicely done.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #765206
Sunny-138471
Sunny-138471
Posted Wednesday, August 05, 2009 4:52 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, January 18, 2013 2:22 PM
Points: 162,
Visits: 262
Thanks to all for the comments and suggestions. Really appreciate them.
Paul - Thanks for the script. I shall go through it during the weekend and let you know.
Post #765330
stevstr
stevstr
Posted Wednesday, August 05, 2009 5:37 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, November 02, 2012 12:23 PM
Points: 2,
Visits: 12
The application of XML in an OLTP system is not very scalable. The goal of any OLTP system is to increate the TPS. XML is not built for speed. Also consider that most OLTP sytems are highly normalized. Good luck joining from XML columns out to other tables and retaining your performance.
I see XML fields more applicable in static data scenarios. Also don't forget that SQL Server provides primary and secondary XML index functionality. This can be used to increase query performance over your XML, but great care is needed in designing your XML schema.
Steve
Post #765344
Goldie Lesser
Goldie Lesser
Posted Wednesday, August 05, 2009 7:41 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 477,
Visits: 1,366
Why bother with the database at all then? Just store the XML directly on the disk?
Seriously though, in today's day and age physical space is not usually an issue.
And, if you are going to be querying the data often, you will get better performance from a relational model.
The example with Candidates and Tests hit close to home.
We have a similar scenario and the original decision (made years ago) was to store the data as XML, for some of the reasons mentioned in the article.
But -- guess what? -- querying the data became slower and slower...
Eventually we had to write an APP that breaks the XML back into relational tables.
Post #765444
« Prev Topic
|
Next Topic »
38 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
