t-sqlsql-server-2008full-text-catalog

Need to search for names, but have no Freetext index

I have a need to search names in our table, but we don't have a FREETEXT index setup. This isn't an option anymore due to the large amount of data in the table.

Is there any alternatives that I can do? Essentially I'd be looking at doing:

SELECT *
  FROM MyTable
 WHERE FREETEXT(FirstName, @firstname)

Any ideas or pointers would be greatly appreciated.

Solution

  • Without full text indexes, then you'd maybe consider using

    WHERE FirstName LIKE '%' + @firstname+ '%'

    However it doesn't scale well (linearly, scan of all rows).

    And you have a "large amount of data" which means you really should have full text indexes