SQL Server修改表结构内幕

修改表结构的内幕

并非所有的ALTER TABLE变动在ALTER TABLE命令使用时都需要SQL Server去更改每一行。

SQL Server可以以三种基本方式去执行ALTER TABLE命令

1. 它可能只需要更改元数据

2. 它可能需要检查所有现有数据, 以确保它与更改兼容, 但只需要对元数据进行更改。

3. 它可能需要在物理上改变每一行。

在很多情况下，SQL Server只能更改元数据(主要通过sys.columns来查看数据)来反映新结构。

特别是，当删除列、添加一个null值的列，可变长度列的长度提升，一个不可空的列变更为可空时，都不会改变原有的数据。

当删除列时数据不会被触及，意味着该列的磁盘空间不会被回收。当表的行大小接近或超过其限制时, 你可能需要手工回收已删除列的磁盘空间。

你可以通过创建或ALTER INDEX重建表的聚族索引去回收空间，见第7章，或者通过ALTER TABLE重建表，见第8章。

某些表结构变更需要检查数据但修改。例如，当你把可空的列变更为不可空时，SQL Server必须首先确认该列的数据中没有空值。

一个可变长的列被缩短时，所有存在的数据必须被检查，如果有任何的数据长度大于新限制，ALTER TABLE命令都会执行失败。值得注意的是，变更一个大表是需要时间的。

改变一个固定长度的列为更短的类型，例如int变为smallint，或者char(10)变为char(8),也是需要检查所有的数据都能存储到新的类型中。

但是，即使新数据类型占用更少的字节，物理页上的数据也不会被修改。

如果你创建一个表有int列，每行4字节，那么所有行都会使用完整的4个字节。在表的int类型修改为smallint类型后，你插入数据是会受新类型的范围限制，

但是这些数据仍是4个字节，是不是smallint的2个字节，你可以通过dbcc page命令验证。

char(10)变为char(8)与之前的类似，数据依然使用10字节存储，但是插入是受8字节长度限制。直至重建表之后，char(10)才会真正变为char(8)。

对表结构的其他更改要求 SQL server 在物理上更改每一行;当它进行更改时, 它必须将适当的记录写入事务日志, 因此对于大型表来说, 这些更改可能非常耗费资源。

此类型更改的一个示例是将列的数据类型更改为具有不同内部存储表示形式的新类型。

修改表结构的另一个负面影响出现在列被修改为提升长度。在这种情况下, 旧列实际上没有被替换;而是将新列添加到表中, DBCC 页显示旧数据仍然存在。

您可以自行浏览此情况的页面转储, 但您可以通过使用清单6-5 前面所示的列详细信息查询来查看列偏移量来看到某些意外行为。

首先, 创建一个具有所有固定长度列的表, 包括第一个位置中的 smallint:

CREATE TABLE change

(col1 smallint, col2 char(10), col3 char(5));

现在查看列偏移量:

SELECT c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

ON p.partition_id = pc.partition_id

JOIN sys.columns c

ON column_id = partition_column_id

AND c.object_id = p.object_id

WHERE p.object_id=object_id('change');

RESULTS:

column_name   column_id   max_inrow_length   system_type_id leaf_offset

------------- ----------- ------------------ -------------- -----------

col1          1           2                  52             4

col2          2           10                 175            6

col3          3           5                  175            16

现在把 smallint 改为 int:

ALTER TABLE change

ALTER COLUMN col1 int;

最后, 再次运行清单6-5 中的列详细信息查询, 以查看 col1 现在在该行中开始的时间较晚, 

并且在行标题信息之后没有任何列在偏移量4处开始。

即使在表中放置任何数据之前, 由于更改表而创建的新列也会发生:

column_name   column_id   max_inrow_length   system_type_id   leaf_offset

------------- ----------- ------------------ ---------------- -----------

col1          1           4                  56               21

col2          2           10                 175              6

col3          3           5                  175              16

SQL server 在不实际删除旧列时的行为的另一个缺点是, 行大小现在受到了更严格的限制。行大小现在包括旧列, 它不再可用或可见 (除非使用 DBCC PAGE)。

例如, 如果创建的表具有一对大的固定长度字符列,

如下所示, 则可以将 char (2000) 列更改为 char (3000):

CREATE TABLE bigchange

(col1 smallint, col2 char(2000), col3 char(1000));

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

此时, 由于3000字节列、1000字节列和 smallint, 行长度应仅超过4000个字节。但是, 如果尝试添加另一个3000字节的列, 则会失败:

ALTER TABLE bigchange

ADD col4 char(3000);

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'bigchange' failed because the minimum row size

would be 9009, including 7 bytes of internal overhead. This exceeds the

maximum allowable table row size of 8060 bytes.

但是, 仅创建具有两个3000字节列和1000字节列的表不会导致任何问题:

CREATE TABLE nochange

(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));