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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.