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之间，所以就是现场更新了。