双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

被MSSQL耍了

2012-04-10 09:49:22 9.3k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

guguda2008 墨衍会员 · AI 创作全网分发关注 + 加入社区

Adminer 连接mssql sqlserver ./freetds/freetds.conf:/etc/freetds.conf:rw # 确保 :rw 可读写。Adminer 安装mssql sqlserver php 驱动；adminer 安装pdo_dblib mssql;这个版本的镜像不用自己安装驱动了。耗时一个美好的周六下午。阅读详情

1.建表，报警告，行最大大小超过8060，INSERT和UPDATE可能失败

2.插入数据，报超过8060，插入失败

3.用ALTER TABLE ALTER COLUMN COL_NAME VARCHAR(MAX)把所有字符串列全改成VARCHAR(MAX)，其它列长度加起来共320

4.插入数据，报超过8060，插入失败

就这样，被华丽丽的刷了，虽然现在已经解决了，但还是郁闷。

第一个详细解释原因的给250技术分，谨以此分献给MS

...全文

在DJANGO中要连接mssql（sqlserver）如何配置,实测可用现在，你的Django项目已配置为使用MSSQL数据库。你可以在Django中使用ORM进行数据库操作，并与MSSQL进行交互。网上找了大量的资料测试过都解决不了django连接sqlserver的问题。设置中，设置MSSQL连接的相关配置。在DJANGO中要连接mssql如何配置。这些配置是可选的，根据你的需求进行设置。首先，确保你的Python环境中已安装。经过好几天公关终于解决了这个难题。打开Django项目中的。阅读详情

guguda2008

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

评论 158

切换为时间正序

黄_瓜 2013-08-12

打赏
举报

引用 146 楼 bala7229291 的回复:

今天早上看见楼主的数据，但是忙着出差没来得及详细分析，我也写写我的分析吧


--测试
--使用临时数据库
use tempdb;
go

--创建测试表tb_test
if object_id('tb_test') is not null
	drop table tb_test;
go

create table tb_test
(
	col1 uniqueidentifier,
	col2 nchar(4000),
	col3 nvarchar(4000),
	col4 nvarchar(4000),
	constraint PK_TEST primary key(col1)
);
go
/*
警告: 行的最大长度超过了所允许的上限(8060 个字节)。
	  对于某些大值组合，插入/更新操作将失败。

分析：因为nchar和nvarchar数据类型使用两个字节存储一个字符，
	  所以各列的最大长度分别为：16B、8000B、8000B、8000B。
	  共计24016B（不包括额外的磁盘开销），超过了行的最大
	  长度8060B，但由于定长数据nchar和uniqueidentifier的
	  总长度为8016，未超过8060的限制，而变长数据可以存储到
	  行溢出数据中，因此SQL Server并不阻止此创建语句的执行，
	  但给出警告，因为存在某些大值组合确实会导致行超过8060
	  的限制。（此种情况已在25楼的时候讨论过，这里略过）
*/

--查看表中所有行的分配情况
select c.name as column_name, c.column_id, 
	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
	inner join sys.partitions p
		on p.partition_id = pc.partition_id
	inner join sys.columns c
		on c.column_id = pc.partition_column_id and
		   c.object_id = p.object_id
where p.object_id = object_id('tb_test');
/*
column_name   column_id   max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1          1           16               36             4
col2          2           8000             239            20
col3          3           8000             231            -1
col4          4           8000             231            -2

从以上数据我们可以看出，leaf_offset是列数据在行内相对于行头的偏移量
即：col1从4字节开始，到19字节结束，共16字节。
    col2从20字节开始，因为是定长数据，长度为8000字节
    col3、col4属于变长数据，实际的长度因不同的记录不同而不同，这里
    不能指定准确的偏移量，-1、-2表示的是第一变长列和第二变长列。
*/

--插入记录
declare @string nchar(4000);
set @string = replicate('A', 4000);

insert into tb_test
select newid(), @string, @string, @string;
go
/*
消息 511，级别 16，状态 1，第 44 行
不能创建大小为 8077 的行，该大小大于所允许的最大值 8060。

分析：假设这条记录能成功插入，计算记录的长度
	  状态位占用2B，列数目的位置记录占用2B，列数目占用2B，
	  空值位图占用1B，变长列数目占用2B，两个变长列位置偏
	  移量占用4B，col1长16B，col2长8000B， col3只能是指针
	  24B，col4也只能是指针24B，共计8077字节，因超过8060B
	  长度限制，SQL Server不予插入此记录
*/

--为了知道数据内部存储的细节，先插入一条不超过限制的记录
declare @string nvarchar(5);
set @string = replicate('A', 5);

insert into tb_test
select newid(), @string, @string, @string;
go
/*
插入成功
(1 行受影响)

分析：计算记录的长度
	  状态位占用2B，列数目的位置记录占用2B，列数目占用2B，
	  空值位图占用1B，变长列数目占用2B，两个变长列位置偏
	  移量占用4B，col1长16B，col2长8000B， col3占用10B，
	  col4占用10B，共计8049B，因未超过8060限制，给予插入。
*/

