我有一个查询,使用通配符“喜欢”搜索客户端.例如：
SELECT TOP (10)
[t0].[CLIENTNUMBER],[t0].[FIRSTNAME],[t0].[LASTNAME],[t0].[MI],[t0].[MDOCNUMBER]
FROM [dbo].[CLIENT] AS [t0]
WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0')
AND ([t0].[FIRSTNAME] LIKE '%John%')
AND ([t0].[LASTNAME] LIKE '%Smith%')
AND ([t0].[SSN] LIKE '%123%')
AND ([t0].[CLIENTNUMBER] LIKE '%123%')
AND ([t0].[MDOCNUMBER] LIKE '%123%')
AND ([t0].[CLIENTINDICATOR] = 'ON')
它还可以在“where”子句中使用较少的参数,例如：
SELECT TOP (10)
[t0].[CLIENTNUMBER],[t0].[MDOCNUMBER]
FROM [dbo].[CLIENT] AS [t0]
WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0')
AND ([t0].[FIRSTNAME] LIKE '%John%')
AND ([t0].[CLIENTINDICATOR] = 'ON')
解决方法
要为模式具有“％XXX％”形式的LIKE做很多事情,您需要查找sql Server的全文索引功能,并使用CONTAINS而不是LIKE.原样,你正在进行全表扫描,因为普通索引无法帮助搜索以通配符开头的项目 – 但是全文索引会.
/* ... */ WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND (contains([t0].[FIRSTNAME],'John')) AND (contains([t0].[LASTNAME],'Smith')) AND (contains([t0].[SSN],'123')) AND (contains([t0].[CLIENTNUMBER],'123')) AND (contains([t0].[MDOCNUMBER],'123')) AND ([t0].[CLIENTINDICATOR] = 'ON')
