SQL Server, Can I compress just an Individual table in the DB?

I'm working with a SQL server DB in SQL 2014.

The client has one table 'tblLandHistrory' containing historical data that is much larger than any of the other data tables in the DB and is not used frequently.  The client also has disk space limitations and has asked to see how much space can be gained by compressing the DB.
I have always tried to avoid compressing SQL DB's because of the performance hit.  I know it's usually not that great of a hit but I like to keep things as fast as possible.

I explained that to the client, then asked if it was possible to only compress the one massive table 'tblLandHistory'.  I really don't know if it is possible to just compress one table in the DB.  If so, this one would be an ideal candidate.  It needs to be available but is used infrequently.

Is it possible to compress select (not the entire DB) tables in a SQL DB?  If it is how is that accomplished?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello mlcktmguy,

Yes it is possible to compress single table in database.

USE yourDBName;  
GO  
EXEC sp_estimate_data_compression_savings 'DbName', 'TableNmae', NULL, NULL, 'ROW' ;  

ALTER TABLE DbName.TableNmae REBUILD PARTITION = ALL  
WITH (DATA_COMPRESSION = ROW);  
GO

Read-

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index
0
 
Scott PletcherSenior DBACommented:
Compression speeds up read access of that data (yes, writing takes longer, but it's CPU overhead, the I/O overhead is still less).  Note that index rebuilds, and particularly table rebuilds, take much, much longer.  Therefore, once you compress, make sure you only rebuild if/when it's absolutely required.

For large amounts of data, you'd almost always want to use PAGE compression.  

But since MS provides a proc to estimate how much space compressing data will save you, you should certainly go ahead and run it first and review the results.  You can check for both 'ROW' and 'PAGE' in separate executions.

EXEC sp_estimate_data_compression_savings ...
0
 
mlcktmguyAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.