Search code examples
sql-serversql-server-2008sql-server-2005full-text-searchclustered-index

Full-text indexing vs %% and clustered index

I have two questions.

  1. Does it make sense when I have to choice of a clustered index on an Integer type column instead of choosing it over string values type column.

Why?

The clustered index will cause a reordering of the page data whenever an INSERT is processed because the new row is not appended to the end of the index but inserted in the middle

I am right? Any other reason for Choosing the clustered Index for Integer type column? Or I am moving in wrong direction?

  1. When I have to search a string using %...% like below query

    Select Column1 From Table1 Where Column1 Like '%SomeVal%'

I have following questions for this.

  1. Does it make sense to use %....% in the above situation?
  2. Can I put the clustered index for Column1 and then go for %....%
  3. Should I choose full-text indexing? If so why I should prefer full-text indexing over %...% ?
Solution

    1. Yes, SQL Server (and computers in general) are faster at looking through lists of numbers than they are through lists of text. That means that it's generally faster to search for a record WHERE ID = 3 than WHERE FirstName = 'BOB'. That works great if you're system is geared up to track one or the other, like when the user clicks on a product you can choose to store the ProductID or the ProductName, the ProductID will pull faster. However, if the only column that's going to be in that WHERE clause is a text filed, like you're always looking for a movie by its title, then you're probably better off putting your clustered index on that column. As a general rule I have an integer ID field with the clustered index and then put a nonclustered index on the other field, but it really depends on the situation.

    2. FTS is word-based while wildcards (%) are pattern-based, so if you need to search a text field for "Patricia", FTS would probably be your best choice, but if you needed to search for "Pat%" to look for "Patrick" or "Pattie" or "Patricia", FTS will not work well. I hope that makes sense. Search StackOverflow or google for "fts vs like" and you'll get a few discussions on the matter.