Data compression in SQL Server 2008

Data compression is introduced in SQL Server 2008, allowing data compression to be performed in tables, indexes, and partitions. This will not only save disk space, but also allow more data pages to be loaded into memory, reducing disk IO and improving query performance. Of course, there are pros and cons, and after you enable data compression, the database server requires additional CPU resources to compress. In general, the CPU usage of the database server is not too high, and disk IO is easy to become a bottleneck, so in most cases the large database, especially the data warehouse to enable this feature is more beneficial than the disadvantage.
SQL Server 2008 data compression is divided into two types: row compression and page compression. Row compression is achieved primarily by storing fixed-length types as variable-length types, while also reducing the metadata overhead associated with records. Page compression on the basis of compression and prefix compression and dictionary compression, can obtain a greater compression ratio.
To enable database compression simply add the WITH (Data_compression = ROW) or with (Data_compression = PAGE) After the build table statement. To modify an existing index to enable compression, ALTER index on table REBUILD with (Data_c0mpression=row) or ALTER INDEX index on table REBUILD with (data_c0mpression=page) implementation.
Finally, a simple script is provided to determine if the data table needs to be compressed, and a compression script is automatically generated for the system administrator to perform. The sp_msforeachtable stored procedure is used here. In this script, the @precommand parameter is used to execute the SQL command before the command command executes, to create a temporary table to hold the data table information, @command1 parameter represents the SQL command to execute, each table is used sp_ The spaceused stored procedure gets the disk occupancy information for the table and saves it to the established staging table, @postcommand parameters are used to execute the SQL commands after the command command, associating the previously established temporary table with the system, Generates a data table compression script based on the conditions set (data table occupies more than 10G).

EXEC sp_msforeachtable@precommand=n ' CREATE TABLE # # (id int identity,name sysname,rows int,reserved Nvarchar (), data varchar (), indexdata varchar (+), unused varchar () ', @command1 =n ' insert INTO # # (Name,rows,reserved,data, indexdata,unused) exec sp_spaceused '? '  Update # # Set data=substring (data, 1, LEN (data)-2) where id=scope_identity () and LEN (data) >=2 ', @postcommand =n ' SELECT  "ALTER TABLE" + TABLENAME + "REBUILD with (data_compression = PAGE)" From Sys.tables Ajoin (SELECT c.name + ". + A.name as TABLENAME, object_id from # # Ajoin sys.objects BON a.name = b.namejoin sys.schemas CON b.schema_id = C.schema _idwhere CAST (data as int) > 10000000 and object_id in (SELECT object_id from sys.tables) BON a.object_id = b.object_i D and type = ' U ';d rop table # # '

Data compression in SQL Server 2008