Shulou(Shulou.com)05/31 Report--

This article focuses on "how to compress data in SQLServer2008". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to compress data in SQLServer2008.

To enable database compression, simply add WITH (DATA_COMPRESSION=ROW) or WITH (DATA_COMPRESSION=PAGE) after the table statement is created. If you need to modify the existing index to enable compression, you can do so through ALTERINDEXindexONTableREBUILDWITH (DATA_C0MPRESSION=ROW) or ALTERINDEXindexONTableREBUILDWITH (DATA_C0MPRESSION=PAGE).

Finally, a simple script is provided to determine whether the data table needs to be compressed, and the compression script is automatically generated for the system administrator to execute. The unexposed stored procedure sp_MSforeachtable is used here. In this script, the @ precommand parameter is used to execute the SQL command before the execution of the command instruction, to establish a temporary table to store the information of the data table, the @ command1 parameter represents the SQL command to be executed, for each table, the sp_spaceused stored procedure is used to obtain the disk occupancy information of the table and save it in the established temporary table, and the @ postcommand parameter is used for the SQL command after executing the command instruction to associate the previously established temporary table with the system. Generate a data table compression script according to the set conditions (the data table occupies more than 10G of space).

How to compress data in SQLServer2008

Execsp_MSforeachtable@precommand=N'createtable## (idintidentity,namesysname,rowsint,reservedNvarchar (50), datavarchar (50), indexdatavarchar (50), unusedvarchar (50))', @ command1=N'insertinto## (name,rows,reserved,data,indexdata,unused) execsp_spaceused''''update##setdata=SUBSTRING (data,1,LEN (data)-2) whereid=scope_identity () AND

At this point, I believe you have a deeper understanding of "how to compress data in SQLServer2008". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.