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));