Full-Text Index and Semantic Search in SQL Server 2014
SQL Server 2014 Statistical Semantic Search functions use indexes created by Full-Text Search. SQL Server semantic search queries can be used to figure out key phrases in a database table text column. Administators and SQL developers can create Transact-SQL statements to identify key words in a table row column and find similar or related data by using identified keyphrases or keywords.
In this SQL tutorial, I tried to demonstrate one of the SQL Server Semantic Search functions SemanticKeyPhraseTable on a SQL Server 2014 sample database table to identify most significant keyword or key phrase. To cover SQL Semantic Search, first of all requirements or prerequisites like Full-Text Search and setup of Semantic Search database are discussed. After installation and configuration of Semantic Search on SQL Server 2014, first semantic search query using SemanticKeyPhraseTable is built and executed.
There is a good SQL Server tutorial on MSDN for installing and configuring semantic search on SQL Server database tables. I want to summarize the steps here with a sample case.
One of the basic advantages that semantic search over full-text search on SQL Server table data is:
SQL developer has to explicitly provide the search text or query text in SQL Server fulltext search commands like CONTAINS or CONTAINSTABLE.
On the other hand, applying semantic search on a database table column the SEMANTICKEYPHRASETABLE command is able to find out the keywords or text important or significant.
This is very important if you want to figure out the important words used repeatedly in a text for instance.
This SQL Server 2014 Semantic Search tutorial shows how to list the top keywords stored on a database table column.
SQL Server Full-Text Search
As first step, check if fulltext search is installed on your SQL Server 2014 instance. If the outcome is 1 then full-text is installed for the SQL Server 2014 database instance.
SELECT SERVERPROPERTY('IsFullTextInstalled')
If SQL Server Full-Text Search is not installed, please check SQL Server Full-Text Search installation and first complete SQL Server Full-Text Search on your database server before you continue with this SQL Semantic Search tutorial
SQL Server Semantic Language Statistics Database
As second step, database administrators and developers should check if SQL Server 2014 Semantic Language Statistics database is installed or not.
SELECT * FROM sys.fulltext_semantic_language_statistics_database
If there is no rows returned, then download Microsoft SQL Server 2014 Semantic Language Statistics Database from Microsoft Download Center (for SQL Server 2014)
For SQL Server 2016 and later (SQL Server 2017 and SQL Server 2019) database programmers can download and attach Semantic Language Statistics Database using this download link Microsoft SQL Server 2016 Semantic Language Statistics
According to your SQL Server architecture (32-bit or 64-bit) download the correct SemanticLanguageDatabase.msi file and install it.
In fact, the .msi file will extract only the database data file semanticsDB.mdf and the log file semanticsdb_log.ldf
Since I experienced a lot of problem with restrictions due to security policies on my domain for running and installing .msi files, I suggest this database files can be available for download in a zipped format instead of msi file format.
Copy the database file and log file which are extracted from SemanticLanguageDatabase.msi file into SQL Server Data folder or the file folder where you store your database files. I moved them to SQL Server Data folder "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
Attach data files to create a new SQL Server database named semanticsdb.
SQL developers and database administrators can modify below T-SQL command to create database semanticsdb according to their environment.
CREATE DATABASE semanticsdb
ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\semanticsdb.mdf' )
LOG ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\semanticsdb_log.ldf' )
FOR ATTACH
After the semantic database is created successfully administrator and programmers have to register it by using the below SQL command
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
After these steps are completed the following SELECT query will return a result indicating the database id of semanticsdb database in SQL Server sys.databases system table
SELECT * FROM sys.fulltext_semantic_language_statistics_database
Create SQL Server FullText Catalog
SQL Server data professionals can now create a full-text catalog in the target database where the table they want to query exists. If you have created your full text catalog before installing and configuring the semantic database, it is OK as well.
In case if this is the first time you will be creating a full-text catalog, you can simple execute following DDL command.
CREATE FULLTEXT CATALOG KodyazFTCatalog
Create FullText Index
Although you are now ready to create fulltext index on your database table column which includes the text data we will apply semantic search, I will share with you sample database table create and data insert script for the sake of this SQL Server tutorial.
It is important that the table must have a unique index.
Create Table txt.TextTable (
id int identity(1,1),
textColumn nvarchar(max)
)
create unique nonclustered index ix_texttable_id on txt.texttable(id)
insert into txt.TextTable select 'Memory-Optimized Tables'
insert into txt.TextTable select 'AlwaysOn Enhancements'
insert into txt.TextTable select 'Buffer Pool Extension'
insert into txt.TextTable select 'SQL Server Data Files in Windows Azure'
insert into txt.TextTable select 'Power View for Multidimensional Models'
insert into txt.TextTable select 'Columnstore Indexes'
insert into txt.TextTable select 'Encryption for Backups'
insert into txt.TextTable select 'Hot Add Memory and CPU'
Now database developer or the database administrator can create fulltext index on target table column using following Transact-SQL syntax. Please notice that in below command, the unique index defined on the table and the full-text catalog created in the database is used.
CREATE FULLTEXT INDEX ON txt.TextTable
(
textColumn STATISTICAL_SEMANTICS
)
KEY INDEX ix_texttable_id ON KodyazFTCatalog
Using the STATISTICAL_SEMANTICS for the full-text index column definition is important.
Otherwise, sql developer can not use SEMANTICKEYPHRASETABLE function or the other semantic search functions like SEMANTICSIMILARITYTABLE and SEMANTICSIMILARITYDETAILSTABLE functions.
In case if you experience the following error message after you execute SQL Server semantic search query using one of the above SQL functions (SEMANTICSIMILARITYTABLE, SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE):
Msg 41203, Level 16, State 1, Line 37
The column 'textColumn' specified in the SEMANTICSIMILARITYTABLE, SEMANTICKEYPHRASETABLE or SEMANTICSIMILARITYDETAILSTABLE function is not full-text indexed with the STATISTICAL_SEMANTICS option. The column must be full-text indexed using the STATISTICAL_SEMANTICS option to be used in this function.
You can solve this error by applying the following steps:
On SQL Server Management Studio Object Explorer windows, right click on table.
From context menu, choose options: Full-Text index > Properties
On Columns tab, mark the Statistical Semantics checkbox.
Sample SQL Server Semantic Search Query
We are now ready to build our first SQL Server semantic query developed over SQL Server 2014 full-text index using SemanticKeyPhraseTable function.
Here is our first SQL query with full-text semantic search functions
SELECT
column_id,
document_key,
keyphrase,
score,
id,
textColumn
FROM SemanticKeyPhraseTable(txt.TextTable, textColumn) as p
INNER JOIN txt.TextTable as t
ON p.document_key = t.id
Note that the table valued SQL Server semantic search function SemanticKeyPhraseTable() data in returns column_id, document_key, keyphrase and score column format. The returned data is joined to original table data using the document_key defined as unique index for the table.
Following data is what we get after executing or running above SQL Select statement on our database with sample data
We can modify above query and add some more intelligence to count the keywords or key phrases used in table column data and return the most significant one. SQL developers can easily understand the use of SQL CTE expression to simplify grouping for Count() function over key words and key phrases.
;WITH CTE as (
SELECT
column_id,
document_key,
keyphrase,
score,
id,
textColumn
FROM SemanticKeyPhraseTable(txt.TextTable, textColumn) as p
INNER JOIN txt.TextTable as t
ON p.document_key = t.id
)
SELECT TOP 1 keyphrase, COUNT(*)
FROM CTE
GROUP BY keyphrase
ORDER BY COUNT(*) desc
An interesting result of the above query with my sample data that I collected for this SQL Server semantic search tutorial is that: "memory" is the most significant or most repeated keyword
If you remember the early announcements of Hekaton with in-memory OLTP enhancements and performance boost because of in-memory operation capabilities of SQL Server 2014, it is normal that "memory" is the top repeated key word or keyphrase for data about SQL Server 2014 features.
Thanks to SQL Server Full-Text index improvements and SQL Server Semantic Search enhancements introduced with SQL Server 2014 which simplified analysis like we did in this SQL tutorial.