SQL Server database Compression

Two days ago, a trial version of the project to be delivered to the user must contain demo data for the customer. However, because the product is based on data analysis and the data volume is large, the task of re-establishing data on an empty database is large. In addition, the team's staff has been in short supply, therefore, we decided to modify the database used for testing, delete unnecessary data, and add some display data. However, after the data processing is complete, we found that the database still has more than 400 mb. We want to compress the data to reduce the size of the database. Here, we will list some of the methods used in the process.

 

• View the space usage of the database exec sp_spaceused. The result contains database_size (size of the current database, including data and log files), unallocated space (database space not reserved for database objects), reserved (total space allocated by objects in the database), index_size (total space used by the index) and unused (total space reserved for objects in the database but not used)
• shrink the database, DBCC shrinkdatabase, and DBCC shrinkfile. In addition, SQL Server 2008 supports row and page compression at the table and index levels, row compression is mainly used to store data types in variable-length format and fixed-length strings by using a variable-length format that does not store null characters. It is optimized for null and 0 data, so that it does not occupy storage space. Page compression is performed in the order of row compression, prefix compression, and Dictionary Compression. The page compression ratio is better, but the cost is high CPU usage.
• SSMs provides data import, data export, and script generation functions. However, if data is exported to a new empty database, foreign keys and indexes are not imported, it is just the data import.
• SSMs tools (a third-party plug-in) provides some common functions to generate the insert script (generate insert scripts) for all data in the database or table), search for a data in all tables, and so on
• cancel all table constraints, select all copies, and run

   select 'alter table' + quotename (object_name (parent_obj) + 'nocheck constraint all' from sysobjectswhere xtype = 'F';