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

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

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

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

    WHERE COL1 LIKE '%search string%' < / p >

  2. 使用TEXT列并在该列上放置全文索引/目录，然后使用CONTAINS子句进行搜索?

    WHERE CONTAINS (Col1, 'MyToken') < / p >

229872 次浏览
小开

对于大型文本，全文索引比多快。但是你也可以全文索引 __abc0。

小开

如果不将文本转换为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在equal to运算符中不兼容。

然而这并不是:

DECLARE @table TABLE (a varchar(max))

有趣的是，LIKE仍然有效，即。

WHERE a LIKE '%a%'
小开
最佳答案

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

在这两种数据类型之间使用LIKE语句是相同的。VARCHAR(MAX)提供给你的额外功能是，它也可以与=和GROUP BY一起使用，就像任何其他VARCHAR列一样。然而，如果你有大量的数据，使用这些方法就会有巨大的性能问题。

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

如果你正在搜索大量的文本，性能是关键，那么你应该使用全文索引。

LIKE实现起来更简单，通常适用于少量的数据，但由于它不能使用索引，所以在处理大数据时性能非常差。

小开

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

小开
  • 基本定义

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

  • 用哪一个?

根据MSDN，微软建议避免使用TEXT数据类型，它将在SQL Server的未来版本中被删除。VarChar(MAX)是存储大字符串值的建议数据类型，而不是TEXT数据类型。

  • 行内或行外存储

TEXT类型列的数据存储在单独的LOB数据页的行外。表数据页中的行只有一个指向LOB数据页的16字节指针，LOB数据页是实际数据所在。VarChar(MAX)类型列的数据如果小于或等于8000字节，则存储在行中。如果VarChar(MAX)列的值大于8000字节，则VarChar(MAX)列的值将存储在单独的LOB数据页中，并且row将只有一个指向实际数据所在的LOB数据页的16字节指针。所以“in-row"VarChar(MAX)适用于搜索和检索。

  • 支持/不支持的功能

一些字符串函数、操作符和构造不能在TEXT类型的列上工作，但它们可以在VarChar(MAX)类型的列上工作。

  1. =等于VarChar(MAX)类型列上的运算符
  2. VarChar(MAX)类型列上的GROUP BY子句
  • 系统IO注意事项

如我们所知，VarChar(MAX)类型的列值只有在值的长度大于8000字节或行中没有足够的空间时才会存储在行外，否则它将存储在行内。因此，如果存储在VarChar(MAX)列中的大多数值都很大，并且存储在行外，则数据检索行为将几乎类似于TEXT类型的列。

如果存储在VarChar(MAX)类型列中的大多数值都足够小，可以存储在行中，那么检索不包括LOB列的数据需要读取更多的数据页，因为LOB列的值存储在存储非LOB列值的同一数据页中的行中。但是如果SELECT查询包含一个LOB列，那么与TEXT类型列相比，它需要更少的页来读取数据检索。

结论

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

Source .


提交答案