I am running a few selects over adventure works.
(i.e.
SELECT *
FROM Production.Product
WHERE FREETEXT(*, 'screw washer spaner');
)
I have yet to encounter a select that uses the thesaurus and displays the finding of synonyms.
How can I know if I am using this feature?
can anyone supply a select that demonstrate the usage of the thesaurus
Here is the setup script (thanks to Itzik Ben-Gan (author Querying SQL Server)) which will allow you to use example query at bottom of thesaurus:
IF OBJECT_ID('dbo.Documents', 'table') IS NOT NULL
DROP TABLE dbo.Documents;
CREATE TABLE dbo.Documents
(
id INT NOT NULL IDENTITY,
title NVARCHAR(100) NOT NULL,
doctype NCHAR(4) NOT NULL,
docexcerpt NVARCHAR(1000) NOT NULL,
doccontent VARBINARY(MAX) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY CLUSTERED(id)
);
GO
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Introduction to Data Mining',
N'docx',
N'Using Data Mining is becoming more a necessity for every company
and not an advantage of some rare companies anymore. ',
bulkcolumn;
GO
--Edit the thesaurus file by adding
<expansion>
<sub>need</sub>
<sub>necessity</sub>
</expansion>
--Run the following to reload your edited thesaurus
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
GO
And in a separate batch, execute the following command:
SELECT *
FROM dbo.Documents
WHERE FREETEXT(doccontent, N'FORMSOF(THESAURUS, need)');
GO
The word "need" is not in the docexcerpt, however as the synonym is loaded in the thesaurus, the row will return if the thesaurus is loaded properly. If you have issues with that, there are many StackOverflow articles and BOL entries on how to load/configure it.
