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`.
```sql
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.
```sql
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')
```
```none
leaf_offset max_length
4 4
8 4
```
Let's change c2 from `INT` to `BIGINT`
```sql
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
```none
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.
```sql
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:
```none
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.
```sql
ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null
```
The second surprise, the 'new' column has not been added.
```none
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](https://www.microsoftpressstore.com/articles/article.aspx?p=2225060&seqNum=4) 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.
TopAnswers
Databases
Top Answer
Paul White (imported from SE)
## Documentation
There is no documentation I am aware of for metadata-only `ALTER COLUMN` operations on compressed tables, beyond the [very general statement][1]:
>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][2]. I would not personally want to be the person tasked with that effort, or keeping it up to date.
I asked my contacts at Microsoft about this, and they said they are looking at ways to improve the documentation. It is tricky to document well given the number of variables involved. Still, I hope we will see improvements soon.
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](https://sqlperformance.com/2020/04/database-design/new-metadata-column-changes-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;
```
[1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql#locks-and-alter-table
[2]: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-get-help