sql server full text search:
Hi Guys
SQL Server 2000 SP3
(WIN NT/2000/XP)
Can somebody please explain how I can overcome the
following (what looks to be) limitation in SQL Server's
FullText capabilities?
Consider the following query (table defs at bottom of
post):
select *
from a
left outer join b on b.b_id = a.b_id
Which returns:
a_id b_id b_id foo
---- ---- ---- ---
1 NULL NULL NULL
2 1 1 bar
Now, the column foo in table b is fulltext indexed.
So, the following query returns the following, correct
results:
select *
from a
left outer join b on b.b_id = a.b_id
where contains(b.*, '"bar"')
a_id b_id b_id foo
2 1 1 bar
However, if I want to return all of the rows that do not
contain the word "bar", I would execute the following:
select *
from a
left outer join b on b.b_id = a.b_id
where not contains(b.*, '"bar"')
But, this query does not return *any* rows. I was
expecting to see:
a_id b_id b_id foo
---- ---- ---- ---
1 NULL NULL NULL
It is as if my query has become an INNER JOIN, i.e. it
yields the same results as:
select *
from a
inner join b on b.b_id = a.b_id
where not contains(b.*, '"bar"')
....which I would expect.
Oddly, SET ANSI_NULLS OFF fixes the problem - but I
cannot see why this relates to CONTAINS searching and
besides. SET ANSI_NULLS OFF is definitely *not* an option
for me.
My questions are:
1. Is this the expected behaviour?
2. Is this a common problem?
3. What can I do to "fix" the problem?
Incidentally, executing the same NOT CONTAINS query in
SQL Server 2005 BETA2 does *not* exhibit the problem,
i.e. it works as we would like it to.
Many Thanks in advance.
create table a(a_id int not null constraint pk_a primary
key, b_id int)
insert into a values(1, null)
insert into a values(2, 1)
create table b(b_id int not null constraint pk_b primary
key, foo varchar(100))
insert into b values(1, 'bar')
sp_fulltext_catalog 'test', 'create'
sp_fulltext_table 'b', 'create', 'test', 'pk_b'
sp_fulltext_column 'b', 'foo', 'add'
sp_fulltext_table 'b', 'activate'
