Compress text column on SQL2000

I am trying to compress text/image column on a table on MSSQL200 Enterprise Ed.
When will compress when write to the table and uncompress when user retrieve
the data. Anyone can please suggest me how to or any tool to do this.
You need to do the compression and uncompression in the client app or middle
tier and not the database server.
Andrew J. Kelly SQL MVP
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
>I am trying to compress text/image column on a table on MSSQL200 Enterprise
>Ed.
> When will compress when write to the table and uncompress when user
> retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||To add to Andrew's response, I recommend that you test very heavily before
implementing this in a production environment; I've done fairly extensive
testing of various on-the-fly .NET compression libraries for the purpose of
compressing LOB data on the way in and out of the database, and found that
instead of improving performance as I expected (due to lowering disk IOs and
network bandwidth required to retrieve the data), there was instead a
moderate degredation due to the extra processor strain on the middle tier.
If you do find a way to improve performance using compression, I would be
very interested in hearing about your techniques and results -- of course,
if you're doing this only for disk space savings, you can disregard my
rantings...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> I am trying to compress text/image column on a table on MSSQL200
Enterprise Ed.
> When will compress when write to the table and uncompress when user
retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||Andrew J. Kelly wrote:
> You need to do the compression and uncompression in the client app or
> middle tier and not the database server.
>
The OP could place the TEXTIMAGE on a filegroup that is located on a
compressed folder on the server. That's supported by SQL Server if I'm
not mistaken and won't require any additional libraries to manage.
Andrew, any thoughts?
David Gugick
Imceda Software
www.imceda.com
|||Actually I know using compressed volumes is not recommended and I think it
may even be unsupported for Sql Server.
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23S5%23N4uGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
> The OP could place the TEXTIMAGE on a filegroup that is located on a
> compressed folder on the server. That's supported by SQL Server if I'm not
> mistaken and won't require any additional libraries to manage.
> Andrew, any thoughts?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Andrew J. Kelly wrote:
> Actually I know using compressed volumes is not recommended and I
> think it may even be unsupported for Sql Server.
>
You are correct:
http://support.microsoft.com/kb/231347/EN-US/
David Gugick
Imceda Software
www.imceda.com
|||What about upgrade to use Yukon, use CLR written in c# to write unpresss text
and store on sqlserver? Anyone has done this sort of thing?
"Adam Machanic" wrote:

> To add to Andrew's response, I recommend that you test very heavily before
> implementing this in a production environment; I've done fairly extensive
> testing of various on-the-fly .NET compression libraries for the purpose of
> compressing LOB data on the way in and out of the database, and found that
> instead of improving performance as I expected (due to lowering disk IOs and
> network bandwidth required to retrieve the data), there was instead a
> moderate degredation due to the extra processor strain on the middle tier.
> If you do find a way to improve performance using compression, I would be
> very interested in hearing about your techniques and results -- of course,
> if you're doing this only for disk space savings, you can disregard my
> rantings...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
> news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> Enterprise Ed.
> retrieve
>
>
|||"SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> What about upgrade to use Yukon, use CLR written in c# to write unpresss
text
> and store on sqlserver? Anyone has done this sort of thing?
I haven't tried yet -- but I plan to when the next beta is released and
performance is improved a bit. MS hadn't started performance tuning the
last releases much so it wasn't worth testing, IMO.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Please let us know later the.
"Adam Machanic" wrote:

> "SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
> news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> text
> I haven't tried yet -- but I plan to when the next beta is released and
> performance is improved a bit. MS hadn't started performance tuning the
> last releases much so it wasn't worth testing, IMO.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>