0

I wish to avoid a fullscan for a query using the LIKE keyword:

SELECT * 
FROM MYTABLE 
WHERE MYCOLUMN LIKE '%WORD%'` AND PRICE < 3;

With SQL Server, we can define a fulltext index and use it with the CONTAINS clause (and other ones). The problem: I'm not allowed to change my query, so I want the server use the fulltext index with the LIKEkeyword.

Is it possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37
  • 2
    You can't. You either need to use `CONTAINS` (with a Full Text Index) or drop the leading Wild Card; which won't provide the same result. Using `LIKE` with a leading Wild Card makes a query non-SARGable. I suspect that this is an XY Problem; why can't you make use on `CONTAINS`? You say you can't change your query; why? – Thom A Jan 04 '19 at 16:14
  • Because the query is automatically generated by a software and I cannot change the code of this software. – Olivier Faucheux Jan 10 '19 at 14:11

1 Answers1

-1

You could increase the performance by using the folowing trick:

WHERE MYCOLUMN LIKE 'WORD%' 
AND MYCOLUMN LIKE '%WORD'
AND PRICE < 3;
Mustafa Erdogan
  • 103
  • 7