主页 / dba / 问题 / 256046
Accepted
Alsin
Asked: 2019-12-21 07:08:53 +0800 CST

压缩表上的 ALTER 固定长度 COLUMN

  • 772

我想知道表的压缩是否真的会改变ALTER TABLE规则。我没有找到关于这个主题的信息。让我们考虑一个简单的例子。我将创建一个表并将其中一列从 更改INT为BIGINT。

CREATE TABLE dbo.test1
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
)

物理行看起来就像我们可以建议的那样，两个字段，每个 4 个字节。

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

让我们将 c2 从 更改INT为BIGINT

ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint not null

我们增加了固定长度列的长度，因此 SQL Server 在行尾再添加一列。相同的查询显示

leaf_offset max_length
4           4
8           4
12          8

到目前为止一切顺利，一切都像书籍和许多文章描述的那样工作。让我们尝试对压缩表做同样的事情。

CREATE TABLE dbo.test2
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
) with (data_compression = page)

物理结构如下所示：

leaf_offset max_length
-1          4
-2          4

所有“偏移量”都是负数。只有可变长度列假设有负偏移量。一开始我很惊讶，我从来没有见过这个记录。但这是有道理的，压缩页面不能从行的开头有固定的偏移量。现在让我们尝试更改列。

ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null

第二个惊喜，“新”栏没有添加。

leaf_offset max_length
-1          4
-2          8

更令人惊讶的是，在 SQL Server 2017 上，此操作似乎是“仅限元数据”，仍待确认。SQL Server 2014 以旧方式处理它。

我的问题

  1. 我们是否有任何关于表压缩如何影响的文档ALTER TABLE？谁能解释一下？
  2. SQL Server 2017 是否改进了这一点？

我已经阅读了 Microsoft SQL Server 2012 Internals 中的这个示例章节。它涵盖了压缩数据的列描述符行格式，但仅涵盖所涉及的物理结构，根本不涉及架构更改。

sql-server alter-table

1 个回答

  1. Best Answer

    文档

    除了非常一般的声明之外，我不知道ALTER COLUMN有关压缩表上的仅元数据操作的文档：

    您在 ALTER TABLE 中指定的更改会立即实施。如果更改需要修改表中的行，则 ALTER TABLE 会更新这些行。

    人们可以将其解释为，如果使用的特定存储格式意味着数据不会改变，SQL Server 可能会避免数据大小操作。继续以这种慷慨的方式，有人可能会争辩说，特定更改是否仅是元数据取决于已实施的内容以及逻辑上可能的内容。

    当 SQL Server 2012 添加了对添加NOT NULL具有运行时常量默认值的列的支持时，添加了文档说明，并且文档已针对新ONLINE操作进行了更新。如果您认为文档对于压缩表上仅元数据的数据类型更改有用，您应该提交反馈。我个人不想成为负责这项工作或保持最新的人。

    与此同时，我在 SQL Server 2016 中的 New Metadata-Only Column Changes 中写下了我能够辨别出的关于这种行为的内容。

    SQL Server 2017 是否改进了这一点？

    根据我的测试，在 SQL Server 2016 中启用了新行为。可以使用未记录的启动或全局跟踪标志 3618 禁用它：

    DBCC TRACEON (3618, -1);

    一个演示：

    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;
    • 3

相关问题