semantickeyphrasetable (Transact-SQL)

Article
05/16/2013

Returns a table with zero, one, or more rows for key phrases associated with the specified columns in the specified table.

This rowset function can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Topic link icon Transact-SQL Syntax Conventions

Syntax

SEMANTICKEYPHRASETABLE
    (
    table,
    { column | (column_list) | * }
     [ , source_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.
column
Name of the indexed column for which results should be returned. Column must have semantic indexing enabled.
column_list
Indicates several columns, separated by a comma and enclosed in parentheses. All columns must have semantic indexing enabled.
*
Indicates that all columns that have semantic indexing enabled are included.
source_key
Unique key for the row, to request results for a specific row.

The 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 source_key is omitted, then results are returned for all rows.

Table Returned

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

Column_name	Type	Description
column_id	int	ID of the column from which the current key phrase was extracted and indexed. See the COL_NAME and COLUMNPROPERTY functions for details on how to retrieve column name from column_id and vice versa.
document_key	* This key matches the type of the unique key in the source table.	Unique key value of the document or row from which the current key phrase was indexed.
keyphrase	NVARCHAR	The key phrase found in the column identified by column_id, and associated with the document specified by document_key.
score	REAL	A relative value for this key phrase in its relationship to all the other key phrases in the same document in the indexed column. 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 Key Phrases in Documents with Semantic Search.

Metadata

For information and status about semantic key phrase 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

Example 1: Find the Top Key Phrases in a Specific Document

The following example retrieves the top 10 key phrases from the document specified by the @DocumentId variable in the Document column of the Production.Document table of the AdventureWorks sample database. The @DocumentId variable represents a value from the key column of the full-text index. The SEMANTICKEYPHRASETABLE function retrieves these results efficiently by using an index seek instead of a table scan. This example assumes that the column is configured for full-text and semantic indexing.

SELECT TOP(10) KEYP_TBL.keyphrase
FROM SEMANTICKEYPHRASETABLE
    (
    Production.Document,
    Document,
    @DocumentId
    ) AS KEYP_TBL
ORDER BY KEYP_TBL.score DESC;

Example 2: Find the Top Documents that Contain a Specific Key Phrase

The following example retrieves the top 25 documents that contain the key phrase “Bracket” from the Document column of the Production.Document table of the AdventureWorks sample database. This example assumes that the column is configured for full-text and semantic indexing.

SELECT TOP (25) DOC_TBL.DocumentID, DOC_TBL.DocumentSummary
FROM Production.Document AS DOC_TBL
    INNER JOIN SEMANTICKEYPHRASETABLE
    (
    Production.Document,
    Document
    ) AS KEYP_TBL
ON DOC_TBL.DocumentID = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase = 'Bracket'
ORDER BY KEYP_TBL.Score DESC;

Share via