Its difficult to find info about FTS in SSC.

I was just about to create a net topic but i think this old ill do.

I just make a example:

-- 0. create dummy table

CREATE TABLE dummy_table

(

id int identity constraint PK_id primary key

,name varchar(100)

,surname varchar(100)

)

-- 0.1 populate dummy table

insert into dummy_table values ('AAs','11s')

insert into dummy_table values ('AAs','22s')

insert into dummy_table values ('BBs','11s')

insert into dummy_table values ('BBs','22s')

insert into dummy_table values ('AAs BBs','11s 22s')

-- 1. create catalog

CREATE FULLTEXT CATALOG ctg_TESTE WITH ACCENT_SENSITIVITY = OFF

GO

-- 2. create index

CREATE FULLTEXT INDEX ON dummy_table

(

name,

surname

)

KEY INDEX PK_id

ON ctg_TESTE

WITH STOPLIST = SYSTEM, CHANGE_TRACKING OFF, NO POPULATION;

GO

-- 3. populate index

ALTER FULLTEXT INDEX ON dummy_table START FULL POPULATION;

GO

-- first issue, using * to search in both columns causes the predicate to mach for each column, no for row

select * from dbo.dummy_table

where contains(*,'AAs and 11s')

select * from dbo.dummy_table

where contains(*,'AAs') and contains(*,'11s')

---------------------------------------------

-- second issue, same thing using the wild card

select * from dbo.dummy_table

where contains(*,'"AA BB*"')

select * from dbo.dummy_table

where contains(*,'"AA 11*"')

---------------------------------------------

So, I ill really need to concatenate the varchar columns to make it owrk properly?

There are others wild cards to use with FTS?