--查看表占用的页面信息
dbcc ind(tempdb, tb_test, -1);
go
/*
PageFID PagePID     ObjectID    iam_chain_type       PageType 
------- ----------- ----------- -------------------- -------- 
1       93          245575913   In-row data          10       
1       77          245575913   In-row data          1       
1       174         245575913   Row-overflow data    10       
1       120         245575913   Row-overflow data    3        
1       41         245575913   Row-overflow data    3

分析： PageType=1代表行内数据页，PageType=3代表行溢出数据页
	   虽然之前插入超过限制的数据并没有成功，但是SQL Server
	   已经分配了行溢出数据的页，即col3和col4原本要放入行溢
	   出数据页内，但由于行内只存放指针的空间都不够，于是之前
	   插入的数据即使没有成功，但行溢出页已经分配。       
*/

--查看行内数据行实际使用情况
dbcc traceon(3604);
go

dbcc page(tempdb, 1, 77, 1);
go
/*
结果略
分析略
细节显示与上面的分析一致
*/

--删除已插入数据，以便后面的测试
delete tb_test;
/*
执行成功
(1 行受影响)
*/

--修改表结构，修改col2
alter table tb_test 
	alter column col2 nvarchar(max);
go
/*
警告: 已经创建表 'tb_test'，但是它的最大行大小超过了允许的
	  最大字节数 8060。如果得到的行超过此大小限制，则对此
	  表的 INSERT 或 UPDATE 操作将失败。
*/

--查看表中所有行的分配情况
select c.name as column_name, c.column_id, 
	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
	inner join sys.partitions p
		on p.partition_id = pc.partition_id
	inner join sys.columns c
		on c.column_id = pc.partition_column_id and
		   c.object_id = p.object_id
where p.object_id = object_id('tb_test');
go
/*
column_name   column_id   max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1          1           16               36             4
col2          2           8000             231            -3
col3          3           8000             231            -1
col4          4           8000             231            -2

从以上数据我们可以看出，col2的数据类型已经改变，并且修改后的col2成为
第三个变长列，物理上紧跟col4的后面，至于上面的警告怎么出现，将在后面
的测试中说明
*/

--继续插入一条数据查看物理存储细节
declare @string nvarchar(1);
set @string = replicate('A', 1);

insert into tb_test
select newid(), @string, @string, @string;
go
/*
执行成功
(1 行受影响)
*/

--查看物理页存储细节
dbcc traceon(3604);
go

dbcc page(tempdb, 1, 77, 1);
go
/*
通过第3、4字节可以在偏移量8020字节后找到两字节的列数目，
通过查看可以看到有5列数据存在（元数据显示只有4列），另外
可以看到变长列只有3个，数据显示是col2、col3、col4就存在
这3个变长列中。
通过数据还可以看出修改之前的8000字节nchar数据长度还预留
在原先的位置，也就是说还在占用行的长度，因此除去定长的
数据以及额外的开销，留给3个变长列的长度为8060-8031= 29B

也就是说实际上以上的修改实际上等同于增加1个nvarchar(max)
的列（物理上有5个列，而元数据只有4个列），因此SQL Server对
增加一个列的这种行为进行了告警，并且这种超过长度限制的情况
是可能存在的。
*/

--删除测试数据
delete tb_test;
go

--继续修改col2
alter table tb_test 
	alter column col3 nvarchar(max);
go
/*
执行成功
(1 行受影响)
*/

--查看表中所有行的分配情况
select c.name as column_name, c.column_id, 
	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
	inner join sys.partitions p
		on p.partition_id = pc.partition_id
	inner join sys.columns c
		on c.column_id = pc.partition_column_id and
		   c.object_id = p.object_id
where p.object_id = object_id('tb_test');
go
/*
column_name   column_id   max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1          1           16               36             4
col2          2           8000             231            -3
col3          3           8000             231            -4
col4          4           8000             231            -2

从以上数据我们可以看出，col3的数据类型已经改变，并且修改后的col2成为
第4个变长列，物理上紧跟col2的后面
*/

--继续插入一条数据查看物理存储细节
declare @string nvarchar(1);
set @string = replicate('A', 1);

insert into tb_test
select newid(), @string, @string, @string;
go
/*
执行成功
(1 行受影响)
*/

--查看物理页存储细节
dbcc traceon(3604);
go

dbcc page(tempdb, 1, 77, 1);
go
/*
通过数据可以看到，行的物理列数有6个，而变长列有4个
但第一个变长列没有内容
可以看出修改之前的偏移量字节还继续占用，增加了两字节的
第4变长列的偏移量，留给3个变长列的长度为8060-8033= 27B
*/

--删除测试数据
delete tb_test;
go

--继续修改col4
alter table tb_test 
	alter column col4 nvarchar(max);
