After I wrote about the basics in the blog SQL SERVER – What is Semantics Search Inside SQL Server?, I had multiple requests to understand and get started with Sematic Search. So I thought to set the building blocks of working with the same. Where do we start? Well, here is something for you:
Similar to earlier versions of SQL Server, to use semantic search, you must complete installing “Full-Text” and “Semantic Extractions for Search” on the “Features to Install” page during setup of SQL Server. They are installed together as one feature.
You can run the following command to determine if full-text and semantic search are installed. A return value of 1 indicates that these components are installed; a return value of 0 indicates that they are not installed.
SELECT SERVERPROPERTY('IsFullTextInstalled');
GO
Language Statistical Database
In addition to installing the full-text and semantic search feature, statistical semantic search requires a language statistical database. This installs separately – I will outline the steps below.
This database contains the statistical language models required by semantic search. A single semantic language statistics database contains the language models for all the languages that are supported for semantic indexing.
The language used for the full-text index on a column determines the statistical language model used for semantic indexing. When you create a semantic index on a column, the index creation fails if you specify a language for which the language model is not available.
Note: Fewer languages are supported for semantic indexing than for full-text indexing. As a result, there may be columns that support full-text indexing, but that do not support semantic indexing.
Installing the Language Statistical Database
- Locate the Windows installer package named SemanticLanguageDatabase.msi on the SQL Server installation media.
- Run the SemanticLanguageDatabase.msi Windows installer package to extract the database and log file.
- Move the extracted database file and log file to a suitable location in the file system. If you leave the files in their default location, it will not be possible to extract another copy of the database for another instance of SQL Server.
- Note: When the semantic language statistics database is extracted, restricted permissions are assigned to the database file and log file in the default location in the file system. As a result, you may not have permission to attach the database if you leave it in the default location. If an error is raised when you try to attach the database, move the files, or check and fix file system permissions as appropriate.
- Attach the database using the following command:
CREATE DATABASE semanticsdb
ON (FILENAME = 'C:\Microsoft Semantic Language Database\semanticsdb.mdf')
FOR ATTACH;
GO
Registering the Language Statistical Database
After attaching the Language Statistical Database, you must register it so SQL Server knows to use it as the Language Statistical Database:
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';
GO
Verify the Language Statistical Database is installed and Registered
The following query returns a single row of information if the semantic language statistics database is installed and registered for the instance.
SELECT DB_NAME(database_id) DBName, * FROM sys.fulltext_semantic_language_statistics_database;
GO
In subsequent blogs, I will try to take couple of examples to illustrate how these enabled features can be used in our applications.
Reference : Pinal Dave (https://blog.sqlauthority.com)
