CODEWITHSUNDEEP
sqlsql-server-2008
user989056
user989056

Reputation: 1274

Performance with LIKE vs CONTAINS using full-text indexing

I have a table with a large(ish) amount of rows 500k, MSSQL Server 2008. I have a column which holds a nvarchar product ID which is usually 15 characters in length, alphabetical and numerical e.g. FF93F348HJKCF5HW9 . I would like to be able to search for this product ID with the best performance. I have done some research into using Full-Text indexing on this column and I dont really think that using full-text indexing using CONTAINS offers any benefit over using LIKE '%%'. This looks to be down to the fact Full-text indexing is more beneficial when searching for whole words, rather than a series of characters.

Can somebody confirm/deny this for me?

Upvotes: 0

Views: 2188

Answers (1)

N West
N West

Reputation: 6819

Full-Text indexing is about searching for language words in unstructured text data. Your data doesn't contain words, just a sequence of characters.

I haven't tested this, but I would expect that LIKE would actually be faster, as long as your data is indexed. CONTAINS is meant for searching for words & word-like structures.

If your requirement is for "auto-complete", then LIKE will perform pretty well since the optimizer will use an INDEX SEEK when you search for something such as LIKE 'F5521%'.

This MSDN article explains the basics of the CONTAINS keyword.

Upvotes: 1

Related Questions