Data Compression is a wonderful feature. In general, its good since this is an era of Data Explosion and customers want to store more and more data. With the need to store huge amounts of data, applying compression is inevitable. Many of us know the Data Compression is an Enterprise Edition feature only. So how does a customer, who is running Standard Edition, know the space savings they can get if they upgrade to Enterprise Edition? Hmmmm tricky question !
Well, I do not have answer – since sp_estimate_data_compression_savings stored procedure, that gives you the estimated space savings, does not run in lower SKUs like SE, WE or Express Editions.
Well, this feedback has been given to Microsoft already and you may see this SP running on all editions in Denali.
Yes we can backup and restore the DB in a trial EE edition to test space savings, but not really practical if the DB is a few TBs. You can import/export a few large tables only to test but then these workarounds are a pain.
Summary: In times to come, Data Compression is going to be a major driver for customers to move from SE to EE, but they should be able to see the benefit before they spend those extra $$.
Feel free to comment if there is another solution/workaround !