In SQL Server 2008 R2 I have a query which looks like

create stored procedure give_me_art @filter varchar(15),@skl_id int
  id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art 
left outer join lager on art.id=lager.art_id and lager.skl_id=@skl_id
where sifra like '%'+@filter+'%' or naziv like '%'+@filter+'

I have an index on column naziv and column sifra.

I am considering changing this query to full text search. Table art has around 150K records and my main goal is to get faster response from SQL Server, because this query is common during daily work.

If I make a full text index on these two columns and redesign my query to use the full text index, what will happen with performance of this query?

  • 3
    Your LIKE queries with a leading wildcard are never going to use an index, so for all intents and purposes, you may as well pretend they don't exist. Feb 19, 2013 at 23:10
  • @AaronBertrand is there any other option to use any part of string inside search, beside using full text ?
    – adopilot
    Feb 20, 2013 at 12:01
  • none that I know of. Why are you afraid of full-text search? Just turn it on and try it. Feb 20, 2013 at 13:36
  • @AaronBertrand Perhaps I am afraid because I do not have enough knowledge about it. Ill try anyway thank you on nice comments.
    – adopilot
    Feb 20, 2013 at 13:47

1 Answer 1


In large part it's going to depend on if @filter is a word or a group of words. Full text indexes essentially break down the contents of the column into the individual words and let you search for a word (group of words) or a synonym etc. 150k rows really isn't all that much to be searching on as these things go, but you may very well see some performance increase, again depending on what @filter is. I would also double check the rest of your indexes. For example one on art.id and one on lager.art_id and lager.skl_id. Also given how few columns you are returning (assuming that is the case in the real query not just this example) you might consider making them covering indexes by "including" (look at the key word INCLUDE in CREATE INDEX) the extra fields in your indexes. That lets the query just look at the index and not have to go back to the original table.

  • @filter in most time will be part of name (naziv) or part of catalog number (sifra). User knows only part of catalog number or part of name of an article. And then searching. Does that mean that it is no propose of setting full text indexes
    – adopilot
    Feb 20, 2013 at 12:02
  • That's my understanding of it. If I were you I would concentrate on performance on the rest of the query and minimizing the number of rows you are going to be checking with the likes. Feb 20, 2013 at 12:59
  • 1
    If the part of the name or catalog number that is known can be deterministic in some way, you might consider storing that separately and searching on that instead of relying on partial match. For example if the name of a product is "big yellow boat" you could store big, yellow, and boat separately (think of them as "tags") and search those (using an index) instead of LIKE, giving priority to ones where more than one word matched. Feb 20, 2013 at 13:39
  • Also you can dramatically improve the performance of LIKE statements if you can get rid of the preceding wildcard. So field LIKE 'test%' is MUCH faster than field LIKE '%test%'. Feb 20, 2013 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.