sps Search for FullText

This post is a description of how to use the Microsoft.Office.Server.Search.Query.FullTextSqlQuery class for searching with the MOSS 2007 search engine using the Enterprise Search SQL syntax. I will at the end of this posting explain how to execute queries for optimal results rankings. 

But let me first establish the basics of using the FullTestSqlQuery class. You will only need to employ it for one of the following reasons:

• You need to support Wildcard searches.
• You need to search with date ranges.
• You need to search properties with different operators (CONTAINS, =, >=, <=, <, >, LIKE).
• You need to search properties for NULL values.
• You need to use the NEAR operator.
• You need nested Boolean queries.
• You like the most flexible (or complicated ;-) ) solution.

Simple keyword and property queries are easier done with the Microsoft.Office.Server.Search.Query.KeywordQuery class. I will, however, not cover this class here.

Ok, let us get started. I have outlined the format of the SQL Syntax accepted by the MOSS 2007 search engine below. After that I will show how to talk to the search engine via the official .NET API.

SQL Syntax
A SQL query is a string that must adhere to the following structure:

SELECT <columns>
FROM <content source>
WHERE <conditions>
ORDER BY <columns>

SQL Syntax Examples

1. Finds relevant results containing the keyword SharePoint.
   
   

   SELECT WorkId,Path,Title,Write,Author,HitHighlightedSummary,
          HitHighlightedProperties,CollapsingStatus
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') 
   ORDER BY Rank Desc

2. Finds relevant results containing at least one of the keywords SharePoint and Search.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint Search') 
   ORDER BY Rank Desc

3. Finds relevant results containing both the keywords SharePoint and Search.

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, '+SharePoint +Search') 
   ORDER BY Rank Desc

4. Finds relevant results containing the exact phrase SharePoint Search.

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, ' "SharePoint Search" ') 
   ORDER BY Rank Desc

5. Finds relevant results containing both the keywords SharePoint and Search but not the keyword WSS. 

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, '+SharePoint +Search -WSS') 
   ORDER BY Rank Desc

6. Finds relevant SharePoint results authored by persons named John.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') AND CONTAINS(Author,' "John" ')
   ORDER BY Rank Desc

7. Finds relevant SharePoint results modified within the last 30 days.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') AND Write<=DATEADD(DAY,30,GETGMTDATE())
   ORDER BY Rank Desc

 .NET API
The following code should give you some inspiration how to execute a SQL query against the MOSS 2007 search engine.

// Execute Query
ResultTableCollection results = null;
using (FullTextSqlQuery query = new FullTextSqlQuery(ServerContext.Current))
{
  query.StartRow = 0;
  query.RowLimit = 10;
  query.HighlightedSentenceCount = 3;
  query.EnableStemming = true;
  query.TrimDuplicates = true;
  query.Culture = CultureInfo.CurrentCulture;

  query.KeywordInclusion = KeywordInclusion.AnyKeyword;
  query.SiteContext = new Uri("http://yourserver/sites/asite");




  if (SPSecurity.AuthenticationMode != AuthenticationMode.Windows)

    query.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
  else

    query.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;
  query.QueryText = "SELECT ... FROM Scope() WHERE ... ORDER BY ..."; 
  results = query.Execute();
}

// Parse results and create XML output
StringBuilder buffer = new StringBuilder(10240);
ResultTable relevantResults = results[ResultType.RelevantResults];
using (XmlTextWriter writer = new XmlTextWriter(new StringWriter(buffer)))
{
  writer.Formatting = Formatting.Indented;
  writer.WriteStartElement("Results");
  writer.WriteAttributeString("hits", relevantResults.TotalRows.ToString());
  while (relevantResults.Read())
  {
    writer.WriteStartElement("Result");
    for (int i=0; i<relevantResults.FieldCount; i++)
    {
      writer.WriteStartElement(relevantResults.GetName(i));
      object val = relevantResults.GetValue(i);
      if (val != null)
        writer.WriteString(val.ToString());
      else
        writer.WriteString("null");
      writer.WriteEndElement();
    }      
    writer.WriteEndElement(); 
  }
  writer.WriteEndElement(); 

  string xml = buffer.ToString();
  ...






}

The using statement around the usage scope of the FullTextSqlQuery class is very important to remember as you will otherwise get an OutOfMemoryException after running a good number of queries. This is so because the search engine is a COM server that the .NET API maintains a handle to.

A closer look at the FREETEXT predicate
Take a look at following two keyword searches using the FREETEXT predicate (rest of SQL string omitted for simplicity):

FREETEXT(defaultproperties, '+sharepoint +search')

FREETEXT(defaultproperties, 'sharepoint') AND FREETEXT(defaultproperties, 'search')

Both queries are valid and yield the same results buth with different ranking. Which one yields the best results ranking? Answer: The first one! It is recommended that you only use one FREETEXT predicate in a search query. Results ranking will otherwise not be optimal.

The defaultproperties keywords references the default set of properties to include in the ranking algorithm. This is also recommended for optimal ranking of results. You can alternatively use the WITH predicate to define your own set of properties if you are not happy with the default one. This is helpful if you need to promote results containing the keywords on your own custom properties. Note: The standard MOSS 2007 search center simply uses the defaultproperties.

The FREETEXT predicate can also be configured for implicit AND search or implicit OR search. This means that a query like:

FREETEXT(defaultproperties, 'sharepoint search')

can respectively find results containing both the keywords sharepoint and search or find results containing at least one of the keywords. Use the the KeywordInclusion property on the FulltextSqlQuery object to control this behavior. See the code example earlier in this posting.

Conclusion
I have just described the key concepts of using the FulltextSqlQuery class. I have not described all of its features nor have I described the SQL Syntax in full. I refer you to the MOSS 2007 SDK for a complete reference.

Stay stuned for more postings on the inner workings of the MOSS 2007 search engine!