CODEWITHSUNDEEP
sql-serverindexingfull-text-search
ren
ren

Reputation: 3993

SQL Server 2008: should I create full text index on 'short phrase' column

I have around 100 000 records and a column which contains 2-3 words phrases. I would like to search on this column using like functionality or similarly. The question is: should I build a full text index on this column? Another question: would building a simple non-clustered index on this column improve like's speed? And finally: how much space would such full-text index take?

Upvotes: 0

Views: 279

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Should I build a full text index on this column?

For 100,000 rows I am not sure that you will benefit that much from it. You are properly in the area where it would be more conclusive to test it.

would building a simple non-clustered index on this column improve like's speed?

LIKE 'foo%'? Yes. LIKE '%foo%'? No. Think of a phone book, which is indexed by "Lastname, Firstname". Very easy to find all the Smiths, not very easy to find all the Johns.

how much space would such full-text index take?

Far too speculative. You're going to have a hard time getting an authoritative answer to this. Again I suggest you need to test.

Upvotes: 1

Related Questions