How can I see if the data in a SQL Server table is page-compressed?

by Caitlin M. Shaw   Last Updated August 13, 2019 20:06 PM

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? 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?

Here are the steps to create row-compressed data in a theoretically-page-compressed table:
1. 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.

The result is a table in which the data is compressed on the row level (smaller than an uncompressed table but larger than a page-compressed table), but inspecting the sys.allocation_units catalog table shows the data compression as page.

The question

When the table's data allocation is for page compression as in this scenario, what can I do to find out if the data in that table is page-compressed?

Related Questions

sparse column or DATA_COMPRESSION?

Updated October 18, 2018 15:06 PM

Compressing database file fails

Updated March 08, 2018 17:06 PM