go
/*
与上面的测试一样

通过数据可以看到，行的物理列数有7个，而变长列有5个
但第1、2个变长列没有内容
可以看出修改之前的偏移量字节还继续占用，增加了两字节的
第5变长列的偏移量，留给3个变长列的长度为8060-8035= 25B
*/

declare @string nvarchar(4000)
select @string = replicate('A',4000)
insert into tb_test
select newid(),@string, @string, @string
go
/*
消息 511，级别 16，状态 1，第 3 行
不能创建大小为 8107 的行，该大小大于所允许的最大值 8060。
语句已终止。

因为只剩下25B的空间，因此3个变长列都将被移到行溢出数据
中，但是即使只保存指针也需要24*3=72B的空间，于是SQL Server
报错，8035 + 72 = 8017B > 8060
*/

楼主的后面重建表的测试不用进行了，其实问题已经很清楚。 SQL Server对于表结构的修改采取三种方式： 1.只修改元数据，也就是在sys.columns这些系统目录中的数据。 2.修改元数据并且检查现有的数据行。 3.修改元数据并且修改实际的数据行。楼主的情况基本属于第一种和第二种。其实写程序的人都有这样的感觉，应用中效率和占用的资源往往是一个矛盾的，就以楼主的情况来看，要释放曾经占用的资源，那么修改表的效率就要下降，特别是表里有大量数据的。怎么找到一种平衡呢? 上面SQL Server采用的三种方式正好体现了这个原则，从上往下效率在降低，但是有助于释放修改前数据占用的资源空间。在一般的应用中，使用数据行的极限空间的情况毕竟是很少的，而且面对修改表结构，使用者更注重的效率，也可以简单的理解为速度，当然也不是说就不考虑空间释放问题，楼主最后的中间表的方法固然可行，但是在实际应用中表中的数据就丢失了。其实SQL Server在注重效率的同时，并不是就放弃对空间释放的满足，其实也提供一种方式来解决这种问题，我个人认为最好的办法还是重建索引


--重建索引
alter index PK_TEST on tb_test rebuild;
go

declare @string nvarchar(4000)
select @string = replicate('A',4000)

insert into tb_test
select newid(),@string, @string, @string
go
/*
执行成功
(1 行受影响)
*/

通过楼主的问题，可以看出，对于经常修改表结构的表，应该定时进行索引重建，避免出现类似楼主相似问题的发生。

吊炸天，学习受教了之前说的以前肯定的主管判断确实太过自负了。

menglong35 2012-12-14

打赏
举报

高手真多啊，基本明白意思了，我也遇到同样的状况，但是不会计算行的最大长度。怎么计算都不到8062 。alter table ISSUE_D2 alter column STATUS nvarchar(1) not null 报错：消息511，级别16，状态1，第1 行不能创建大小为8062 的行，该大小大于所允许的最大行大小8060。语句已终止。重新创建表就OK，老的就不行，那位高手帮帮看看是怎么计算的？


