Data Compression in SQL Server: Shubham Saxena

Question

Data Compression in SQL Server: Shubham Saxena

shubham.saxena67

Default port

Points: 1490
More actions
April 2, 2013 at 1:31 am

#272695

Dear Friends,
Please help!!
I need to build up a function or stored procedure to compress/decompress a large object.
like image or blob please provide me script to compress and decompress image in SQL.
Thanks !!:-)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The only compression SQL Server has is table/object compression (look up ALTER INDEX) and backup compression (BACKUP DATABASE ... WITH COMPRESSION)

Could you explain more what you're trying to do here?

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

shubham.saxena67 Default port Points: 1490 More actions · Answer 2

I Want to store image as in image datatype with compression of byte using a function

like fn_compress(@imagebyte image) also need to decompress the same.

could you help me to create a function like fn_compress(@imageByte Image)

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

Compression algorithms aren't really something you want to write in T-SQL, wrong tool for the job. Have a look at the CLR, there may be some built in functions in there that you can use in a CLR function

p.s. the fn prefix is unnecessary. It's just a waste of typing.

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

DiverKas SSCrazy Points: 2049 More actions · Answer 4

shubham.saxena67 (4/2/2013)
I Want to store image as in image datatype with compression of byte using a function
like fn_compress(@imagebyte image) also need to decompress the same.
could you help me to create a function like fn_compress(@imageByte Image)

I would recommend reviewing this white paper: http://research.microsoft.com/apps/pubs/default.aspx?id=64525

I am of the opinion that images shouldnt be stored in relational databases, only the link/path to the item should be. But thats IMHO.

But to answer your question, without going to a CLR, SQL Server can't compress images, nor would you want it to, its not designed for that.

Lowell SSC Guru Points: 323504 More actions · Answer 5

I played with this compression CLR for SQL 2005 more than five years ago.

It worked at the time, but as Gail said, I think doing it in TSQL is the wrong tool for the job. I usually do compression at the application /file system level, and then maybe i would store the results in a SQL database.

you'd most likely need to modify the structure of the table to now have varbinary(max) columns for the compressed data.

http://www.codeproject.com/Articles/16934/Using-CLR-integration-to-compress-BLOBs-CLOBs-in-S

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shubham.saxena67 Default port Points: 1490 More actions · Answer 6

Thanks Gail!!

Thanks Lowell!!

Agreed, i was doing something wrong but use of CLR is the best option.

Lowell, given link was too much helpful.

Thanks a lot!:-)