Search code examples
sql-serverfulltext-index

SQL Server: Normal Index vs. Fulltext Index

what exactly is the difference (and advantages/disadvantages) between a fulltext index and a regular index on a varchar column? When would I use which index?

I have a multitude of varchar columns (addresses - city name, street name etc.) which I need to be searchable in the most performant way and I'm trying to figure out which index type to use and why.

Thank you!

Solution

  • It depends on the kind of search you want to do. For example, you can't use a normal index with this query:

    SELECT * FROM [MyTable] WHERE [MyColumn] LIKE '%' + @SearchText + '%'

    It's not sargable. This is sargable, but the selectivity might not be very good:

    SELECT * FROM [MyTable] WHERE [MyColumn] LIKE @SearchText + '%'

    You use a full-text index completely differently: 

    SELECT * FROM [MyTable] WHERE CONTAINS([MyColumn], @SearchText)