修改表結構的內幕

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

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

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

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

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

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

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

特別是，當刪除列、添加一個null值的列，可變長度列的長度提升，一個不可空的列變更為可空時，都不會改變原有的數(shù)據(jù)。

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

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

某些表結構變更需要檢查數(shù)據(jù)但修改。例如，當你把可空的列變更為不可空時，SQL Server必須首先確認該列的數(shù)據(jù)中沒有空值。

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

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

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

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

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

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

對表結構的其他更改要求 SQL server 在物理上更改每一行;當它進行更改時, 它必須將適當?shù)挠涗泴懭胧聞杖罩? 因此對于大型表來說, 這些更改可能非常耗費資源。

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

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

您可以自行瀏覽此情況的頁面轉儲, 但您可以通過使用清單6-5 前面所示的列詳細信息查詢來查看列偏移量來看到某些意外行為。

首先, 創(chuàng)建一個具有所有固定長度列的表, 包括第一個位置中的 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;

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

并且在行標題信息之后沒有任何列在偏移量4處開始。

即使在表中放置任何數(shù)據(jù)之前, 由于更改表而創(chuàng)建的新列也會發(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 在不實際刪除舊列時的行為的另一個缺點是, 行大小現(xiàn)在受到了更嚴格的限制。行大小現(xiàn)在包括舊列, 它不再可用或可見 (除非使用 DBCC PAGE)。

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

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

CREATE TABLE bigchange

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

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

此時, 由于3000字節(jié)列、1000字節(jié)列和 smallint, 行長度應僅超過4000個字節(jié)。但是, 如果嘗試添加另一個3000字節(jié)的列, 則會失敗:

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)建具有兩個3000字節(jié)列和1000字節(jié)列的表不會導致任何問題:

CREATE TABLE nochange

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

網(wǎng)頁名稱：SQLServer修改表結構內幕
本文鏈接：http://www.rwnh.cn/article30/pdeppo.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護、做網(wǎng)站、網(wǎng)站營銷、網(wǎng)站設計、響應式網(wǎng)站、網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)