问题描述
我有很多(超过一千个地方)遗留的 T-SQL
代码,它们只会将 INSERT
转换为 varchar(8000)
实用程序表中的列.我们的需求发生了变化,现在该列需要能够处理更大的值.因此,我需要将该列设为 varchar(max)
.这只是一个简单的数据列,其中没有执行搜索,没有索引,只有一个过程读取它,它是 INSERT
并忘记应用程序(几乎就像一个日志条目).
我计划只在少数几个地方进行更改,以实际生成更大的数据,并在处理此列的单个存储过程中进行更改.
- 将列从
varchar(8000)
更改为varchar(max)
是否有任何隐藏的陷阱? - 所有的
T-SQL
字符串函数是否都一样,LEN()
、RTRIM()
、SUBSTRING()
等 - 谁能想象我必须对认为该列仍然是
varchar(8000)
的代码进行任何更改的任何原因?
- 所有 MAX 类型都有小的性能损失,请参阅 varchar(max) 与 varchar(N) 的性能比较.
- 如果您的维护包括在线操作(在线索引重建),您将失去执行这些操作的可能性.具有 BLOB 列的表不支持在线操作:
- 当基础表包含大对象 (LOB) 数据类型时,必须离线创建、重建或删除聚集索引:image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml.
- 当表包含 LOB 数据类型但这些列都没有在索引定义中用作键或非键(包含)列时,可以在线创建非唯一非聚集索引.使用 LOB 数据类型列定义的非聚集索引必须离线创建或重建.
性能损失真的很小,所以我不担心.对于非常热的必须在线操作表来说,失去进行在线重建的能力可能会有问题.除非必须在线操作,否则我会投票支持它并将其更改为 MAX.
I have a lot (over a thousand places) of legacy T-SQL
code that only makes INSERT
s into a varchar(8000)
column in a utility table. Our needs have changed and now that column needs to be able to handle larger values. As a result I need to make that column varchar(max)
. This is just a plain data column where there are no searches preformed on it, no index on it, only one procedure reads it, it is INSERT
and forget for the application (almost like a log entry).
I plan on making changes in only a few places that will actually generate the larger data, and in the single stored procedure that processes this column.
- Are there any hidden pitfalls changing a column from
varchar(8000)
to avarchar(max)
? - Will all the
T-SQL
string functions work the same,LEN()
,RTRIM()
,SUBSTRING()
, etc. - Can anyone imagine any reason why I'd have to make any changes to the code that thinks the column is still
varchar(8000)
?
- All MAX types have a small performance penalty, see Performance comparison of varchar(max) vs. varchar(N).
- If your maintenance include online operations (online index rebuild), you will lose the possibility to do them. Online operations are not supported for tables with BLOB columns:
The performance penalty is really small, so I wouldn't worry about it. The loss of ability to do online rebuilds may be problematic for really hot must-be-online operations tables. Unless online operations are a must, I'd vote to go for it and change it to MAX.
这篇关于将列从 varchar(8000) 更改为 varchar(max) 的任何隐藏陷阱?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!