/****** Object:  Table [dbo].[ISSUE_D2]    Script Date: 12/14/2012 12:11:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ISSUE_D2](
	[SequenceNumber] [int] NOT NULL,
	[ISSUE_D2_ID] [uniqueidentifier] NOT NULL,
	[ISSUE_DESCRIPTION] [ntext] NOT NULL,
	[WITH_PHOTO] [ntext] NOT NULL,
	[STATUS] [nvarchar](20) NOT NULL,
	[ISSUE_TYPE_ID] [uniqueidentifier] NOT NULL,
	[REPLY_DESCRIPTION] [ntext] NOT NULL,
	[VERIFICATION] [int] NOT NULL,
	[VERIFICATION_DESCRIPTION] [ntext] NOT NULL,
	[RETURN_NUM] [int] NOT NULL,
	[TRANSFER_DOC_NO] [nvarchar](20) NOT NULL,
	[CLOSED_DATE] [datetime2](7) NOT NULL,
	[CLOSED_DAYS] [int] NOT NULL,
	[VERIFICATION_DATE] [datetime2](7) NOT NULL,
	[CreateDate] [datetime2](7) NOT NULL,
	[LastModifiedDate] [datetime2](7) NOT NULL,
	[CreateBy] [uniqueidentifier] NOT NULL,
	[LastModifiedBy] [uniqueidentifier] NOT NULL,
	[Version] [timestamp] NOT NULL,
	[ISSUE_ID] [uniqueidentifier] NOT NULL,
	[mmm] [bit] NOT NULL,
 CONSTRAINT [PK_ISSUE_D2] PRIMARY KEY NONCLUSTERED 
(
	[ISSUE_D2_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'SequenceNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主鍵' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_D2_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附圖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'WITH_PHOTO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題狀態' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'STATUS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題分類' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_TYPE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'回復說明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'REPLY_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'反映人驗證狀態' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證說明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證退回次數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'RETURN_NUM'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'轉單單號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'TRANSFER_DOC_NO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'結案日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CLOSED_DATE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'結案天數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CLOSED_DAYS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證完成日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION_DATE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'LastModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CreateBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'LastModifiedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本号，不要随意更改' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'Version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'议题明细/CHT/議題明細' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2'
GO

ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ((0)) FOR [SequenceNumber]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [ISSUE_D2_ID]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [ISSUE_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [WITH_PHOTO]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [STATUS]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [ISSUE_TYPE_ID]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [REPLY_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ((0)) FOR [VERIFICATION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [VERIFICATION_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ((0)) FOR [RETURN_NUM]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT (' ') FOR [TRANSFER_DOC_NO]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('1900-01-01 00:00:00') FOR [CLOSED_DATE]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ((0)) FOR [CLOSED_DAYS]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('1900-01-01 00:00:00') FOR [VERIFICATION_DATE]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('1900-01-01 00:00:00') FOR [CreateDate]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('1900-01-01 00:00:00') FOR [LastModifiedDate]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [CreateBy]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [LastModifiedBy]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD  CONSTRAINT [DF_ISSUE_D2_mmm]  DEFAULT ((0)) FOR [mmm]
GO

garydocy 2012-05-26

打赏
举报

有点不懂

guguda2008 2012-04-23

打赏
举报

[Quote=引用 25 楼的回复:]

引用楼主的回复:
1.建表，报警告，行最大大小超过8060，INSERT和UPDATE可能失败

2.插入数据，报超过8060，插入失败

3.用ALTER TABLE ALTER COLUMN COL_NAME VARCHAR(MAX)把所有字符串列全改成VARCHAR(MAX)，其它列长度加起来共320

4.插入数据，报超过8060，插入失败

就这样，被华丽丽的……
[/Quote]

最完整详实的分析了，感谢从数据页的层面分析原因~结帖了

lcw321321 2012-04-22

打赏
举报

我记得有这样一个说法，当alter table 的列的时候，包括drop column 以及更改列的属性，这个时候原来的列的存储空间会保留。
所以会出现楼主这样的情况（alter table 还是不行，drop table 然后creat table 就行了。），如果不出意外的话，不用重建一张新表。
你重建该表的聚集索引，估计也不会报异常了。

mfkmu 2012-04-21

打赏
举报

不可能吧。

游戏精灵 2012-04-20

打赏
举报

程序开发正是强大。

bala7229291 2012-04-20

打赏
举报

今天早上看见楼主的数据，但是忙着出差没来得及详细分析，我也写写我的分析吧



--测试

--使用临时数据库

use tempdb;

go



--创建测试表tb_test

if object_id('tb_test') is not null

	drop table tb_test;

go



create table tb_test

(

	col1 uniqueidentifier,

	col2 nchar(4000),

	col3 nvarchar(4000),

	col4 nvarchar(4000),

	constraint PK_TEST primary key(col1)

);

go

/*

警告: 行的最大长度超过了所允许的上限(8060 个字节)。

	  对于某些大值组合，插入/更新操作将失败。



分析：因为nchar和nvarchar数据类型使用两个字节存储一个字符，

	  所以各列的最大长度分别为：16B、8000B、8000B、8000B。

	  共计24016B（不包括额外的磁盘开销），超过了行的最大

	  长度8060B，但由于定长数据nchar和uniqueidentifier的

	  总长度为8016，未超过8060的限制，而变长数据可以存储到

	  行溢出数据中，因此SQL Server并不阻止此创建语句的执行，

	  但给出警告，因为存在某些大值组合确实会导致行超过8060

	  的限制。（此种情况已在25楼的时候讨论过，这里略过）

*/



--查看表中所有行的分配情况

select c.name as column_name, c.column_id, 

	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset

from sys.system_internals_partition_columns pc

	inner join sys.partitions p

		on p.partition_id = pc.partition_id

	inner join sys.columns c

		on c.column_id = pc.partition_column_id and

		   c.object_id = p.object_id

where p.object_id = object_id('tb_test');

/*

column_name   column_id   max_inrow_length system_type_id leaf_offset

------------- ----------- ---------------- -------------- -----------

col1          1           16               36             4

col2          2           8000             239            20

col3          3           8000             231            -1

col4          4           8000             231            -2



从以上数据我们可以看出，leaf_offset是列数据在行内相对于行头的偏移量

即：col1从4字节开始，到19字节结束，共16字节。

    col2从20字节开始，因为是定长数据，长度为8000字节

    col3、col4属于变长数据，实际的长度因不同的记录不同而不同，这里

    不能指定准确的偏移量，-1、-2表示的是第一变长列和第二变长列。

*/



--插入记录

declare @string nchar(4000);

set @string = replicate('A', 4000);



insert into tb_test

select newid(), @string, @string, @string;

go

/*

消息 511，级别 16，状态 1，第 44 行

不能创建大小为 8077 的行，该大小大于所允许的最大值 8060。



分析：假设这条记录能成功插入，计算记录的长度

	  状态位占用2B，列数目的位置记录占用2B，列数目占用2B，

	  空值位图占用1B，变长列数目占用2B，两个变长列位置偏

	  移量占用4B，col1长16B，col2长8000B， col3只能是指针

	  24B，col4也只能是指针24B，共计8077字节，因超过8060B

	  长度限制，SQL Server不予插入此记录

*/



