semanticsimilaritydetailstable (Transact-SQL)

Article
08/10/2023

Applies to: SQL Server

Returns a table of zero, one, or more rows of key phrases that are common across two documents (a source document and a matched document) whose content is semantically similar.

This rowset function can be referenced in the FROM clause of a SELECT statement

Transact-SQL syntax conventions

Syntax

SQL

SEMANTICSIMILARITYDETAILSTABLE  
    (  
    table,  
    source_column,  
    source_key,  
    matched_column,  
    matched_key  
    )

Arguments

table
Is the name of a table that has full-text and semantic indexing enabled.

This name can be a one to four part name, but a remote server name is not allowed.

source_column
Name of the column in the source row that contains the content to be compared for similarity.

source_key
The unique key that represents the row of the source document.

This key is implicitly converted to the type of the full-text unique key in the source table whenever possible. The key can be specified as a constant or a variable, but cannot be an expression or the result of a scalar sub-query. If an invalid key is specified, no rows are returned.

matched_column
Name of the column in the matched row that contains the content to be compared for similarity.

matched_key
The unique key that represents the row of the matched document.

Table Returned

The following table describes the information about key phrases that this rowset function returns.

Column_name	Type	Description
keyphrase	NVARCHAR	The key phrase that contributes to the similarity between source document and the matched document.
score	REAL	A relative value for this key phrase in its relationship to all the other key phrases that are similar between the 2 documents. The value is a fractional decimal value in the range of [0.0, 1.0] where a higher score represents a higher weighting and 1.0 is the perfect score.

General Remarks

For more information, see Find Similar and Related Documents with Semantic Search.

Metadata

For information and status about semantic similarity extraction and population, query the following dynamic management views:

Security

Permissions

Requires SELECT permissions on the base table on which the full-text and semantic indexes were created.

Examples

The following example retrieves the 5 key phrases that had the highest similarity score between the specified candidates in HumanResources.JobCandidate table of the AdventureWorks2022 sample database. The @CandidateId and @MatchedID variables represent values from the key column of the full-text index.

SQL

SELECT TOP(5) KEY_TBL.keyphrase, KEY_TBL.score  
FROMSEMANTICSIMILARITYDETAILSTABLE  
    (  
    HumanResources.JobCandidate,  
    Resume, @CandidateID,  
    Resume, @MatchedID  
    ) AS KEY_TBL  
ORDER BY KEY_TBL.score DESC;

Share via