After discussing the basics of Semantic Search in a previous blog post, I received numerous requests to understand and get started with Semantic Search. So, let’s set the building blocks for working with this feature.

Similar to earlier versions of SQL Server, in order to use Semantic Search, you must complete the installation of “Full-Text” and “Semantic Extractions for Search” on the “Features to Install” page during the setup of SQL Server. These components are installed together as one feature. To determine if Full-Text and Semantic Search are installed, you can run the following command:

SELECT SERVERPROPERTY('IsFullTextInstalled');

A return value of 1 indicates that these components are installed, while a return value of 0 indicates that they are not installed.

In addition to installing the Full-Text and Semantic Search feature, statistical Semantic Search requires a language statistical database. 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. It’s important to note that fewer languages are supported for semantic indexing compared to full-text indexing. Therefore, there may be columns that support full-text indexing but do not support semantic indexing.

To install the Language Statistical Database, follow these steps:

1. Locate the Windows installer package named “SemanticLanguageDatabase.msi” on the SQL Server installation media.
2. Run the “SemanticLanguageDatabase.msi” Windows installer package to extract the database and log file.
3. Move the extracted database file and log file to a suitable location in the file system. It’s recommended to not leave the files in their default location to avoid any issues when extracting another copy of the database for another instance of SQL Server.
4. Attach the database using the following command:

CREATE DATABASE semanticsdb ON (FILENAME = 'C:\Microsoft Semantic Language Database\semanticsdb.mdf') FOR ATTACH;

After attaching the Language Statistical Database, you must register it so that SQL Server knows to use it as the Language Statistical Database. Use the following command to register the database:

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';

To verify if the Language Statistical Database is installed and registered, you can run the following query:

SELECT DB_NAME(database_id) AS DBName, * FROM sys.fulltext_semantic_language_statistics_database;

If the query returns a single row of information, it means that the semantic language statistics database is installed and registered for the instance.

In subsequent blog posts, we will explore a few examples to illustrate how these enabled features can be used in our applications.