Are there differences in performance of getting data between FullText Search and indexed columns In SQL Server 2008 R2 I have a query which looks like

create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select 
  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+'
end


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?
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.