Performace gains of searching with FTS over it with LIKE on indexed colum(s)?

开发者_JS百科Subquestioning "full-text search sql server 2005"

Would the use of FTS (Full Text Search) be faster in comparison with searches using tsql LIKE on indexed column(s)?

Why?

And how much?

---

yes FTS will be faster for searches in the middle of the column, like will only be faster if what you searching is at the start of the column

where lastname like 'S%'

will be fast

where lastname like '%S%'

will be slow and the index won't be used because it has to traverse the whole column

---

Depending on your application requirements, the first thing to keep in mind is that FTS is word based, while LIKE is matching character patterns. So, for example LIKE will find '%rate%' in 'overrated' while an FTS search on 'rate' will not.

Beyond that, SQLMenace's answer covers the topic nicely.

---

I certainly prefer seeing links to any docs...

"CONTAINS (Transact-SQL). SQL Server 2008 R2 Books Online" has paragraph:

• "Comparison of LIKE to Full-Text Search

  *In contrast to full-text search, the LIKETransact-SQL predicate works on character patterns only.
  Also, you cannot use the LIKE predicate to query formatted binary data.

  Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data.

  A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned*"