Update操作一定是先Delete再Insert嗎？
Update在數據庫中的執行是怎么樣的？“Update操作是先把數據刪除，然后再插入數據”。在網上看了很多也都是這么認為的。但在查閱到一些不同看法的時候我進行了一些驗證，發現還有其它的情況。
這里我分三種情況來講：
1、更改沒有索引列的字段，更改前和更改后的字符串長度一樣；
2、更改沒有索引列的字段，更改后比更改前的字符串長；
3、更改聚集索引字段。
一、 創建表、索引和數據：
|
--創建表MyTable1
IF EXISTS(SELECT * FROM sysobjects WHERE [name]='MyTable1' AND [type]='u')
DROP TABLE MyTable1
GO
CREATE TABLE MyTable1
(
ID INT,
SName VARCHAR(20)
);
--創建索引
CREATE UNIQUE CLUSTERED INDEX IX_ID ON MyTable1(ID);
INSERT INTO MyTable1 values( 1,'aaaa')
INSERT INTO MyTable1 values( 2,'bbbb')
INSERT INTO MyTable1 values( 3,'cccc')
GO
SELECT * FROM MyTable1 mt
GO
--創建表MyTable2
IF EXISTS(SELECT * FROM sysobjects WHERE [name]='MyTable2' AND [type]='u')
DROP TABLE MyTable2
GO
CREATE TABLE MyTable2
(
ID INT,
SName VARCHAR(20)
);
--創建索引
CREATE UNIQUE CLUSTERED INDEX IX_ID ON MyTable2(ID);
INSERT INTO MyTable2 VALUES ( 1,'aaaa')
INSERT INTO MyTable2 VALUES ( 2,'bbbb')
INSERT INTO MyTable2 VALUES ( 3,'cccc')
SELECT * FROM MyTable2 mt
|
二、 查看數據庫的ID號以及兩個表對應的ID
|
--查看數據庫的ID號以及兩個表對應的ID
select db_id() AS '數據庫ID',object_id('MyTable1')as '表MyTable1_ID',object_id('MyTable2')as '表MyTable2_ID'
|
查詢結果如下：
三、 查看數據頁的頁碼
|
--查看數據頁的ID
DBCC extentinfo(6,213575799)--這里是剛剛查出來的數據庫的ID，是表MyTable1的ID
DBCC extentinfo(6,229575856)--這里是剛剛查出來的數據庫的ID，是表MyTable2的ID
|
查詢結果如下：
表示MyTable1的數據存儲在第45頁，MyTable2的數據存儲在第94頁。
四、 查看2個表所在頁面上每條記錄的存儲情況：
|
DBCC traceon(3604) WITH NO_INFOMSGS --打開跟蹤
DBCC IND('TestDB','MyTable1',0) --列出所有頁和索引。
--參數說明，：數據庫名；：表名；：索引的ID，表示堆，-1 表示顯示所有索引和IAMs, -2表示只顯示IAMs
DBCC PAGE(TestDB,1,45,1) --查看數據頁和索引
--參數說明，：數據庫名；：數據頁文件文件組編號；：數據頁ID；：數據顯示類型（，）
|
語句執行后我們得到下面的結果：
1. MyTable1的Row – Offset：
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
|
2. MyTable2的Row – Offset：
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
|
可以看到兩個表的存儲在數據庫中數據頁的位置是一樣的。這是因為一個頁只能放一個對象。
五、 下面我們來看第一種情況：更改沒有索引列的字段，更改前和更改后的字符串長度一樣；
|
UPDATE MyTable1 SET SName = 'dddd' WHERE ID=2
|
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
|
發現他的存儲位置沒有發生改變。
再來看MyTable2
|
--先刪除后插入
DELETE FROM MyTable2 WHERE ID=2
INSERT INTO MyTable2(ID,SName)VALUES(2, 'dddd')
|
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 153 (0x99)
0 (0x0) - 96 (0x60)
|
表MyTable2的存儲發生變化了，原先在115和134之間存儲的是第二條記錄，現在這條記錄卻存儲到了153個字節以后了，而原來115和134之間什么也沒存儲，這樣這里就形成了內部碎片。對于這種update后數據的存儲位置不發生變化的更新稱為現場更新，如果位置發生了改變就稱為非現場更新。
所以對于這種情形來說：update操作并不是先delete后insert的。
六、 下面我們再來測試第二種情況：更改沒有索引列的字段，更改后比更改前的字符串長；
先更新表MyTable1，再查看數據頁的存儲情況：
|
UPDATE MyTable1 SET SName='aaaaaa' WHERE ID=2
DBCC PAGE(TestDB,1,45,1)
|
存儲結果如下：
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 153 (0x99)
0 (0x0) - 96 (0x60)
|
這時我們看到他的存儲和先Delete再Insert一樣了。
七、 我們再來看第三種情況：更改聚集索引字段
為了避免對數據庫的操作影響查看的難度，再執行一下創建表的語句。
數據更新之前的結果如下：
|
MyTable1：
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
|
|
MyTable2：
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
|
先對表MyTable1操作：更新ID
|
UPDATE MyTable1 SET ID = 0 WHERE ID=2
DBCC PAGE(TestDB,1,94,1) --數據頁的位置已經發生改變
|
存儲結果如下：
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 153 (0x99)
0 (0x0) - 96 (0x60)
|
再來對MyTable2操作：
|
DELETE FROM MyTable2 WHERE ID = 2
INSERT INTO MyTable2(ID,SName)VALUES(2, 'bbbb')
DBCC PAGE(TestDB,1,126,1) --數據頁的位置已經發生改變
|
存儲結果如下：
|
OFFSET TABLE:
Row - Offset
2 (0x2) - 134 (0x86)
1 (0x1) - 153 (0x99)
0 (0x0) - 96 (0x60)
|
發現此時upadte為非現場更新，數據的存儲位置已經發生了改變。和我們所想的先Delete再Insert是一樣的。
其實在更改聚集索引鍵列的時候，也可能發生現場更新。比如有3條記錄分別為1、2、5，我們把其中的2更改為了3，由于3是在1和5之間的數字，所以在更改為3后，這條記錄還是會存儲在1和5之間，所以就是現場更新了。