--为了知道数据内部存储的细节，先插入一条不超过限制的记录

declare @string nvarchar(5);

set @string = replicate('A', 5);



insert into tb_test

select newid(), @string, @string, @string;

go

/*

插入成功

(1 行受影响)



分析：计算记录的长度

	  状态位占用2B，列数目的位置记录占用2B，列数目占用2B，

	  空值位图占用1B，变长列数目占用2B，两个变长列位置偏

	  移量占用4B，col1长16B，col2长8000B， col3占用10B，

	  col4占用10B，共计8049B，因未超过8060限制，给予插入。

*/



--查看表占用的页面信息

dbcc ind(tempdb, tb_test, -1);

go

/*

PageFID PagePID     ObjectID    iam_chain_type       PageType 

------- ----------- ----------- -------------------- -------- 

1       93          245575913   In-row data          10       

1       77          245575913   In-row data          1       

1       174         245575913   Row-overflow data    10       

1       120         245575913   Row-overflow data    3        

1       41         245575913   Row-overflow data    3



分析： PageType=1代表行内数据页，PageType=3代表行溢出数据页

	   虽然之前插入超过限制的数据并没有成功，但是SQL Server

	   已经分配了行溢出数据的页，即col3和col4原本要放入行溢

	   出数据页内，但由于行内只存放指针的空间都不够，于是之前

	   插入的数据即使没有成功，但行溢出页已经分配。       

*/



--查看行内数据行实际使用情况

dbcc traceon(3604);

go



dbcc page(tempdb, 1, 77, 1);

go

/*

结果略

分析略

细节显示与上面的分析一致

*/



--删除已插入数据，以便后面的测试

delete tb_test;

/*

执行成功

(1 行受影响)

*/



--修改表结构，修改col2

alter table tb_test 

	alter column col2 nvarchar(max);

go

/*

警告: 已经创建表 'tb_test'，但是它的最大行大小超过了允许的

	  最大字节数 8060。如果得到的行超过此大小限制，则对此

	  表的 INSERT 或 UPDATE 操作将失败。

*/



--查看表中所有行的分配情况

select c.name as column_name, c.column_id, 

	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset

from sys.system_internals_partition_columns pc

	inner join sys.partitions p

		on p.partition_id = pc.partition_id

	inner join sys.columns c

		on c.column_id = pc.partition_column_id and

		   c.object_id = p.object_id

where p.object_id = object_id('tb_test');

go

/*

column_name   column_id   max_inrow_length system_type_id leaf_offset

------------- ----------- ---------------- -------------- -----------

col1          1           16               36             4

col2          2           8000             231            -3

col3          3           8000             231            -1

col4          4           8000             231            -2



从以上数据我们可以看出，col2的数据类型已经改变，并且修改后的col2成为

第三个变长列，物理上紧跟col4的后面，至于上面的警告怎么出现，将在后面

的测试中说明

*/



--继续插入一条数据查看物理存储细节

declare @string nvarchar(1);

set @string = replicate('A', 1);



insert into tb_test

select newid(), @string, @string, @string;

go

/*

执行成功

(1 行受影响)

*/



--查看物理页存储细节

dbcc traceon(3604);

go



dbcc page(tempdb, 1, 77, 1);

go

/*

通过第3、4字节可以在偏移量8020字节后找到两字节的列数目，

通过查看可以看到有5列数据存在（元数据显示只有4列），另外

可以看到变长列只有3个，数据显示是col2、col3、col4就存在

这3个变长列中。

通过数据还可以看出修改之前的8000字节nchar数据长度还预留

在原先的位置，也就是说还在占用行的长度，因此除去定长的

数据以及额外的开销，留给3个变长列的长度为8060-8031= 29B



也就是说实际上以上的修改实际上等同于增加1个nvarchar(max)

的列（物理上有5个列，而元数据只有4个列），因此SQL Server对

增加一个列的这种行为进行了告警，并且这种超过长度限制的情况

是可能存在的。

*/



--删除测试数据

delete tb_test;

go



--继续修改col2

alter table tb_test 

	alter column col3 nvarchar(max);

go

/*

执行成功

(1 行受影响)

*/



--查看表中所有行的分配情况

select c.name as column_name, c.column_id, 

	   pc.max_inrow_length, pc.system_type_id, pc.leaf_offset

from sys.system_internals_partition_columns pc

	inner join sys.partitions p

		on p.partition_id = pc.partition_id

	inner join sys.columns c

		on c.column_id = pc.partition_column_id and

		   c.object_id = p.object_id

where p.object_id = object_id('tb_test');

go

/*

column_name   column_id   max_inrow_length system_type_id leaf_offset

------------- ----------- ---------------- -------------- -----------

col1          1           16               36             4

col2          2           8000             231            -3

col3          3           8000             231            -4

col4          4           8000             231            -2



从以上数据我们可以看出，col3的数据类型已经改变，并且修改后的col2成为

第4个变长列，物理上紧跟col2的后面

*/



