0

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 %...% ?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • 5
    prefix with '%' will do an index scan at best. Your questions don't really make sense. Rather than tell us your perceived solution, tell us the actual problem you are trying to solve. – Mitch Wheat Jun 19 '12 at 03:52
  • @mitchwheat - sir, my query is based upon contains predicate. As mentioned in the query. – Pankaj Jun 19 '12 at 03:54
  • I typically cluster my indexes on the most-utilized foreign key in the table, that way lookups/joins via that FK are in the same page on disk (should be faster). Also, %...% are fairly expensive, depending on how many records you anticipate. I'd avoid these whenever possible, but FTS is another can of worms. As mentioned above, more details would help. – Tom Jun 19 '12 at 06:55

1 Answers1

4

  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.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28