我刚刚读到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')
我刚刚读到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')
如果不将文本转换为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实现起来更简单,通常适用于少量数据,但由于不能使用索引,它在处理大数据时的性能非常差。
如果使用MS Access(特别是像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数据类型。
行内或行外存储
Data of a TEXT type column is stored out-of-row in a separate LOB data pages. The row in the table data page will only have a 16 byte pointer to the LOB data page where the actual data is present. The data of a VarChar(MAX) type column is stored in-row if it is less than or equal to 8000 bytes. If the value of a VarChar(MAX) column is greater than 8000 bytes, then the VarChar(MAX) column value is stored in a separate LOB data pages and row will only have a 16 byte pointer to the LOB data page where the actual data is present. So "in-row" VarChar(MAX) is good for searches and retrieval.
支持/不支持的功能
一些字符串函数、操作符和构造在TEXT类型列上不起作用,但它们在VarChar(MAX)类型列上起作用。
=等于VarChar(MAX)类型列上的运算符 VarChar(MAX)类型列上的GROUP BY子句
系统IO注意事项
我们知道,VarChar(MAX)类型的列值只有在值的长度大于8000字节或行中没有足够的空间时才会在行外存储,否则将在行内存储。因此,如果VarChar(MAX)列中存储的大多数值都很大,并且存储在行外,则数据检索行为几乎类似于TEXT类型列。
如果存储在VarChar(MAX)类型列中的大多数值都足够小,可以存储在行中,那么检索不包括LOB列的数据需要读取更多的数据页,因为LOB列值存储在存储非LOB列值的同一数据页中的行中。但是,如果SELECT查询包含LOB列,那么与TEXT类型列相比,它在数据检索中需要读取的页面更少。
结论
为了获得更好的性能,使用VarChar(MAX)数据类型而不是TEXT。
源