Sql-server – ALTER fixed-length COLUMN on compressed table

alter-tablesql server

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 INT to BIGINT.

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 INT to BIGINT

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.

My questions

  1. Do we have any document available on how table compression affects ALTER TABLE? Who can shed some light on it?
  2. 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.

Best Answer

Documentation

There is no documentation I am aware of for metadata-only ALTER COLUMN operations on compressed tables, beyond the very general statement:

Changes you specify in ALTER TABLE implement immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows.

One could interpret that as meaning that SQL Server may avoid a size-of-data operation if the particular storage format in use means the data does not change. Continuing in that generous vein, one could argue that whether a particular change is metadata-only or not depends on what has been implemented as well as what is logically possible.

Documentation notes were added when SQL Server 2012 added support for adding a NOT NULL column with a runtime constant default value, and the documentation has been updated for new ONLINE operations. If you feel documentation would be useful for metadata-only data type changes on compressed tables, you should submit feedback. I would not personally want to be the person tasked with that effort, or keeping it up to date.

In the meantime, I wrote what I have been able to discern about this behaviour in New Metadata-Only Column Changes in SQL Server 2016.

Does SQL Server 2017 improve this?

From my testing, the new behaviour was enabled in SQL Server 2016. It can be disabled with undocumented startup or global trace flag 3618:

DBCC TRACEON (3618, -1);

A demo:

CREATE TABLE dbo.test1
(
  c1 bigint NOT NULL,
  c2 integer NOT NULL,
  c3 char(4) NOT NULL
  primary key (c1)
)
WITH (DATA_COMPRESSION = ROW);

INSERT dbo.test1 WITH (TABLOCK)
(
    c1,
    c2,
    c3
)
SELECT
    N.n,
    CONVERT(integer, N.n),
    LEFT(N.n, 4)
FROM
(
    SELECT 
        n = ROW_NUMBER() OVER (ORDER BY @@SPID) 
    FROM sys.all_columns AS AC1
    CROSS JOIN sys.all_columns AS AC2
    ORDER BY n
    OFFSET 0 ROWS
    FETCH FIRST 5 * 1000 * 1000 ROWS ONLY
) AS N;

-- Both metadata only
ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint NOT NULL;
ALTER TABLE dbo.test1 ALTER COLUMN c3 char(8) NOT NULL;

DROP TABLE dbo.test1;
Related Question