SQL Server: Normal Index vs. Fulltext Index

sql-server fulltext-index
Alex picture Alex · Jul 23, 2009 · Viewed 11.3k times · Source

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!

Answer

Joel Coehoorn picture Joel Coehoorn · Jul 23, 2009

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)

Related questions

How can I know when SQL Full Text Index Population is finished?

We are writing unit tests for our ASP.NET application that run against a test SQL Server database. That is, the ClassInitialize method creates a new database with test data, and the ClassCleanup deletes the database. We do this by …

Read More
Full text search vs LIKE

My question is about using fulltext.As I know like queries which begin with % never use index : SELECT * from customer where name like %username% If I use fulltext for this query can ı take better performance? Can SQL Server use fulltext …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More