Yes, your operation is correct. Full-text breaks the text into words and index those words. Which means that finding something that start with these words is quick. But it does not help you find fragment in words.
If you want to find fragment in words and you find LIKE to be a tad slow, there is a way to speed it up: cast to a binary collation
SELECT * FROM tbl WHERE col COLLATE Latin1_General_BIN2 LIKE '%ABC%'
Which gain you make depends on your current collation and data type. Or more precisely, you will make a gain of a factor 7-10 if you have nvarchar
or a Windows collation. If you have an SQL collation and varchar
, your gain will be quite modest.
If you want to it go as about as fast as full-text can give you, this is possible, but it requires heavy artillery. I have actually written about this, but in difference to about everything else I've written, this is not available on my web site, but in a book, where the royalities go to WarChild International. You can find a teaser here: https://www.sommarskog.se/yourownindex.html.