May 1, 2008 at 5:59 am
I am looking for a way to compress data I store in Xml columns (bear with me, as I know it wouldn't be Xml if it were compressed).
I have a table with, say, FK_ID, Xml and DateTime columns, where there would be multiple records for a given FK_ID...imagine a 'history table' for an ever changing piece of Xml where we need to keep a history of all the changes.
Actually, I don't need to keep the Xml as Xml in the history table, I could say put it in a VarBinary - as the latest Xml is available in the PK table.
So - what I am looking for is a way in T-SQL (I want to avoid using SQL CLR if possible) of taking some Xml datatype and compressing it into a VarBinary (or something else if necessary) such that if I wanted to return it using T-SQL I could decompress it JIT.
To date, my feeling is I can't do this in SQL 2005 (maybe not even in forthcoming 2008) - so will have to use SQL CLR - but if anyone could confirm this that would be great.
Thanks
May 21, 2008 at 2:07 am
Well, there is no built in functionality in SQL Server 2005 to achieve what you need, that's for sure. 🙂
SQL CLR is an option, but why not let the client application take care of that? Or are you using T-SQL to access XML data (judging by your post you're not)?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 21, 2008 at 2:16 am
Hi
I forgot I made this post as it went silent for so long...I have decided to use SQL# (sqlsharp.com) component which allows me to compress the Xml datatype into a varbinary and decompress it via a computed field should I need any SQL that requires the raw Xml (i.e. it decompress JIT).
I do use T-SQL to access the Xml but for the Xml I was planning on compressing I don't - as this particular Xml is archive/history copies.
But thanks for responding - it is good to know I deduced the same answer as you and hadn't missed a SQL Server feature!
Thanks
May 21, 2008 at 2:21 am
Well, as long as you're happy, I'm happy. 🙂
I figured, since you dislike SQL CLR, I wouldn't mention 3rd party tools at all. 😉
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 21, 2008 at 2:29 am
Cheers - but don't get me wrong - it isn't that I dislike SQL CLR - I am a .NET Dev in my real job :D, but I happen to be doing the DB right now. I just don't want to unnecessarily use CLR when T-SQL can do it...but I am not fully aware of when CLR beats SQL in performance for some of the stuff I am doing.
It would have been nice to have a built in compression function - but I am happy with SQL# - at the very least it saved me writing the code and so far the functionality I am using is within the free version of SQL# - bonus 😛
May 21, 2008 at 2:37 am
🙂
I'm not saying disliking SQL CLR is bad. 😉
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
December 3, 2010 at 4:15 am
I realize that it has been three years since you posted this question; but I thought that I would reply for the benefit of those who may come across this post.
I have had success with using cell-level encryption to "compress" XML data into a varbinary data type. The overall "compression" of the data that resulted was fairly good. My initial tests revealed that the row was about 1/3 less in size. In this case, since the use of encryption is to "compress" rather than protect the key management aspects of encryption does not need to be so stringent... just make sure to back up the key and/or certificate.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply