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.
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?