Using Full-text Search In Sql Server 2005 Across Multiple Tables, Columns

I have a problem, I created a full text search query which return a record(s), in which the paramater I have supplied match(es) in every fields(full-text indexed) of multiple table

Solution 1:

SELECT u.Id
FROM Users u
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT c.AId
FROM Certification c
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT ad.AId
FROM ApplicantDetails ad
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT eb.AId
FROM EducationalBackground eb
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT ed.AId
FROM EmploymentDetails ed
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT e.AId
FROM Expertise e
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT ge.AId
FROM GeographicalExperience ge
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT pd.AId
FROM ProjectDetails pd
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT r.AId
FROM [References] r
WHERE FREETEXT(*,'"BPI"')
UNIONSELECT t.AId
FROM Training t
WHERE FREETEXT(*,'"BPI"')

Solution 2:

Another solution but thisis very slow compare the below query.

select DISTINCT u.Id  from Users u
 inner join Certification c   on u.ID =
 c.AId inner join ApplicantDetails ad  
 on u.ID=ad.AId inner join 
 EducationalBackground eb   on
 u.ID=eb.AId inner join
 EmploymentDetails ed   on u.Id=ed.AId
 inner join Expertise e   on u.Id=e.AId
 inner join GeographicalExperience ge  
 on u.Id=ge.AId inner join [Language] l
 on u.Id=l.AId inner join
 ProjectDetails pd   on u.Id=pd.AId
 inner join [References] r   on
 u.Id=r.AId inner join Training t  on
 u.Id=t.AId left joinFreeTexttable
 (Users,
 (AlternativeEmail,Email,Firstname,Lastname,Middlename),
 'xandrick' ) as uf   on uf.[KEY] =
 u.id left joinFreeTexttable
 (ApplicantDetails,
 (Address1,Address2,City,Province,StateorRegion),
 'xandrick' ) as adf   on adf.[KEY] =
 ad.id left joinFreeTexttable
 (Certification,
 (Certification,School), 'xandrick' )
 as cf   on cf.[KEY] = c.id left joinFreeTexttable (EducationalBackground,
 (fieldofStudy,other,School),
 'xandrick' ) as ebf   on ebf.[KEY] =
 eb.id left joinFreeTexttable
 (EmploymentDetails,
 (Address1,Address2,City,CompanyName,DescriptionofDuties,Position,Province,TypeofBusiness),
 'xandrick' ) as edf   on edf.[KEY] =
 ed.id left joinFreeTexttable
 (Expertise, (Expertise), 'xandrick' )
 as ef   on ef.[KEY] = e.id left joinFreeTexttable (GeographicalExperience,
 ([Description]), 'xandrick' ) as gef  
 on gef.[KEY] = ge.id left joinFreeTexttable ([Language],
 ([Language]), 'xandrick' ) as lf   on
 lf.[KEY] = l.id left joinFreeTexttable (ProjectDetails,
 (Address1,Address2,City,ProjectDescription,Projectname,Projectrole,Province,ServiceRendered,StateorRegion),
 'xandrick' ) as pdf   on pdf.[KEY] =
 pd.id left joinFreeTexttable
 ([References],
 (ContactDetails,CurrentPosition,Name,Organization),
 'xandrick' ) as rf   on rf.[KEY] =
 r.id left joinFreeTexttable
 (Training, (School,Training),
 'xandrick' ) as tf   on tf.[KEY] =
 t.id

 where uf.[KEY] isnotnull    OR
 adf.[KEY] isnotnull   OR cf.[KEY] isnotnull   OR ebf.[KEY] isnotnull  
 OR edf.[KEY] isnotnull   OR ef.[KEY]
 isnotnull   OR gef.[KEY] isnotnull
 OR lf.[KEY] isnotnull   OR pdf.[KEY]
 isnotnull   OR rf.[KEY] isnotnull 
 OR tf.[KEY] isnotnull

Solution 3:

By far the absolute best way of doing this is to use an indexed view which combines the tables in question. Add your free-text index to the view, then use that for your searches.

Believe it or not, but it is MUCH faster than running multiple freetexttable clauses.