有没有一种有效的方法来索引SQL Server 2008中的单词？ [英] Is there an efficient way to index inside words in SQL server 2008?

问题描述

我们的系统中有一些查询使用LIKE'％'+ @ SomeCriteria +'％'来搜索某个人的姓名。在这种情况下，我们正在讨论VARCHAR（50）字段。我们真的希望允许我们的用户在名称中进行搜索。

We have a a few queries in our system that use LIKE '%'+@SomeCriteria+'%' to search a for a person's name. We are talking VARCHAR(50) fields in this case. We would really like to allow our users the ability to search within names.

我理解它的方式，索引字段只会使我们搜索第一个更快名称的一部分，全文索引将搜索特定的单词，但据我所知，在单词内搜索时无效。

The way I understand it, indexing the field will only make this faster if we search for the first part of the name, and full text indexing will search for specific words, but as far as I can tell doesn't help when searching within words.

有没有好处在SQL 2008中有效地搜索给定条件的单词内部而不进行完整的表扫描？我注定要提出一个复杂的自定义方法，或者查询速度一直很慢吗？

Is there a good way in SQL 2008 to efficiently search inside of words for a given criteria without doing a complete table scan? Am I doomed to come up with a complex custom approach or have a consistently slow query?

推荐答案

我们做过一次有什么帮助由于查询缓慢，我们有一个存储过程，它运行两段代码而不是一段。首先，我们进行了完全匹配搜索，然后只进行了不精确的搜索，如果确切的一个没有返回结果（或者在一种情况下，如果完全匹配搜索返回少于5个结果）。这样，愿意输入全名的用户得到了快速响应，但那些只想输入几个字符的用户得到的速度较慢。你可以做到三种可能性，精确匹配，仅在最后的不精确匹配和前后不精确的匹配。

What we did one time that helped with the slowness of the query was we had a stored proc that ran two pieces of code instead of one. First we did an exact match search and then only went to the inexact search if the exact one didn't return results (or in one case if the exact match search returned less than 5 results). That way, users who were willing to type the whole name got a fast response but those who only wanted to type a few characters got a slower one. You could do it with three possibilities as well, exact match, inexact match only on the end and inexact match at front and end.

这篇关于有没有一种有效的方法来索引SQL Server 2008中的单词？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！