--继续插入一条数据查看物理存储细节

declare @string nvarchar(1);

set @string = replicate('A', 1);



insert into tb_test

select newid(), @string, @string, @string;

go

/*

执行成功

(1 行受影响)

*/



--查看物理页存储细节

dbcc traceon(3604);

go



dbcc page(tempdb, 1, 77, 1);

go

/*

通过数据可以看到，行的物理列数有6个，而变长列有4个

但第一个变长列没有内容

可以看出修改之前的偏移量字节还继续占用，增加了两字节的

第4变长列的偏移量，留给3个变长列的长度为8060-8033= 27B

*/



--删除测试数据

delete tb_test;

go



--继续修改col4

alter table tb_test 

	alter column col4 nvarchar(max);

go

/*

与上面的测试一样



通过数据可以看到，行的物理列数有7个，而变长列有5个

但第1、2个变长列没有内容

可以看出修改之前的偏移量字节还继续占用，增加了两字节的

第5变长列的偏移量，留给3个变长列的长度为8060-8035= 25B

*/



declare @string nvarchar(4000)

select @string = replicate('A',4000)

insert into tb_test

select newid(),@string, @string, @string

go

/*

消息 511，级别 16，状态 1，第 3 行

不能创建大小为 8107 的行，该大小大于所允许的最大值 8060。

语句已终止。



因为只剩下25B的空间，因此3个变长列都将被移到行溢出数据

中，但是即使只保存指针也需要24*3=72B的空间，于是SQL Server

报错，8035 + 72 = 8017B > 8060

*/

楼主的后面重建表的测试不用进行了，其实问题已经很清楚。
SQL Server对于表结构的修改采取三种方式：

1.只修改元数据，也就是在sys.columns这些系统目录中的数据。
2.修改元数据并且检查现有的数据行。
3.修改元数据并且修改实际的数据行。

楼主的情况基本属于第一种和第二种。

其实写程序的人都有这样的感觉，应用中效率和占用的资源往往是一个矛盾的，
就以楼主的情况来看，要释放曾经占用的资源，那么修改表的效率就要下降，
特别是表里有大量数据的。怎么找到一种平衡呢?
上面SQL Server采用的三种方式正好体现了这个原则，从上往下效率在降低，
但是有助于释放修改前数据占用的资源空间。

在一般的应用中，使用数据行的极限空间的情况毕竟是很少的，而且面对修改
表结构，使用者更注重的效率，也可以简单的理解为速度，当然也不是说就不
考虑空间释放问题，楼主最后的中间表的方法固然可行，但是在实际应用中表中
的数据就丢失了。

其实SQL Server在注重效率的同时，并不是就放弃对空间释放的满足，其实也
提供一种方式来解决这种问题，我个人认为最好的办法还是重建索引



--重建索引

alter index PK_TEST on tb_test rebuild;

go



declare @string nvarchar(4000)

select @string = replicate('A',4000)



insert into tb_test

select newid(),@string, @string, @string

go

/*

执行成功

(1 行受影响)

*/

通过楼主的问题，可以看出，对于经常修改表结构的表，应该定时进行索引重建，避免出现类似楼主相似问题的发生。

billpu 2012-04-20

打赏
举报

很少看到有讨论数据结构的帖了
回复数是不少,不过大多都是水

测试一下楼主的代码

USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

COL1 UNIQUEIDENTIFIER PRIMARY KEY

,COL2 NCHAR(4000)

,COL3 NVARCHAR(4000)

,COL4 NVARCHAR(4000)

)

GO

DECLARE @STR NVARCHAR(4000)

SELECT @STR=REPLICATE('A',8000)

INSERT INTO TB

SELECT NEWID(),@STR,@STR,@STR

GO

--警告: 行的最大长度超过了所允许的上限(8060 个字节)。对于某些大值组合，插入/更新操作将失败。

--消息 511，级别 16，状态 1，第 3 行

--不能创建大小为 8077 的行，该大小大于所允许的最大值 8060。

呵呵我也来分析一把
状态A(1字节)+状态B(1字节)+列数据位(2字节)+定长uniqueidentify(16字节)+定长col2(8000字节)+列数目(2字节)+空位图(1字节)+变长列数目(2字节)+变长col3偏移(2字节)+变长col4偏移(2字节)+2个指向溢出页的指针48字节
一共是8077字节所以报错,这个前面有人也分析过了,只不过我分析的是楼主给的数据

ALTER TABLE TB ALTER COLUMN COL2 NVARCHAR(MAX)

ALTER TABLE TB ALTER COLUMN COL3 NVARCHAR(MAX)

ALTER TABLE TB ALTER COLUMN COL4 NVARCHAR(MAX)

GO

DECLARE @STR NVARCHAR(4000)

SELECT @STR=REPLICATE('A',4000)

INSERT INTO TB

SELECT NEWID(),@STR,@STR,@STR

GO

DROP TABLE TB

GO

