SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data compression


Data compression

Author
Message
dinolg
dinolg
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 94
MSDN clearly notes that "FILESTREAM filegroups can be on compressed volumes." Everyone seems to mention BOL, but fails to mention a quote, or any kind of reference. Perhaps BOL is just wrong. It certainly wouldn't be the first time.

How are so many people oblivious to compression options ? Are the people answering "No" the people who don't use compression ?

The answer is only "No" if you use the most basic form of compression, where rows over 8K or so disallow the basic compression.

There are also more advanced options, such as using a CLR procedure to compress your data, or doing compression outside SQL Server. All of these answer the question "Is data compression available for the data that is stored separately?", but we don't even need to look that far. Obviously the poster and about half the respondents read about row level compression, and stopped there.

The correct answer is YES. Yes, Data compression is available for large-value data types when the data is stored off row, and in many different ways. Doesn't anyone compress their large data types ? What do people actually compress if not the biggest things, the things that (usually) benefit MOST from compression ?
Revenant
Revenant
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8656 Visits: 4921
Yogeshwar Phull (1/29/2013)
Thanks for the question. BOL does describe this..

+1, and thanks!
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4825 Visits: 2774
Thanks a lot for this good question. :-)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13201 Visits: 12180
roger.plowman (1/30/2013)
However, the question now becomes, why *doesn't* it compress seperately stored data?

I started to reply here, but as the length of my reply grew I realised I probably better put it on my blog. So here is the link: http://sqlblog.com/blogs/hugo_kornelis/archive/2013/01/31/why-does-sql-server-not-compress-data-on-lob-pages.aspx.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sipas
sipas
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 722
dinolg (1/30/2013)
Everyone seems to mention BOL, but fails to mention a quote, or any kind of reference.


The BOL reference is in the explanation.

I thought it was pretty clear that the question referred to data compression as implemented by SQL server.

Hugo Kornelis (1/31/2013)
roger.plowman (1/30/2013)
However, the question now becomes, why *doesn't* it compress seperately stored data?

I started to reply here, but as the length of my reply grew I realised I probably better put it on my blog. So here is the link: http://sqlblog.com/blogs/hugo_kornelis/archive/2013/01/31/why-does-sql-server-not-compress-data-on-lob-pages.aspx.

Great explanation - read this everyone.
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3426 Visits: 1694
Hugo Kornelis (1/31/2013)
I started to reply here, but as the length of my reply grew I realized I probably better put it on my blog...


Great question with good discussion!

Hugo, again thank you for blogging this. As do excellent work!

Not all gray hairs are Dinosaurs!
@Cassie
@Cassie
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 123
+1
Strange though, no compression
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40617 Visits: 18565
EZ-PZ



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Andre Ranieri
Andre Ranieri
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 379
+1 - thanks for the question. I got it wrong (guessed) but learned something.

Cheers,

Andre Ranieri
jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 299
interesting and useful one !
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search