将列从 varchar(8000) 更改为 varchar(max) 的任何隐藏陷阱? [英] Any hidden pitfalls changing a column from varchar(8000) to varchar(max)?

问题描述

我有很多(超过一千个地方)遗留的 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 INSERTs 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 a varchar(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:

  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
  • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.

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) 的任何隐藏陷阱?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！