--警告: 已经创建表 'TB'，但是它的最大行大小超过了允许的最大字节数 8060。如果得到的行超过此大小限--制，则对此表的 INSERT 或 UPDATE 操作将失败。

--消息 511，级别 16，状态 1，第 3 行

--不能创建大小为 8107 的行，该大小大于所允许的最大值 8060。

之所以这里是字节反而多了,这就是sqlserver的特色用alter table修改表大多数情况下,你修改的元数据确实是生效了,但是数据行反而大了.就算如果比数据长度原来小,sqlserver也不会去改动数据页的数据,只会检查数据,你这个例子原来的col1,col2,col3其实都在,只不过在后面又加了字段而已.所以这里数据行更会爆了(大于8060),还有另外nvarchar(max)不会存在常规数据页上,常规数据页上存在的只是指针而已

说到varchar(max)顺便讨论一下varchar(max),nvarchar(max),很多人觉得varchar(max)出来后可以彻底舍弃text我个人觉得其实在设计当中应该尽量避免varchar(max)/nvarchar(max),这个绝对不是个新东西,其实无非就是可变字段+text(ntext)而已,当数据列不超过8000就当作varchar(8000)或nvarchar(4000),超过8000/4000就是text/ntext,但是在数据页的划分,缓存命中率效率上会带来很多的问题.我做过测试,在写入效率上也并不见得效率比text高.
varchar(max)/nvarchar(max)方便的只是程序开发和应用而已.不过这个也没办法,微软看重的就是用户体验嘛.

最后就是刚刚前面有人争执的修改表是用ssms/alter table,这个我比较赞同Beirut同学的,微软当然不会不知道这个问题存在,但是现在sqlserver的架构就决定了这是个没办法的解决问题.所以楼主你说这个是不是bug,从应用层面来说我觉得这个是,但是从数据层面来说这个又不完全是.ssms在修改表结构的时候之所以要采取先删再建还是有道理的,说明他们早就明白.alter如果真的这么完美,为啥他们自己不用,海量数据下修改个表结构真的痛苦...

还有楼主(还是别人)提到的索引问题,其实楼主要解决这个问题,除了重建表还有个方法就是建立聚集索引,如果已经存在的话就重建.

暂时就这样了,好久不关心数据结构了今天又复习了一把,如果有说错请大家误喷,讨论而已

needanothercoder 2012-04-20

打赏
举报

duck, 祝贺你
以后你不会再同样被耍了
相信你不会两次落入同一个马葫芦

Tomzzu 2012-04-20

打赏
举报

一了百了，这样只要数据不超过2^31-1 个字节就行，而且在SQL2005中开销很小的



exec sp_tableoption '楼主的数据表', 'large value types out of row', 1;

啊的发哦 2012-04-20

打赏
举报

不是被MSSQL耍了，而是楼主学艺不精

guguda2008 2012-04-19

打赏
举报

[Quote=引用 134 楼的回复:]
对于楼主的测试我相当质疑的不是现象，而是楼主怎么测试的，为什么不能给出测试数据，如果给出测试数据通过数据库页面级的检查，很容易就知道问题的根结在哪里，何必一直在讲解原理嘛，原理是从实践中总结出来的。你就给出你的测试表的定义，怎么出错的，你有了确实的数据，大家讨论才能更进一步，如果你觉得怕泄露你的业务数据，那你自己编造一个测试数据嘛
[/Quote]
应你的要求，写了一个，分步运行就可以看到效果了



USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

COL1 UNIQUEIDENTIFIER PRIMARY KEY

,COL2 NCHAR(4000)

,COL3 NVARCHAR(4000)

,COL4 NVARCHAR(4000)

)

GO

DECLARE @STR NVARCHAR(4000)

SELECT @STR=REPLICATE('A',8000)

INSERT INTO TB

SELECT NEWID(),@STR,@STR,@STR

GO

ALTER TABLE TB ALTER COLUMN COL2 NVARCHAR(MAX)

ALTER TABLE TB ALTER COLUMN COL3 NVARCHAR(MAX)

ALTER TABLE TB ALTER COLUMN COL4 NVARCHAR(MAX)

GO

DECLARE @STR NVARCHAR(4000)

SELECT @STR=REPLICATE('A',4000)

INSERT INTO TB

SELECT NEWID(),@STR,@STR,@STR

GO

DROP TABLE TB

GO

CREATE TABLE TB(

COL1 UNIQUEIDENTIFIER PRIMARY KEY

,COL2 NVARCHAR(max)

,COL3 NVARCHAR(max)

,COL4 NVARCHAR(max)

)

go

DECLARE @STR NVARCHAR(4000)

SELECT @STR=REPLICATE('A',4000)

INSERT INTO TB

SELECT NEWID(),@STR,@STR,@STR

xmyohmy1 2012-04-19

打赏
举报

不太懂
不过为什么是8060 这个数值呢?

笑是我的药 2012-04-19

打赏
举报

表示Oracle好像没有这个限制，，字段个数限制1000~

