％searchKeyword％上的SQL Server FullText [英] SQL Server FullText on %searchKeyword%

问题描述

我在想，如果SQL Server实际上在文本中间的搜索中使用了全文索引（比如LIKE％searchKeyword％）？

我忘了我在哪里读过它，但我确实在某处读到全文索引只会在以StartsWith方式（searchKeyword％）使用时才被使用，您可以在其中搜索具有固定开头和变量结尾的LIKE。如果这是真的，请让我知道吗？

（ CONTAINS（）和 FREETEXT（））来访问索引（如果您拥有它们，它们并未默认启用），而不是 LIKE strong>运算符。

$ b LIKE 运算符可以使用正常索引（例如，CREATE INDEX ix_tbl_fld ON mytable（textfield））但是当模式以％通配符开始时，这些搜索需要扫描表中的所有值。

如果您有使用SQL Server 2008下的表压缩可以提高 LIKE'％keyword％'搜索的性能，即使没有索引，我也可以重复使用很多具有相同值的行没有，所以我不能测试这个理论......）。

I was wondering if SQL Server is actually utilizing its fulltext index on searches that can be in the middle of text (such as LIKE %searchKeyword%)?

I forgot where I read it, but I did read somewhere that the fulltext index would only be utilized when it is used in a "StartsWith" manner (searchKeyword%) where you search for a LIKE with a fixed beginning and a variable ending.

Please let me know if this is true?

解决方案

Full-text indexes (if you have them, they aren't enabled by default) are accessed using functions like CONTAINS() and FREETEXT(), not the LIKE operator.

Normal indexes (e.g., "CREATE INDEX ix_tbl_fld ON mytable(textfield)") can be used by the LIKE operator, but not when the pattern begins with a % wildcard... these searches require a scan of all of the values in the table.

If you have a lot of rows with the same value repeated, using table compression under SQL Server 2008 may improve performance of LIKE '%keyword%' searches, even without an index (I don't have it, so I can't test this theory...).

这篇关于％searchKeyword％上的SQL Server FullText的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！