SET @Title = 'Sample Document.docx'
SELECT @DocID = DocumentID
FROM Documents
WHERE DocumentTitle = @Title
SELECT @Title AS Title, keyphrase, score
FROM SEMANTICKEYPHRASETABLE(Documents, *, @DocID)
ORDER BY score DESC
(1) @Title and @DocID are not declared.
(2) The column names DocumentID and DocumentTitle are different from ID and Title in the dbo.Documents table when we attach the sample database
(3) The Document table in the sample database does not have a row for 'Sample Document.docx'
(4) After installing the sample semantic database, we need to enable semantic search on the Documents table and column(s).
(3) Finally,modify the code to perform semantic search.
=============================CODE================================
Use SemanticsDB
GO
--Create a fulltext catalog
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO
/*
1. Verify the columns and contents in the sample 'dbo.Documents' table.
2. We care about 3 columns the most: id, title, and docexcerpt. The last one will have a full-text index on it.
3. Also note there are four titles in the sample table as below:
Columnstore Indices and Batch Processing
Introduction to DataMining
Why Is Bleeding Edge a Different Conference
Additivity of Measures
*/
SELECT * FROM [dbo].[Documents]
/*
1. Create a full-text index for the column(s) for semantic search. In the sample table, that's docexcerpt
2. Notice the PK for the able (PK_Document) is used.
*/
CREATE FULLTEXT INDEX ON [dbo].[Documents]
(docexcerpt
Language 1033
Statistical_Semantics
)
KEY INDEX PK_Documents
WITH STOPLIST = SYSTEM
GO
--Now we can perform semantic search on the docexcerpt column.
DECLARE @Title varchar(50)
DECLARE @DocID varchar(50)
SET @Title = 'Introduction to Data Mining' --notice:this is one entry in the table, also no .docx
SELECT @DocID = ID --Note: not DocumentID as in the sample code
FROM dbo.Documents
WHERE Title = @Title --Note: not DocumentTitle as in the sample code
SELECT @Title AS Title, keyphrase, score
FROM SEMANTICKEYPHRASETABLE(dbo.Documents,*, @DocID)
ORDER BY score DESC
--The output is as below:
/*
Title keyphrase score
===================================================
Introduction to Data Mining necessity 0.3286656
Introduction to Data Mining anymore 0.2987682
Introduction to Data Mining mining 0.2788366
Introduction to Data Mining rare 0.2755147
Introduction to Data Mining becoming 0.262227
Introduction to Data Mining advantage 0.2589051
Introduction to Data Mining data 0.2256858
Introduction to Data Mining using 0.2256858
Introduction to Data Mining every 0.2057543
Introduction to Data Mining companies 0.1991104
Introduction to Data Mining company 0.1758569
Introduction to Data Mining not 0.1293499
*/
