I have a full-text index created in a varchar (max) type column with the filter enabled. Filtering contains data such as JPG, TIF, PDF, and XML (although this, in my opinion, is mostly irrelevant to this issue).
I have two queries that I created that allow me to search for an index.
Full Text Search # 1 -
select parentObj.ObjectID as 'GroupingID', parentObj.Name as 'Grouping', childObj.ObjectID as 'FileObjID', childObj.Name as 'FileName', fs.FileStreamID from dbo.dat_FileStream fs inner join dbo.dat_Object childObj on fs.ObjectID = childObj.ObjectID inner join dbo.dat_Collection c on fs.ObjectID = c.ObjectID inner join dbo.dat_Object parentObj on c.ParentID = parentObj.ObjectID where contains(FileStreamData, @srchTerm) and parentObj.ObjectTypeID = 1 ORDER BY 'Grouping'
Full Text Search # 2 -
select KEY_TBL.RANK, parentObj.ObjectID as 'GroupingID', parentObj.Name as 'Grouping', childObj.ObjectID as 'FileObjID', childObj.Name as 'FileName', fs.FileStreamID from dbo.dat_FileStream fs inner join containstable(dbo.dat_FileStream, FileStreamData, @srchTerm, 1000) as KEY_TBL on fs.FileStreamID = KEY_TBL.[KEY] inner join dbo.dat_Object childObj on fs.ObjectID = childObj.ObjectID inner join dbo.dat_Collection c on fs.ObjectID = c.ObjectID inner join dbo.dat_Object parentObj on c.ParentID = parentObj.ObjectID where parentObj.ObjectTypeID = 1 ORDER BY 'Grouping'
The only significant difference between the two full-text searches is that request # 1 uses contains and request # 2 uses containsstable.
My problem is that these two queries do not always give the same results. For example, if I were searching for the phrase “Independent Contractors”, query # 1 would give a result set of 10 different documents (PDF and XML), and query # 2 would only yield a result set of 6. This seems to be the rule: request # 1 always gives a little more than query # 2, and query # 2 always gives the same match as query # 1.
Request No. 1 - Search for "Independent Contractors" gives:
4262 AAA-00-12 4561 AAA-00-12.pdf 4235 4316 AAA-00-15 4753 AAA-00-15.pdf 4427 4316 AAA-00-15 4754 AAA-00-15.xml 4428 3873 AAA-00-19 4784 AAA-00-19.pdf 4458 3903 AAA-00-22 6795 AAA-00-22.pdf 6459 3953 AAA-00-24 6899 AAA-00-24.pdf 6563 3953 AAA-00-24 6900 AAA-00-24.xml 6564 4842 AAA-00-9 4905 AAA-00-9.pdf 4577 4842 AAA-00-9 4906 AAA-00-9.xml 4578 4057 AAA-0001 4260 AAA-0001.pdf 3936
Request No. 2 - Search for "Independent Contractors" gives:
19 4262 AAA-00-12 4561 AAA-00-12.pdf 4235 126 4316 AAA-00-15 4754 AAA-00-15.xml 4428 126 4316 AAA-00-15 4753 AAA-00-15.pdf 4427 116 3873 AAA-00-19 4784 AAA-00-19.pdf 4458 125 3903 AAA-00-22 6795 AAA-00-22.pdf 6459 57 3953 AAA-00-24 6900 AAA-00-24.xml 6564 57 3953 AAA-00-24 6899 AAA-00-24.pdf 6563