更新引发的问题

if object_id('ta') is not null
  drop table ta

-- 建測試表.
create table ta
(id int identity(1,1), 
col1 char(8000), 
col2 char(40), 
col3 varchar(20)
constraint pk_ta primary key(id))

declare @dbid int, @objid int
select @dbid=DB_ID(N'DBAP'), @objid=OBJECT_ID(N'ta')
select max_record_size_in_bytes,min_record_size_in_bytes
from sys.dm_db_index_physical_stats(@dbid, @objid, NULL, NULL , 'DETAILED')

max_record_size_in_bytes min_record_size_in_bytes
------------------------ ------------------------
0                        0

-- insert記錄.
insert into ta values('col1', 'col2', '12')

declare @dbid int, @objid int
select @dbid=DB_ID(N'DBAP'), @objid=OBJECT_ID(N'ta')
select max_record_size_in_bytes,min_record_size_in_bytes
from sys.dm_db_index_physical_stats(@dbid, @objid, NULL, NULL , 'DETAILED')

max_record_size_in_bytes min_record_size_in_bytes
------------------------ ------------------------
8057                     8057

dbcc ind('DBAP','ta',-1)
--> PageFID 1   PagePID 127 148

dbcc page('DBAP',1,127,3)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                               
Slot 0 Column 2 Offset 0x8 Length 8000 Length (physical) 8000
col1 = [Error converting to string (length 8000 bytes)]                   
Slot 0 Column 3 Offset 0x1f48 Length 40 Length (physical) 40
col2 = col2                                                               
Slot 0 Column 4 Offset 0x1f77 Length 2 Length (physical) 2
col3 = 12                            
 
-- update col3,出現如下異常,因為記錄總長度=[隱含列rowid]+4+8000+40+10=8065>8060,
update ta set col3='1234567890'
 
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
 
-- 刪除char(8000)的字段
alter table ta drop column col1
 
-- update col3,仍會出現異常.
update ta set col3='1234567890'
 
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
 
declare @dbid int, @objid int
select @dbid=DB_ID(N'DBAP'), @objid=OBJECT_ID(N'ta')
select max_record_size_in_bytes,min_record_size_in_bytes
from sys.dm_db_index_physical_stats(@dbid, @objid, NULL, NULL , 'DETAILED')

max_record_size_in_bytes min_record_size_in_bytes
------------------------ ------------------------
8057                     8057

dbcc ind('DBAP','ta',-1)
--> PageFID 1   PagePID 127 148

dbcc page('DBAP',1,127,3)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                               
Slot 0 Column 67108865 Offset 0x8 Length 0 Length (physical) 8000
DROPPED = NULL                       
Slot 0 Column 3 Offset 0x1f48 Length 40 Length (physical) 40
col2 = col2                                                               
Slot 0 Column 4 Offset 0x1f77 Length 2 Length (physical) 2
col3 = 12
 
-- 索引重建
dbcc dbreindex('ta', '', 90)
 
declare @dbid int, @objid int
select @dbid=DB_ID(N'DBAP'), @objid=OBJECT_ID(N'ta')
select max_record_size_in_bytes,min_record_size_in_bytes
from sys.dm_db_index_physical_stats(@dbid, @objid, NULL, NULL , 'DETAILED')

max_record_size_in_bytes min_record_size_in_bytes
------------------------ ------------------------
57                       57

dbcc ind('DBAP','ta',-1)
--> PageFID 1   PagePID 151 150848

dbcc page('DBAP',1,151,3)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                               
Slot 0 Column 3 Offset 0x8 Length 40 Length (physical) 40
col2 = col2                                                               
Slot 0 Column 4 Offset 0x37 Length 2 Length (physical) 2
col3 = 12       

-- update col3,正常.
update ta set col3='1234567890'

dbcc page('DBAP',1,151,3)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                               
Slot 0 Column 3 Offset 0x8 Length 40 Length (physical) 40
col2 = col2                                                               
Slot 0 Column 4 Offset 0x37 Length 10 Length (physical) 10
col3 = 1234567890