sql-server sql-server-2008-r2 add tag
racer sql (imported from SE)
[In this question][1] he's having the same problem as I am. I need something like:

    select * from blablabla 
    where product 
    like '%rock%' or
    like '%paper%' or
    like '%scisor%' or
    like '%car%' or
    like '%pasta%' 

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

[This question talks about Full Text as well][2]


  [1]: https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server
  [2]: https://stackoverflow.com/questions/6506418/sql-searching-multiple-words-in-a-string
Top Answer
meme (imported from SE)
Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns. 

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

    use tempdb
    
    CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))
    
    INSERT #fulltextindexesarestupid (Id)
    SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
    FROM sys.messages AS m
    CROSS JOIN sys.messages AS m2
    
    UPDATE #fulltextindexesarestupid
    SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
    							   WHEN Id % 3 = 0 THEN 'Idea'
    							   WHEN Id % 5 = 0 THEN 'Jeans'
    							   END
    
    
    ALTER TABLE #fulltextindexesarestupid 
    ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
    					WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
    					ELSE 0 END)
        
    CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)
    
    SELECT COUNT(*)
    FROM #fulltextindexesarestupid AS f
    WHERE LessBad = 1

[![NUTS][1]][1]


  [1]: https://i.stack.imgur.com/BJwhp.jpg

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.