在 SQL Server 上使用 varchar(MAX) 与 TEXT

2021-12-08 00:00:00 performance text sql-server varchar sql-types

我刚刚读到 VARCHAR(MAX) 数据类型(可以存储接近 2GB 的字符数据)是 SQL Server 2005 中 TEXT 数据类型的推荐替代品和下一个 SQL SERVER 版本.

I just read that the VARCHAR(MAX) datatype (which can store close to 2GB of char data) is the recommended replacement for the TEXT datatype in SQL Server 2005 and Next SQL SERVER versions.

如果我想在列内搜索任何字符串，哪个操作更快?

If I want to search inside a column for any string, which operation is quicker?

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

WHERE COL1 LIKE '%search string%'

使用TEXT列并在此列上放置全文索引/目录，然后使用CONTAINS 子句?

Using the TEXT column and put a Full Text Index/Catalog on this column, and then search using the CONTAINS clause?

WHERE CONTAINS (Col1, 'MyToken')

推荐答案

VARCHAR(MAX) 类型是 TEXT 的替代.基本区别在于 TEXT 类型将始终将数据存储在 blob 中，而 VARCHAR(MAX) 类型将尝试将数据直接存储在行中，除非它超过8k 限制，然后将其存储在 blob 中.

The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

使用 LIKE 语句在两种数据类型之间是相同的.VARCHAR(MAX) 为您提供的附加功能是它也可以像任何其他 VARCHAR 一样与 = 和 GROUP BY 一起使用 列即可.但是，如果您确实有大量数据，则使用这些方法会遇到巨大的性能问题.

Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

关于是否应该使用 LIKE 进行搜索，或者是否应该使用 Full Text Indexing 和 CONTAINS.不管VARCHAR(MAX)还是TEXT，这个问题都是一样的.

In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

如果您要搜索大量文本并且性能是关键，那么您应该使用全文索引.

If you are searching large amounts of text and performance is key then you should use a Full Text Index.

LIKE 实现更简单，通常适用于少量数据，但由于无法使用索引，在处理大数据时性能极差.

LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

相关文章