asked
How to create this as an index view?
SELECT
u.Id FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName), '') UNION SELECT c.AId FROM Certification c WHERE FREETEXT(*, '') UNION SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT(*, '') UNION SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT(*, '') UNION SELECT ed.AId FROM EmploymentDetails ed WHERE FREETEXT(*, '') UNION SELECT e.AId FROM Expertise e WHERE FREETEXT(*, '') UNION SELECT ge.AId FROM GeographicalExperience ge WHERE FREETEXT(*, '') UNION SELECT pd.AId FROM ProjectDetails pd WHERE FREETEXT(*, '') UNION SELECT r.AId FROM [References] r WHERE FREETEXT(*, '') UNION SELECT t.AId FROM Training t WHERE FREETEXT(*, '')
You cannot directly create that as an indexed view. You use UNION and FREETEXT, both of which are forbidden in indexed views. There is a reference at: [
http://msdn.microsoft.com/en-us/library/ms191432.aspx][1] What you could do, that may achieve what you want, is to set up a separate table to store the values in question and a procedure to update that other table, then schedule that procedure to run on a regular basis. This will mean it is not continually up to date the way an indexed view would be, but it may be close enough depending on your exact need. Similarly, you could use triggers, but that can create many complications and would be very expensive in terms of performance. You are probably best in the end creating this as a nonindexed view and simply using it that way, depending on your exact use case of course. [1]:
http://msdn.microsoft.com/en-us/library/ms191432.aspx
