SQL Reference - SEMANTICSIMILARITYTABLE

SEMANTICSIMILARITYTABLE

SEMANTICSIMILARITYTABLE is a Transact-SQL table-valued function that returns a table with columns for matched similarity of all rows in a specified table. This function is used to find the statistical semantic similarity between the rows of a table based on the values in a specified columns. It is often used in full-text semantic indexing, to search for key phrases and documents that are similar to a specific text.

SEMANTICSIMILARITYTABLE( table, { column | (column_list) | * }, { registered_source_name, source_id | @source_string })

table: This parameter represents the table on which the SEMANTICSIMILARITYTABLE function is applied. The table should have a full-text index with a registered semantic search key.
{column|(column_list)|}: This parameter indicates the column or columns on which semantic search will be performed. The selected column or columns should be of type varchar(max), nvarchar(max), or varbinary(max). If ”*”, all full-text indexed columns are included.
{registered_source_name, source_id|@source_string}: This parameter provides the specific document to be compared with other documents in the indexed table. ‘registered_source_name, source_id’ represents a row identifier from the indexed table. Alternatively, @source_string can be used to pass a string of type varchar(max), nvarchar(max), or varbinary(max).

Example

USE AdventureWorks2012
GO
SELECT TOP(5) KEY_TBL.RANK, FT_TBL.Description
FROM Production.ProductDescription AS FT_TBL
INNER JOIN
SEMANTICSIMILARITYTABLE
(
    Production.ProductDescription,
    Description,
    (SELECT Description
    FROM Production.ProductDescription
    WHERE ProductDescriptionID = 5)
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.KEY_COLUMN
ORDER BY KEY_TBL.RANK DESC;
GO

Output

| RANK | Description                   |
|------|-------------------------------|
| 12   | Comfortable mountain bike     |
| 15   | Racing mountain bike          |
| 8    | Touring road bike             |
| 10   | Roadmaster road bike          |
| 20   | Top performing on trails bike |

Explanation

The SEMANTICSIMILARITYTABLE function returns a table of zero, one, or more rows for those columns containing character-based data types with values that are similar to a specified expression. The returned table has a RANK column and one column per uniqueidentifier rowset column in the indexed view. Each row contains the similarity rank and uniqueidentifier column values for a document that is similar to a specified document or document section. In the provided example, SEMANTICSIMILARITYTABLE is used to find the top 5 similar product descriptions to the product with ProductDescriptionID = 5.