I wonder if compression of a table actually changes
ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from
CREATE TABLE dbo.test1 ( c1 int not null, c2 int not null, primary key (c1) )
Physical row looks just like we can suggest, two fields, 4 bytes each.
SELECT pc.leaf_offset, pc.max_length FROM sys.system_internals_partitions p join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id WHERE p.object_id = object_id('dbo.test1')
leaf_offset max_length 4 4 8 4
Let's change c2 from
ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint not null
We increased the length of a fixed-length column so SQL Server adds one more column at the end of the row. The same query shows
leaf_offset max_length 4 4 8 4 12 8
So far so good, everything works just like books and numerous articles describe. Let's try to do the same thing with a compressed table.
CREATE TABLE dbo.test2 ( c1 int not null, c2 int not null, primary key (c1) ) with (data_compression = page)
The physical structure looks like this:
leaf_offset max_length -1 4 -2 4
All 'offsets' are negative. Only variable-length columns suppose to have negative offsets. It surprised me at first, I never seen this documented. It makes sense though, the compressed page can't have fixed offsets from the beginning of the row. Now let's try to alter the column.
ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null
The second surprise, the 'new' column has not been added.
leaf_offset max_length -1 4 -2 8
One more surprise, it looks like on SQL Server 2017 this operation is 'metadata-only', still about to confirm it. SQL Server 2014 handles it in the old way.
- Do we have any document available on how table compression affects
ALTER TABLE? Who can shed some light on it?
- Does SQL Server 2017 improve this?
I have read this sample chapter from Microsoft SQL Server 2012 Internals. It covers the column descriptor row format for compressed data, but covers only physical structures involved and doesn't touch schema changes at all.