JAVA高级面试进阶训练营视频教程

admin

问题的现象
在创建表A的时候，出现“信息 511，级别 16，状态 1，第 5 行  无法创建大小为 的行，该值大于允许的最大值 8060。”的信息提示。很奇怪，网上查了一下，是因为要插入表的数据类型的定义类型小了些，把对应数据类型的大小调整一下就可好些了。
但新问题又出现了，后来由于将表A的数据Select Into的形式写入表B，表A的数据结构不明确，因为来自表A的数据是动态的列，是经过列转行的形式转换的数据信息，不能确定对应列的个数和列的名称，那怎么办呢？新思路是能不能将整个表的所有列的数据类型转换为同一个放大的数据类型，这个问题能解决吗？只简单测试一下如何将一个表的所有数据类型转换为统一的类型的方法 。下面是解决分析：

问题解决
1.对问题现象理解
1).现象描述
一般出现这种现象都是适用sql文件在查询分析器里建库的时候，现象一般都是提示：
SQL Server创建表 'xxxx'，但其最大行大小（10438）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败。
其中xxxx是你的建的表名，10438是你建表语句中可变长度列（如 nvarchar 或 varbinary）的总长度，8060是SQL Server对行长度的最大限制。
2).现象原因
因为SQL Server创建表语句中可变长度列的总长度超过了SQL Server对行最大长度的限制8060。如果每一行中数据的总长度不超过8060 字节，就仍可以向表中插入行。但是如果数据超过8060 字节，因此系统提示你就会出现插入或更新操作失败。

错误提示：
　　服务器：信息 511，级别 16，状态 1，第 5 行 无法创建大小为 的行，该值大于允许的最大值 8060。 语句已终止。

举个例子：
比如我总共有10块钱，买A东西可能花1-5块，买B东西可能花2-3块，买C东西可能花3-6块，那我在做预算的时候就要提醒自己，如果ABC三个东西都要花上限的钱，那我的钱可就不够了，因为5+3+6=14 >10，虽然可能我只花了1+2+3=6块钱就把ABC全买了。
3).常用方法
修改SQL Server创建表语句中相应的列的数据类型或长度（如将nvarchar格式改成text），让可变长度列的加和小于8060。这样可以彻底避免出现上述错误发生，当然上述的错误并不是必然出现。即放大表的数据存储空间。

2.对问题解决
针对上面的动态表的情况，如何避免这种问题呢？上面说的方式可以试一下，将表的所有数据列的数据类型统一定义为一个放大的数据类型，当然前提是表的原有数据类型和将要转换后的数据类型，是可以进行数据类型互转的，不然将会报错无法执行。其实是修改表，修改列的类型进行数据类型转换。
1).创建测试表

--创建测试表
IF OBJECT_ID('TestTB') IS NOT NULL      
    DROP TABLE TestTB
    
CREATE TABLE TestTB
(
 id int,
 NAME varchar(60),
 sex bit
)

--插入测试表数据
INSERT TestTB( id, NAME, sex )
VALUES  ( 0, -- id - int
          '99', -- NAME - varchar(60)
          1  -- sex - bit
          )
INSERT TestTB( id, NAME, sex )
VALUES  ( '44', -- id - int
        '22测试数据1', -- NAME - varchar(60)
        0 -- sex - bit
        )
SELECT * FROM TestTB

2).修改测试表的所有数据列

--使用游标遍历更新表的所有列
declare tb cursor for
SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] varchar(200)'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
where d.name='TestTB'  
and 
not exists(
    SELECT 1 FROM sysobjects where xtype='PK' and name in (
    SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid)
)) --** 主键不能修改,所以有 not exists 条件
order by d.name,a.name
 
declare  @sql  varchar(1000)  
open  tb  
fetch  next  from  tb  into  @sql  
while  @@fetch_status  =  0  
begin  
           exec(@sql)  
           fetch  next  from  tb  into  @sql  
end  
close  tb  
deallocate  tb  
go  

其实执行的就是游标遍历表的所有列更新表更新列的数据类型，游标遍历执行SQL：

--其中拉取的遍历执行的SQL
SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] varchar(200)'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
WHERE d.NAME='TestTB'

提示:（在使用游标进行表的列的数据类型转换时，必须是可以转换的数据列，如：上面的TestTB表，将所有列转换的varchar(200)改为int类型就会报错。）
3).查看表的信息

--查询表的信息，查看是否修改数值类型
SELECT (case when a.colorder=1 then d.name else null end) 表名, a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, 
(case when (SELECT count(*) FROM sysobjects  WHERE (name in (
    SELECT name FROM sysindexes  WHERE (id = a.id) AND (indid in  
    (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  
        (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,  
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,  
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM  syscolumns a 
left join systypes b on a.xtype=b.xusertype  
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id  
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
--where b.name is not null
--WHERE d.name='TestTB' --如果只查询指定表,加上此条件
WHERE d.NAME LIKE '%TestTB%' --如果只查询指定表,加上此条件
order by a.id,a.colorder

其实可以在执行游标修改表结构前后调用，查看表的定义信息。

参考博客：
《SQL Server创建表最大行超过限制的解决方法》
《批量将一个库里的所有表里的char改成nchar类型-数据库专栏,SQL Server》
《SQLSERVER如何获取一个数据库中的所有表的名称、一个表中所有字段的名称》