Freigeben über

Beyond Relational– Semantic Search with SQL Server FileTable

  • Artikel

Semantic Search new for SQL Server Denali adds extra intelligence to the FullText search capability that has existed in SQL Server for several releases, by looking at the relationships between words and in and across each document.  For example you can find which documents are similar to a specified document, and this could help with matching candidates’ CVs to job adverts, or you can find the key words and phrases in a given set of documents. 

This statistical analysis information  is stored in a special database (which is supplied but not installed in SQL Server Denali) and is refreshed as part of the process that update the FullText indexes it depends on.

Once it is setup you have access to three tabular functions:

Note that in CTP3 only words not phrases work in these functions and that currently doesn’t understand word stems e.g. that cloudy and clouds are derivations of the term cloud and treats each as separate words.  This could be fixed as Denali gets nearer to release but there is no guarantee of this.

There are examples of this in the adventure works database which you can try, but I wanted to see what it would do with my FileTable of PowerPoint presentations I created in my last three blog posts, and have a short screen cast on this ….

and the 4 FileTable screencasts in this series are now linked (hyperlinks at the end of each screencast) so you can hop through the series.

Here are some notes if you want to install semantic search:

  • You’ll need to have installed SQL Server Denali ctp3 with the FullText option. 
  • Having done that you’ll need to install the SemanticLanguageDatabase.msi in the SQL install media (there are x86 and x64 versions). 
  • This database then needs to be attached to SQL Server 
  • It is then registered with this statement:
 EXEC sp_fulltext_semantic_language_statistics_db @dbname = N'Semanticsdb'
  •   You’ll need to add FullText indexing on the FileTables (or any other table)  the extra semantic_statistics clause:
 CREATE FULLTEXT INDEX ON dbo.MyDecks
    (name 
        LANGUAGE 2057 
        STATISTICAL_SEMANTICS,
    file_stream 
        TYPE COLUMN file_type 
        LANGUAGE 2057
        STATISTICAL_SEMANTICS)
    KEY INDEX PK_MyDecks_5A5B77D56E0F5D8F
    ON DeepFat_Catalog
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

Comments

  • Anonymous
    November 04, 2011
    Hi! You can check out this sample application that uses Semantic Search features. It was released about a week ago. mysemanticsearch.codeplex.com Regards, Daniel

  • Anonymous
    November 10, 2011
    Daniel Loving your work, just didn't know about your application when I published this Andrew