[SQL SERVER][Memo]了解update process
前幾天朋友問我執行update statement，SQL SERVER Database Engine是先delete後insert的嗎?
在很久以前我可能會直接且絕對回答"Yes"，但現在可就不會這麼絕對say yes
畢竟資料庫博大精深，啟可隨便蒙混唬爛過關，所以不廢話馬上來測試實驗看看。
這裡簡單測試兩種狀況(資料長度大小相同)
1.依照PK更新table資料
2.依照Non-Clustered index更新table資料
建立測試table
CREATE TABLE ##test1(
scn int,
col varchar(20),
CONSTRAINT PK_test1 PRIMARY KEY (scn)
)
go
CREATE TABLE ##test2(
scn int,
col varchar(20),
CONSTRAINT PK_test2 PRIMARY KEY (scn)
)
goinsert data
insert into ##test1 select 1,'111111111'
insert into ##test1 select 2,'222222222'
insert into ##test1 select 3,'333333333'
insert into ##test1 select 4,'444444444'
insert into ##test1 select 5,'555555555'
insert into ##test2 select 1,'111111111'
insert into ##test2 select 2,'222222222'
insert into ##test2 select 3,'333333333'
insert into ##test2 select 4,'444444444'
insert into ##test2 select 5,'555555555'check object_id
use tempdb
go
select db_id(),object_id from sys.all_objects tt where tt.name in ('##test1','##test2')
go
確認資料儲存位置
dbcc extentinfo (2,37575172)
dbcc extentinfo (2,101575400)
查看兩個table資料分頁儲存內容
dbcc traceon(3604)
dbcc page(2,1,110,1)
dbcc page(2,1,115,1)PAGE: (1:110)
PAGE: (1:115)
兩個table資料儲存分頁內容是相同的。
測試第一種情況:依照PK更新table資料
update ##test1 set scn=6 where scn=1
go
delete from ##test2 where scn=1
insert into ##test2 select 6,'111111111'
go
再度確認兩個table資料分頁儲存內容
PAGE: (1:110)
PAGE: (1:115)
兩個table結構一模一樣，row0資料都在120~144字節，但原來96~120卻沒有任何資料
看來情況一符合大家所知道的概念，Database Engine update是先delete後insert。
測試第二種情況:依照Non-Clustered index更新table資料
建立NONCLUSTERED INDEX
CREATE NONCLUSTERED INDEX idx_test1
ON ##test1(col)
go
CREATE NONCLUSTERED INDEX idx_test2
ON ##test2(col)
go update data
update ##test1 set col='sixsixsix' where scn=6
go
delete from ##test2 where scn=6
insert into ##test2 select 6,'sixsixsix'
go
再度確認兩個table資料分頁儲存內容
PAGE: (1:110)
PAGE: (1:115)
這時可以看到儲存內容不同了，##test1儲存結構沒有變化，但##test2結構卻發生變化了
原本216個字節儲存row4資料，但現在這筆資料卻儲存在240字節以後
而原本192~216卻沒有儲存任何資料，這裡##test2很明顯就形成了內部碎片
所以對該情況來說，Database Engine update並不是一般大家所知的先delete後insert。
當然真實世界情況有很多種，這裡我只是簡單測試並非就代表全部。
最後祝大家Happy New Year，虎年行大運。