how to create this as an index view?

Question

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(*,'')

Answer 1

You cannot have union in an indexed view or freetext, there are more limitations, here is a partial list

• An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.

• A derived table.

• A common table expression (CTE).

• Rowset functions.

• UNION, EXCEPT or INTERSECT operators.

• The full-text predicates CONTAINS or FREETEXT.

• Subqueries.

• Outer or self joins.

• TOP clause.

• ORDER BY clause.

• DISTINCT keyword.

See here for more info http://msdn.microsoft.com/en-us/library/ms191432.aspx

One way to get around this is to use a stored procedure instead

Answer 2

You can't have unions in an indexed view. You may be able to re-phrase the query as a series of joins though. Also, you can't use FREETEXT with the indexed views. You may be able to get the same effect by naming the columns and using LIKE.

The alternative to using an indexed view is to construct a normal table and use triggers on each of the source tables to keep it updated.