笑是我的药 2012-04-19

打赏
举报

这个也遇到过，是MS的限制，，大数量列改成text等类型吧

bala7229291 2012-04-19

打赏
举报

[Quote=引用 122 楼的回复:]
恩，大概看了一下，似乎有很多人在解释为什么会出现这种报错信息，但这个其实不是问题，我之前就说我纠结的是明明已经把表改成溢出页存储，还会报同样的错。所以感谢这些打很多字和帖很多资料的同学的参与，但不是我希望看到的东西。

真正发出有效讨论的也就唐诗小爱和几个裤衩兄。下面就我感兴趣的几个讨论楼发表我的观点。

影子哥已经和我进行过线下讨论，直接无视。

25楼的三裤衩同学第一个指出问题……
[/Quote]
对于楼主的测试我相当质疑的不是现象，而是楼主怎么测试的，为什么不能给出测试数据，如果给出测试数据通过数据库页面级的检查，很容易就知道问题的根结在哪里，何必一直在讲解原理嘛，原理是从实践中总结出来的。你就给出你的测试表的定义，怎么出错的，你有了确实的数据，大家讨论才能更进一步，如果你觉得怕泄露你的业务数据，那你自己编造一个测试数据嘛

guguda2008 2012-04-18

打赏
举报

恩，大概看了一下，似乎有很多人在解释为什么会出现这种报错信息，但这个其实不是问题，我之前就说我纠结的是明明已经把表改成溢出页存储，还会报同样的错。所以感谢这些打很多字和帖很多资料的同学的参与，但不是我希望看到的东西。

真正发出有效讨论的也就唐诗小爱和几个裤衩兄。下面就我感兴趣的几个讨论楼发表我的观点。

影子哥已经和我进行过线下讨论，直接无视。

25楼的三裤衩同学第一个指出问题所在
----------------------------------------------------------------------------------------------
综上所述，我觉得，你的问题是因为维护溢出行的指针使得长度超过8060，你可以计算一下，如果一个溢出行的指针24字节，8060能存多少个这样的指针，如果有定长数据的时候，还得出去定长的长度。我相信你大概找出你的问题所在，可惜你没有测试数据，我只能按照我的理解来
----------------------------------------------------------------------------------------------
这里已经发现是在改了长度以后还报超长错误，也指出了自己的观点，事实上我的思路和他一样，也是第一时间汇总了一下表中所有列，包括指针列的长度，只是没在开贴时说明，导致这位同学分析依据不足，无法给出进一步的结论。

在我公布我的答案和解决方法之后的原因分析，我一概认为有马后炮嫌疑，不过唐诗同学的链接和其它另一们同学针对小爱的回答发起的讨论还是很有营养的。

唐诗的那个问题我也严重怀疑是同类问题，在重构了索引页后解决问题，但我没试过聚集索引是不是也能这样解决。下次如果再遇到，我会记得试试的。

关于小爱提的图形化配置问题，我不作其它辩驳，只说我之前遇到过的问题，我们的系统里，用过sql_varint类型的列存储不同来源的主键值，但因为后来发现的一些原因，决定把所有这些列全改成varchar(50)，这时我们的系统里，已经有了100多个表有这种列，这时再用图形化界面做，一个是它会删表重建，另一个是非常烦人而且大表会报超时错误。所以我的作法是先清理备份所有表涉及这列的约束，再删约束，再加新varchar列，再更新，再删原列，再新列改名，全部脚本大概写了不到半个小时，运行了三个小时左右后完成。如果是用图形化界面，四个小时能改多少个表呢？我还要处处盯着别报错。

所以我的观点是，中小型的非关键型单表操作用图形化界面更方便和保险，但重要的表以及批量操作还是应该用语句跑，尤其现在SQL2012也在向命令行服务器系统靠，还停留在图形化界面而不理解基操作的全部原理可能会有技术过时的风险。

50楼的三裤衩同学其实是和我的想法最接近的，为啥你MSSQL支持标准语句却不能保证它标准功能完成，给我留这么个坑，要不是我自己先测了一下，明天给人家一用立刻就要被投诉。然后自己图形化界面生成的语句还偷偷加了这么一步。就跟买了个冰箱带按钮自动开门，也没说手动开门前要先抬一下，但自动开门时自己会抬一下，然后用户手动开门出问题了，就说建议使用自动开门。

关于那些说我装神秘装B的，我把我遇到的没地方查的问题发上来后面还带解决方法，不是让你来骂的，是为了其它人遇到这种问题有地方查的，没在开始就公布答案只是想调动一下情绪而已，你如果觉得不爽直接按CTRL+W关掉窗口就好，发那种回复你是想说这个世界只有你一个人很懂技术不装B吗？

rczjp 2012-04-18

打赏
举报

8060是个什么数字，为什么是这个数字的限制呢

a11251126 2012-04-18

打赏
举报

高手云集，学习

加载更多回复（92）

✦双11狂欢大促✦

被MSSQL耍了

相关推荐

双11狂欢大促