2005 Inconsistent Results with VarBinary fields

Hello,
I seem to be encountering some inconsistent results with full text searching
against a combination of VarChar(max) and VarBinary(max) fields.
I have a table which has both a VarChar(max) field and a VarBinary(max)
field. The table is meant to either have text or an attachement (or both).
I have indexed both fields. When I do a particular search against just the
Varbinary field I get 16 results. When I do the same query, but with both
columns specified, I only get 8 results. Maybe there is a good reason for
this, but it seems like adding more fields to the search should not result
in fewer hits. And I have verified that the records it is missing truly
contain the word I am looking for. Below are the relevant queries.
Any help would be appreciated. Thanks!
Randall
-- ***** Table ****
create table dbo.z_FullTextChild (
FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
FullTextMainID uniqueidentifier not null,
LongText VarChar(max) null,
AttachmentFileName VarChar(256) null,
AttachmentFileType VarChar(15) null,
Attachment VarBinary(max),
constraint PK_FullTextChild primary key (FullTextChildID),
constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
cascade
)
go
-- ******** Full Text Index *********
create fulltext index on dbo.z_FullTextChild
(LongText, Attachment TYPE COLUMN AttachmentFileType )
key index PK_FullTextChild
on ftTracker
with CHANGE_TRACKING AUTO
go
-- ******* Query that yields 16 results ***************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******** Query that yields 8 results (only difference is in selected
columns) ******************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment, LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******* Select @.@.Version ***********
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
I have tried to repro this problem and am unable to do so. Can you perhaps
do this query?
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randall" <randall@.randall.com> wrote in message
news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I seem to be encountering some inconsistent results with full text
searching
> against a combination of VarChar(max) and VarBinary(max) fields.
> I have a table which has both a VarChar(max) field and a VarBinary(max)
> field. The table is meant to either have text or an attachement (or
both).
> I have indexed both fields. When I do a particular search against just
the
> Varbinary field I get 16 results. When I do the same query, but with both
> columns specified, I only get 8 results. Maybe there is a good reason for
> this, but it seems like adding more fields to the search should not result
> in fewer hits. And I have verified that the records it is missing truly
> contain the word I am looking for. Below are the relevant queries.
> Any help would be appreciated. Thanks!
> Randall
> -- ***** Table ****
> create table dbo.z_FullTextChild (
> FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
> FullTextMainID uniqueidentifier not null,
> LongText VarChar(max) null,
> AttachmentFileName VarChar(256) null,
> AttachmentFileType VarChar(15) null,
> Attachment VarBinary(max),
> constraint PK_FullTextChild primary key (FullTextChildID),
> constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
> references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
> cascade
> )
> go
> -- ******** Full Text Index *********
> create fulltext index on dbo.z_FullTextChild
> (LongText, Attachment TYPE COLUMN AttachmentFileType )
> key index PK_FullTextChild
> on ftTracker
> with CHANGE_TRACKING AUTO
> go
> -- ******* Query that yields 16 results ***************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
> on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******** Query that yields 8 results (only difference is in selected
> columns) ******************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment, LongText),
'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******* Select @.@.Version ***********
> Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005
18:22:46
> Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>
>
|||Thanks for the reply.
That particular query yields no hits because currently none of the records
that have attachments have any LongText associated with them (although that
could change in the future).
Randall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4eEKfuhFHA.1048@.tk2msftngp13.phx.gbl...
>I have tried to repro this problem and am unable to do so. Can you perhaps
> do this query?
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randall" <randall@.randall.com> wrote in message
> news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> searching
> both).
> the
> 'INFORMATION',
> 18:22:46
>