SQLTeam.com | Weblogs | Forums

How to compress db


#1

I have a really big table
there is a lot of data that needs to be saved
but how can I compress it?
should I used shrink? (I once read not to)

please advise


#2

Data compression in SQL Server:

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

Please note that this is Enterprise Only feature from SQL Server 2008 through SQL Server 2016. In SQL Server 2016 SP1, data compression is available in all editions. It is not available in versions prior to 2008.

If you are archiving data for only occasional access, clustered columnstore indexes are a good feature if you are using SQL Server 2014 Enterprise or higher. It offers the best compression ratio but is not suitable for high transactional processing speed.

You should avoid using compressed NTFS folders or drives as the performance will likely be worse.


#3

I think that might be specifically unsupported nowadays?

Might be that the table just needs rebuilding to release unused space, tighten up slack space and so on. My first check would be to see how much slack space there was.

(There's a word for it, and it is not "slack", sorry forgotten!)