修改表結(jié)構(gòu)的內(nèi)幕

我們提供的服務(wù)有：成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、岐山ssl等。為1000多家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的岐山網(wǎng)站制作公司

并非所有的ALTER TABLE變動(dòng)在ALTER TABLE命令使用時(shí)都需要SQL Server去更改每一行。

SQL Server可以以三種基本方式去執(zhí)行ALTER TABLE命令

1. 它可能只需要更改元數(shù)據(jù)

2. 它可能需要檢查所有現(xiàn)有數(shù)據(jù), 以確保它與更改兼容, 但只需要對(duì)元數(shù)據(jù)進(jìn)行更改。

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

在很多情況下，SQL Server只能更改元數(shù)據(jù)(主要通過(guò)sys.columns來(lái)查看數(shù)據(jù))來(lái)反映新結(jié)構(gòu)。

特別是，當(dāng)刪除列、添加一個(gè)null值的列，可變長(zhǎng)度列的長(zhǎng)度提升，一個(gè)不可空的列變更為可空時(shí)，都不會(huì)改變?cè)械臄?shù)據(jù)。

當(dāng)刪除列時(shí)數(shù)據(jù)不會(huì)被觸及，意味著該列的磁盤空間不會(huì)被回收。當(dāng)表的行大小接近或超過(guò)其限制時(shí), 你可能需要手工回收已刪除列的磁盤空間。

你可以通過(guò)創(chuàng)建或ALTER INDEX重建表的聚族索引去回收空間，見(jiàn)第7章，或者通過(guò)ALTER TABLE重建表，見(jiàn)第8章。

某些表結(jié)構(gòu)變更需要檢查數(shù)據(jù)但修改。例如，當(dāng)你把可空的列變更為不可空時(shí)，SQL Server必須首先確認(rèn)該列的數(shù)據(jù)中沒(méi)有空值。

一個(gè)可變長(zhǎng)的列被縮短時(shí)，所有存在的數(shù)據(jù)必須被檢查，如果有任何的數(shù)據(jù)長(zhǎng)度大于新限制，ALTER TABLE命令都會(huì)執(zhí)行失敗。值得注意的是，變更一個(gè)大表是需要時(shí)間的。

改變一個(gè)固定長(zhǎng)度的列為更短的類型，例如int變?yōu)閟mallint，或者char(10)變?yōu)閏har(8),也是需要檢查所有的數(shù)據(jù)都能存儲(chǔ)到新的類型中。

但是，即使新數(shù)據(jù)類型占用更少的字節(jié)，物理頁(yè)上的數(shù)據(jù)也不會(huì)被修改。

如果你創(chuàng)建一個(gè)表有int列，每行4字節(jié)，那么所有行都會(huì)使用完整的4個(gè)字節(jié)。在表的int類型修改為smallint類型后，你插入數(shù)據(jù)是會(huì)受新類型的范圍限制，

但是這些數(shù)據(jù)仍是4個(gè)字節(jié)，是不是smallint的2個(gè)字節(jié)，你可以通過(guò)dbcc page命令驗(yàn)證。

char(10)變?yōu)閏har(8)與之前的類似，數(shù)據(jù)依然使用10字節(jié)存儲(chǔ)，但是插入是受8字節(jié)長(zhǎng)度限制。直至重建表之后，char(10)才會(huì)真正變?yōu)閏har(8)。

對(duì)表結(jié)構(gòu)的其他更改要求 SQL server 在物理上更改每一行;當(dāng)它進(jìn)行更改時(shí), 它必須將適當(dāng)?shù)挠涗泴?xiě)入事務(wù)日志, 因此對(duì)于大型表來(lái)說(shuō), 這些更改可能非常耗費(fèi)資源。

此類型更改的一個(gè)示例是將列的數(shù)據(jù)類型更改為具有不同內(nèi)部存儲(chǔ)表示形式的新類型。

修改表結(jié)構(gòu)的另一個(gè)負(fù)面影響出現(xiàn)在列被修改為提升長(zhǎng)度。在這種情況下, 舊列實(shí)際上沒(méi)有被替換;而是將新列添加到表中, DBCC 頁(yè)顯示舊數(shù)據(jù)仍然存在。

您可以自行瀏覽此情況的頁(yè)面轉(zhuǎn)儲(chǔ), 但您可以通過(guò)使用清單6-5 前面所示的列詳細(xì)信息查詢來(lái)查看列偏移量來(lái)看到某些意外行為。

首先, 創(chuàng)建一個(gè)具有所有固定長(zhǎng)度列的表, 包括第一個(gè)位置中的 smallint:

CREATE TABLE change

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

現(xiàn)在查看列偏移量:

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

現(xiàn)在把 smallint 改為 int:

ALTER TABLE change

ALTER COLUMN col1 int;

最后, 再次運(yùn)行清單6-5 中的列詳細(xì)信息查詢, 以查看 col1 現(xiàn)在在該行中開(kāi)始的時(shí)間較晚, 

并且在行標(biāo)題信息之后沒(méi)有任何列在偏移量4處開(kāi)始。

即使在表中放置任何數(shù)據(jù)之前, 由于更改表而創(chuàng)建的新列也會(huì)發(fā)生:

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 在不實(shí)際刪除舊列時(shí)的行為的另一個(gè)缺點(diǎn)是, 行大小現(xiàn)在受到了更嚴(yán)格的限制。行大小現(xiàn)在包括舊列, 它不再可用或可見(jiàn) (除非使用 DBCC PAGE)。

例如, 如果創(chuàng)建的表具有一對(duì)大的固定長(zhǎng)度字符列,

如下所示, 則可以將 char (2000) 列更改為 char (3000):

CREATE TABLE bigchange

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

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

此時(shí), 由于3000字節(jié)列、1000字節(jié)列和 smallint, 行長(zhǎng)度應(yīng)僅超過(guò)4000個(gè)字節(jié)。但是, 如果嘗試添加另一個(gè)3000字節(jié)的列, 則會(huì)失敗:

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.

但是, 僅創(chuàng)建具有兩個(gè)3000字節(jié)列和1000字節(jié)列的表不會(huì)導(dǎo)致任何問(wèn)題:

CREATE TABLE nochange

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