Full Text Search

• Alan Burstein

  SSC Guru

  Points: 61152

  More actions

  May 5, 2016 at 8:18 pm

  #1877009

  There's the old joke, "guy goes to the doctor and says, 'Doc, it hurts when I do this...' so the doctor says, 'don't do that'."

  I digress, can you post the execution plan for both queries?

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• pkamal-841727

  Valued Member

  Points: 65

  More actions

  May 26, 2016 at 8:23 am

  #1881571

  The execution plan is same for all searches.

  I created a table with one row.

  create table dbo.Addr

  (

  AddressID int identity primary key,

  AddressLine varchar(100)

  )

  insert into dbo.Addr values ('123 W Main St APT 666')

  Fulltext index enabled on table. Then looking for fulltext index keywords.

  SELECT display_term, column_id, document_count

  FROM sys.dm_fts_index_keywords(DB_ID('FTDB'), OBJECT_ID('Addr'))

  The data looks like below. (Note: W is a stopword. so there is no keyword for W)

  display_termcolumn_iddocument_count

  12321

  66621

  apt21

  main21

  nn12321

  nn66621

  st21

  END OF FILE21

  surprisingly when i search for n*, i am getting the address which do not have n*.

  select a.*

  from Addr a

  join (

  SELECT a.[Key] AS AddressID

  FROM ContainsTable([Addr], (AddressLine), '"n*"') a

  ) z on a.AddressID = z.AddressID

  GO

  why these nn rows are created internally ?