在SQL Server中使用varchar(MAX)与TEXT的区别

Question

我刚刚了解到，在SQL Server2005和下一个SQL SERVER版本中，VARCHAR(MAX)数据类型（可以存储接近2GB的字符数据）是TEXT数据类型的推荐替代品。

如果我想在列中搜索任何字符串，哪种操作更快？

1. 对VARCHAR(MAX)列使用LIKE子句？

WHERE COL1 LIKE '%search string%'

2. 使用TEXT列并在此列上放置 Full Text Index / Catalog ，然后使用CONTAINS子句进行搜索？

WHERE CONTAINS (Col1, 'MyToken')

- user85116

---

这篇文章也很有帮助：https://dev59.com/9HRB5IYBdhLWcg3wpYio - Jake

这篇文章中最重要的提到是一个链接指向MSDN文档，显示TEXT、NTEXT（和IMAGE）已被弃用。 - Brian

请查看以下链接：https://dev59.com/I14b5IYBdhLWcg3wiSTV - vicky

请注意，TEXT正在被弃用，如果可以选择，请不要使用TEXT。https://stackoverflow.com/questions/65625298/when-will-ntext-text-and-image-be-deprecated - DaFi4

Answer 1

VARCHAR(MAX)类型是TEXT的替代品。基本区别在于，TEXT类型总是将数据存储在blob中，而VARCHAR(MAX)类型会尝试将数据直接存储在行中，除非数据超过8k的限制，那么它将把它存储在blob中。

使用LIKE语句在这两种数据类型之间没有区别。 VARCHAR(MAX)提供的附加功能是，它也可以像其他VARCHAR列一样与=和GROUP BY一起使用。但是，如果你有很多数据，使用这些方法会有巨大的性能问题。

关于是否应该使用LIKE搜索，还是使用全文索引和CONTAINS。无论是VARCHAR(MAX)还是TEXT，这个问题都是一样的。

如果你正在搜索大量文本，并且性能至关重要，那么你应该使用全文索引。

LIKE更容易实现，通常适用于小数据量，但是由于无法使用索引，所以在大数据量下的性能非常差。

- Robin Day

---

我不知道它会在页面上存储8k，如果更大就会超出页面。非常酷。 - Brain2000

你的最后一句话部分错误。如果通配符在搜索的字符串开头，LIKE操作无法使用索引。 - SouravA

从一个已有数据的表中将一个字段从文本类型更改为varchar(max)类型，这样做没有问题吗？ - user1531040

我在官方文档中找不到关于“varchar超过8k将存储在blob中”的描述，请问你能提供一个相关链接吗？ - undefined

Answer 2

对于大型文本，全文索引速度更快。但你也可以将varchar(max)进行全文索引。

- Joel Coehoorn

Answer 3

• 基本定义

TEXT和VarChar(MAX)是非Unicode的大型可变长度字符数据类型，最多可以存储2,147,483,647个非Unicode字符（即最大存储容量为：2GB）。

• 该使用哪一个？

根据微软官方文献MSDN的建议，应避免使用TEXT数据类型，并且在未来版本的SQL Server中将删除该数据类型。相比之下，VarChar(MAX) 是建议用于存储大字符串值的数据类型，而不是TEXT数据类型。

• 行内或离线存储

TEXT类型列的数据存储在单独的LOB数据页中，与表数据页中的行只有一个16字节指针指向实际数据所在的LOB数据页。如果VarChar(MAX)类型列的值少于或等于8000个字节，则其数据存储在行内。如果VarChar(MAX)列值大于8000个字节，则VarChar(MAX)列值存储在单独的LOB数据页中，与表数据页中的行只有一个16字节指针指向实际数据所在的LOB数据页。因此，“行内”VarChar(MAX)对于搜索和检索很有用。

• 支持/不支持的功能

一些字符串函数、运算符和结构不能在TEXT类型列上运行，但可以在VarChar(MAX)类型列上运行。

1. = 在VarChar(MAX)类型列上的等于运算符
2. GROUP BY 在VarChar(MAX)类型列上的子句

• 系统 IO 注意事项

我们知道，当VarChar(MAX)类型的列值长度大于8000字节或行内空间不够时，其存储会超出行范围。否则，它将在行内进行存储。因此，如果存储在VarChar(MAX)列中的大多数值都很大且超出行范围，则数据检索行为几乎类似于TEXT类型的列。

如果存储在VarChar(MAX)类型的列中的大多数值都足够小以便在行内存储，则在不包括 LOB 列的数据检索时需要读取更多的数据页面，因为 LOB 列的值在与非 LOB 列值存储在同一数据页面的行内进行存储。但是，如果SELECT查询包含 LOB 列，则与TEXT类型列相比，数据检索所需读取的页面会更少。

结论

为了获得更好的性能，请使用VarChar(MAX)数据类型而不是TEXT。

来源

- Somnath Muluk

Answer 4

你无法在不将文本字段从文本转换为varchar的情况下进行搜索。

DECLARE @table TABLE (a text)
INSERT INTO @table VALUES ('a')
INSERT INTO @table VALUES ('a')
INSERT INTO @table VALUES ('b')
INSERT INTO @table VALUES ('c')
INSERT INTO @table VALUES ('d')


SELECT *
FROM @table
WHERE a = 'a'

这将给您带来错误：

text和varchar数据类型在“等于”操作符中不兼容。

但是，以下不会：

DECLARE @table TABLE (a varchar(max))

有趣的是，LIKE仍然起作用，即.

WHERE a LIKE '%a%'

- DForck42

Answer 5

如果使用 MS Access （尤其是像2003这样的旧版本），你被迫在 SQL Server 上使用 TEXT 数据类型，因为 MS Access 不将 nvarchar(MAX) 识别为 Access 中的 Memo 字段，而 TEXT 被识别为 Memo 字段。

- Klaus Oberdalhoff