一个更新引发的问题的思考的思考,求助各位了.

Question

做如下更新时,
UPDATE t
SET col= 'Stokke KEEP Complete-Cherry'
where ... -- 符合条件的只有一笔记录.Msg 511, Level 16, State 1, Procedure xxx, Line 170
Cannot create a row of size 8062 which is greater than the allowable maximum row size of 8060.查该t.col字段数据类型为varchar(500),且表中所有字段长度总和亦远小于8060,
后跟User联系确认该表曾有个xml字段,后来删除了. 
估计是空间分配未回收问题(仅是估计),做表索引重建,问题解决.
DBCC DBREINDEX('t', '', 90)请问哪位可以解释及重现这个错误?

Answer 1

你有没有这个库的备份来重现这个问题？
联机文档描述“XML 值以内部格式存储为大型二进制对象 (BLOB)”，所以它不应该占用到8060空间中。"查该t.col字段数据类型为varchar(500),且表中所有字段长度总和亦远小于8060,"或者你生成一个T表的脚本看看。

Answer 2

SQL SERVER 2005下测试。if object_id('ta') is not null
drop table ta
go
create table ta(id int identity primary key, col1 char(8000), col2 char(40), col3 varchar(20))
go
insert into ta
values('col1', 'col2', '12')
go
select * from ta
go
update ta
set col3='1234567890'
go
select * from ta
go
alter table ta
drop column col1
go
select * from ta
go
update ta
set col3='1234567890'
go
dbcc dbreindex('ta', '', 90)
go
update ta
set col3='1234567890'
go
select * from ta
go

Answer 3

感謝7樓,重現了這個問題.如何解釋drop column col1後update col3的報錯.一定要重建索引才行嗎.

Answer 4

可以看一下石头锅的存储引擎方面的博客。。，如果是数据列，其实删除列数据仍保存的，不过完美叔说的，XML存在BOLB中，应该不会出现你这个问题才对,

Answer 5

正如9楼所说，删除列时其实并没有从数据页中删除该列。
至于为什么重建索引可以，可能就是它触发SQL SERVER真正从数据页中删除该列了。
（可能还存在其他动作也会触发真正的删除吧。）

Answer 6

应该是增加了xml字段，删除后空间没回收。

Answer 7

use dbx;
go
drop table t;
create table t(v nvarchar(4000),v1 nvarchar(4000));
goalter table t add x nvarchar(4000);insert into t select 'aaasdfdsfds','sadfsadfdsf',replicate('x',4000)
select * from t;alter table t drop column x;select * from t;update t set v=REPLICATE('a',4000),v1=REPLICATE('a',4000)
/*
消息 511，级别 16，状态 1，第 1 行
不能创建大小为 8063 的行，该大小大于所允许的最大行大小 8060。
语句已终止。
*/exec sp_spaceused t

Answer 8

围观varchar nvarchar字段数据删除后或者drop column后的空间并不马上收回，但是影响到行长度计算并且得出错误结论第一次遇到

Answer 9

樓主這樣的用法本身誤用，挑戰SQL限制
char面對以下限制
SQL的行不能跨多頁，且大小限制是8060個字節，頁大小 8192其中頁頭96個字節，頁尾2個字節,還有幾個保留的字節。SQL05以上支持行溢出跨多頁，對部分類型沒有限制如varchar/nvarchar/sql_variant/CLR定義類型等。

Answer 10

好奇怪啊
如果不重建索引
SELECT a.index_id, name,avg_fragmentation_in_percent
FROM   sys.dm_db_index_physical_stats (DB_ID(N'MyJoyDatabase'),OBJECT_ID(N'ta'),NULL,NULL,NULL)AS a 
JOIN sys.indexes AS b ON a.object_id=b.object_id AND a.index_id = b.index_id;
没有索引碎片DBCC SHOW_STATISTICS('ta','PK__ta__3213E83F42E1EEFE')也没有索引统计信息难道是页面分配表中没有更新空间使用情况？求解

Answer 11

楼主可以以这个为题，研究夏。 使用dbcc 命令可以观察到数据前后的变化情况，那么楼主自然就明白了原因。

Answer 12

DBCC IND(test,tb,0)
DBCC PAGE
两个命令就够研究这个内容了

Answer 13

个人看法：
sql规定行数据不能超过一个页也就是8k（8192字节）
系统预留的部分空间，这样最大存储只能是8060字节.针对7#的脚本，把nvarchar类型换成ntext就不存在这个错误了。

Answer 14

实际上这里的问题应该追加为“为了已经drop column，但行数据限制仍存在”的问题。并非某个数据类型的限制，因为是先有了错误，才有了这些重现语句。当然避免和处理的方法很简单，大家在做分区数据时都有体会，数据不会随着意愿而移动，即使做了分区函数和方案仍要通过索引来迁移数据到相应的位置。通过前后对比时，在图2中我们可以发现数据页已经记录了drop column的存在，但数据没有转移，仍需要在该区中继续存储数据，那么除非重新给它分配IAM链。图1
图2
图3

Answer 15

楼上牛人啊小弟本想抽空研究下的，还是自己太懒
下面是个人拙见：
这个问题是不是和临时库逐渐增大类似呢？在一个会话中创建的临时表很大，导致临时库tempdb扩充了。当会话结束关掉，临时库的大小还是那么大，虽然里面内容没那么多，必须得手工收缩数据库。

Answer 16

这个以前关注过，印象中存储引擎那本书也有说，石头哥也做过博文
http://blog.csdn.net/happyflystone/article/details/4923803

Answer 17

#13楼 得分：0回复于：2011-11-08 11:10:00SQL code    use dbx; go drop table t; create table t(v nvarchar(4000),v1 nvarchar(4000)); go alter table t add x nvarchar(4000); insert into t select 'aaasdfdsfds','sadfsadfdsf',replicate('x',4000) select * from t; alter table t drop column x; select * from t; update t set v=REPLICATE('a',4000),v1=REPLICATE('a',4000) /* 消息 511，级别 16，状态 1，第 1 行不能创建大小为 8063 的行，该大小大于所允许的最大行大小 8060。语句已终止。 */ exec sp_spaceused t

Answer 18

引用 8 楼 ap0405140 的回复:感謝7樓,重現了這個問題.如何解釋drop column col1後update col3的報錯.一定要重建索引才行嗎.
正如9楼所说，删除列时其实并没有从数据页中删除该列。
至于为什么重建索引可以，可能就是它触发SQL SERVER真正从数据页中删除该列了。
（可能还存在其他动作也会触发真正的删除吧。）