I have been asked to improve the searching capabilities of a LIKE '%SOMETEXT%' predicate on an NVARCHAR(MAX) field. I am aware of full text indexes but am not sure if they are the best strategy. A traditional rowstore index obviously wouldn't do the job given that NVARCHAR(MAX) isn't an acceptable index key value and also I'd be searching using (LIKE '%SOME TEXT%') so I wouldn't be able to write a sargable statement.
Is a full text index the best option for this?
Cache file /home/queryxchang/public_html/apps/frontend/config/../cache/-q-7-204502-optimize-wildcard-searching-in-a-nvarchar-max-field- could not be written