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

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

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

  • VARCHAR(MAX) 列使用 LIKE 子句?

WHERE COL1 LIKE '%search string%'

  • 使用 TEXT 列并在此列上放置全文索引/目录,然后使用 CONTAINS 子句进行搜索?

WHERE CONTAINS (Col1, 'MyToken')

回答(5)

3 years ago

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

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

关于是否应使用 LIKE 进行搜索,或者是否应使用全文索引和 CONTAINS . 无论 VARCHAR(MAX)TEXT 如何,这个问题都是一样的 .

如果您正在搜索大量文本并且性能很关键,那么您应该使用全文索引 .

LIKE 更易于实现,并且通常适用于少量数据,但由于无法使用索引,因此大数据的性能极差 .

3 years ago

对于大文本,全文索引要快得多 . 但你也可以全文索引 varchar(max) .

3 years ago

如果不将文本字段从文本转换为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'

这给出了一个错误:

The data types text and varchar are incompatible in the equal to operator.

这不是:

declare @table table (a varchar(max))

有趣的是, LIKE 仍然有效,即

where a like '%a%'

3 years ago

  • Basic Definition

TEXTVarChar(MAX) 是非Unicode大型可变长度字符数据类型,最多可存储2147483647个非Unicode字符(即最大存储容量为:2GB) .

  • Which one to Use?

根据MSDN link,Microsoft建议避免使用Text数据类型,它将在未来版本的Sql Server中删除 . Varchar(Max)是用于存储大字符串值而不是Text数据类型的建议数据类型 .

  • In-Row or Out-of-Row Storage

Text 类型列的数据存储在单独的LOB数据页中的行外 . 表数据页中的行只有一个16字节指针指向存在实际数据的LOB数据页 . 而 Varchar(max) 类型列的数据如果小于或等于8000字节则存储在行内 . 如果Varchar(max)列值超过8000字节,则Varchar(max)列值存储在单独的LOB数据页中,并且行将只有一个16字节指针指向存在实际数据的LOB数据页 . 所以 In-Row Varchar(Max)适合搜索和检索 .

  • Supported/Unsupported Functionalities

一些字符串函数,运算符或在Text类型列上不起作用的构造,但它们可以在VarChar(Max)类型列上工作 .

  • = 等于VarChar(Max)类型列上的运算符

  • 关于VarChar(Max)类型列的分组子句

  • System IO Considerations

我们知道只有当存储在其中的值的长度大于8000字节或者行中没有足够的空间时,VarChar(Max)类型的列值才会存储在行外,否则它将存储它在行中 . 因此,如果存储在VarChar(Max)列中的大多数值都很大并且存储在行外,则数据检索行为几乎与Text type列的行为相似 .

但是,如果存储在VarChar(Max)类型列中的大多数值都足够小,可以存储在行中 . 然后,检索不包括LOB列的数据需要读取更多数据页,因为LOB列值存储在存储非LOB列值的同一数据页中的行内 . 但是,如果select查询包含LOB列,那么与Text类型列相比,它需要更少的页面来读取数据 .

Conclusion

使用 VarChar(MAX) 数据类型而不是 TEXT 以获得良好性能 .

Source

3 years ago

如果使用MS Access(特别是像2003这样的旧版本),则必须在SQL Server上使用 TEXT 数据类型,因为MS Access无法将 nvarchar(MAX) 识别为Access中的备注字段,而 TEXT 则被识别为备注字段 .