Contents
When to use row or page compression in SQL Server?
The first thing to understand is that row compression is a subset of page compression. Put another way, you cannot have page compression without first having row compression performed. When you go to enable page compression, the engine will do the following actions in this order: Row compression.
How can I see if the data in a SQL Server table is page compressed?
Create a table with DATA_COMPRESSION=PAGE and do not use sp_tableoption to turn on the “table lock on bulk load” option for this table. 2. Use bcp to bulk insert the data from a flat file into the new table, but without specifying the -h TABLOCK option to lock the table.
How to change the compression state of a table?
To change the compression state of a table, index, or partition, use the ALTER TABLE or ALTER INDEX statements. For general information about compression, see Data Compression. If the existing data is fragmented, you might be able to reduce its size without using compression by rebuilding the index.
How can I tell afterward what compression is applied?
The answer to that question (paraphrased from Randi Vertongen’s excellent answer) is yes, but it requires that the bulk insert take a table-level lock; otherwise, the bulk insert takes a row-level lock and performs only row data compression. This raises the question: how can I tell afterward what compression is applied?
How is row compression used in Excel spreadsheet?
Row compression is defined here. The simple explanation is that it take fixed-length columns and makes them variable length, adding additional bytes for the overhead of tracking the changes being made. The link provided has a table that references the savings for the datatypes used. It’s interesting reading.
How to make a row level compression decision?
Row level makes easier compression decisions, like reducing metadata, and removing unused space, like the letter ‘Y’ stored in a CHAR (1000). The article referenced explains this in much greater detail. In order to make a decision for bunch of tables you can run procedure ” sp_estimate_data_compression_savings “:
How to determine the compression state of partitions?
To determine the compression state of partitions in a partitioned table, query the data_compression column of the sys.partitions catalog view. When you are compressing indexes, leaf-level pages can be compressed with both row and page compression. Non-leaf-level pages do not receive page compression.