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