SQL Server Index on VarChar(50) - Is this fulltext? Or what?

Question

What exactly happens when I create an index in my SQL Server 2008 DB on a VarChar column. This is obviously a text column.

Will it be automatically fulltext indexed or does it create a different kind of index?

Answer 1

no you have to create a fulltext index column for that type of searching. it is not created automatically with a normal index.

Answer 2

Here's a good article I found on the basics of SQL Server indexing:
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Answer 3

Full-text indexes are different from normal indexes. Consider these two seemingly equivalent where clauses:

WHERE [mycolumn] LIKE '%' + @MyValue + '%'

WHERE CONTAINS([mycolumn], @MyValue)

The former will not use any index, even a normal one of the sort you refer to in your question. It is not considered sargable. The latter specifically tells the server to use a full-text index.

On the other hand, this will work with a normal index, but ignores any available full-text index:

WHERE [mycolumn] LIKE @MyValue + '%'

Normal indexes work because they are ordered. When you go to lookup a value in the index, you can use a binary search, b*-tree, or similar algorithm to more-quickly find or reject your match, and when comparing for a join or range the server can 'walk' the index to efficiently check a number of values.

Full-text indexes cannot just be ordered, because you have to be able to search in the middle of the content as quickly as in the beginning. Thus they are much more complicated.