What are the key points to be followed when implementing Full-Text Search on View ?

 Posted by PandianS on 9/16/2010 | Category: Sql Server Interview questions | Views: 2441 | Points: 40
Answer:

When we implement a Full-Text Search on View, We have to follow the key rules

1. View should be 'WITH SCHEMABINDING', Otherwise the following Err will be thrown
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VM_DataTypes' because the view is not schema bound.


2. All tables should be used with schema name (Two part-naming convension, "SchemaName.TableName"), Otherwise the following Err will be thrown
Msg 4512, Level 16, State 3, Procedure VM_DataTypes, Line 2
Cannot schema bind view 'VM_DataTypes' because name 'TB_Category' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


3. Table hint should not be used inside the view(WITH NOLOCK), Otherwise the following Err will be thrown
Msg 10140, Level 16, State 1, Line 1
Cannot create index on view 'DotNetFunda.dbo.VM_DataTypes' because the view contains a table hint. Consider removing the hint.


4. OUTER Join can not be used Inside the View

5. Non-Deterministic & Windows function cannot be used inside the View

6. The Unique Index should be created on single column, Not with composite, Non-Nullable

7. The Index size should not exceed 900 Bytes.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response