Работа с FullTextSearch

Решение:

стр. 245 в книге AsP.NET E-Commerce

1. Проверка на установку
select fulltextserviceproperty('isfulltextinstalled')

Создание:
а. Для базы создаем каталог
CREATE FULLTEXT CATALOG BalloonShopFullText

б. Для таблицы при редактировании(design в контекстном меню) столбцов выбираем в меню TableDisagner/FullTestIndexes. И добавляем новые столбцы(в поле Columns), по котоым будем искать.

в. Запросы:
-- выбираем различные формы для слова good по полю DESC
SELECT *
FROM Persons
WHERE Contains (Persons.[desc], 'FORMSOF(INFLECTIONAL, good)', LANGUAGE 'English')

--выбираем данные по релевантности
SELECT *
FROM Containstable (Persons, [desc], 'good')

SELECT *
FROM Persons INNER JOIN
(select * from
CONTAINSTABLE(Persons, *, 'good')) AS PersonsResults
ON Persons.id = PersonsResults.[KEY]
ORDER BY Rank desc

-- выбор данных в которых больший приоритет достается с релевантными словами в Name, а не Desc
SELECT ProductID, Name,
NameResults.Rank as NameRank,
DescriptionResults.Rank as DescriptionRank
FROM Product
LEFT OUTER JOIN
CONTAINSTABLE(Product, Name, 'red') AS NameResults
ON Product.ProductID = NameResults.[KEY]
LEFT OUTER JOIN
CONTAINSTABLE(Product, Description, 'red') AS DescriptionResults
ON Product.ProductID = DescriptionResults.[KEY]

WHERE (NameResults.Rank IS NOT NULL) OR (DescriptionResults.Rank IS NOT NULL)
ORDER BY (ISNULL(NameResults.Rank, 0) * 3 +
ISNULL(DescriptionResults.Rank, 0)) DESC

-- а здесь добавляетсЯ поиск любых слов (red ballon)
SELECT ProductID, Name,
NameResults.Rank as NameRank,
DescriptionResults.Rank as DescriptionRank
FROM Product
LEFT OUTER JOIN
CONTAINSTABLE(Product, Name,
'FORMSOF(INFLECTIONAL, red) OR FORMSOF(INFLECTIONAL, balloon)',
LANGUAGE 'English') AS NameResults
ON Product.ProductID = NameResults.[KEY]
LEFT OUTER JOIN
CONTAINSTABLE(Product, Description,
'FORMSOF(INFLECTIONAL, red) OR FORMSOF(INFLECTIONAL, balloon)',
LANGUAGE 'English') AS DescriptionResults
ON Product.ProductID = DescriptionResults.[KEY]
WHERE (NameResults.Rank IS NOT NULL) OR (DescriptionResults.Rank IS NOT NULL)
ORDER BY (ISNULL(NameResults.Rank, 0) * 3 +
ISNULL(DescriptionResults.Rank, 0)) DESC

===============================================================================================
Создание хранимых процедур поиска
1. Let’s first create the SearchWord procedure. Using SQL Server Management Studio, open a new query
window and execute the following code, which creates the SearchWord stored procedure:
CREATE PROCEDURE SearchWord (@Word NVARCHAR(50))
AS
SET @Word = 'FORMSOF(INFLECTIONAL, "' + @Word + '")'
SELECT COALESCE(NameResults.[KEY], DescriptionResults.[KEY]) AS [KEY],
ISNULL(NameResults.Rank, 0) * 3 +
ISNULL(DescriptionResults.Rank, 0) AS Rank
FROM
CONTAINSTABLE(Product, Name, @Word,
LANGUAGE 'English') AS NameResults
FULL OUTER JOIN
CONTAINSTABLE(Product, Description, @Word,
LANGUAGE 'English') AS DescriptionResults
ON NameResults.[KEY] = DescriptionResults.[KEY]
2. Now create the SearchCatalog stored procedure. This stored procedure uses the SearchWord
procedure to calculate the search results.
CREATE PROCEDURE SearchCatalog
(@DescriptionLength INT,
@PageNumber TINYINT,
@ProductsPerPage TINYINT,
@HowManyResults INT OUTPUT,
@AllWords BIT,
@Word1 NVARCHAR(15) = NULL,
@Word2 NVARCHAR(15) = NULL,
@Word3 NVARCHAR(15) = NULL,
@Word4 NVARCHAR(15) = NULL,
@Word5 NVARCHAR(15) = NULL)
AS
/* @NecessaryMatches needs to be 1 for any-word searches and
the number of words for all-words searches */
DECLARE @NecessaryMatches INT
SET @NecessaryMatches = 1
IF @AllWords = 1
SET @NecessaryMatches =
CASE WHEN @Word1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @Word2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @Word3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @Word4 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @Word5 IS NULL THEN 0 ELSE 1 END;

/* Create the table variable that will contain the search results */
DECLARE @Matches TABLE
([Key] INT NOT NULL,
Rank INT NOT NULL)
-- Save matches for the first word
IF @Word1 IS NOT NULL
INSERT INTO @Matches
EXEC SearchWord @Word1
-- Save the matches for the second word
IF @Word2 IS NOT NULL
INSERT INTO @Matches
EXEC SearchWord @Word2
-- Save the matches for the third word
IF @Word3 IS NOT NULL
INSERT INTO @Matches
EXEC SearchWord @Word3
-- Save the matches for the fourth word
IF @Word4 IS NOT NULL
INSERT INTO @Matches
EXEC SearchWord @Word4
-- Save the matches for the fifth word
IF @Word5 IS NOT NULL
INSERT INTO @Matches
EXEC SearchWord @Word5
-- Calculate the IDs of the matching products
DECLARE @Results TABLE
(RowNumber INT,
[KEY] INT NOT NULL,
Rank INT NOT NULL)
-- Obtain the matching products
INSERT INTO @Results
SELECT ROW_NUMBER() OVER (ORDER BY COUNT(M.Rank) DESC),
M.[KEY], SUM(M.Rank) AS TotalRank
FROM @Matches M
GROUP BY M.[KEY]
HAVING COUNT(M.Rank) >= @NecessaryMatches
-- return the total number of results using an OUTPUT variable
SELECT @HowManyResults = COUNT(*) FROM @Results

-- populate the table variable with the complete list of products
SELECT Product.ProductID, Name,
CASE WHEN LEN(Description) <= @DescriptionLength THEN Description
ELSE SUBSTRING(Description, 1, @DescriptionLength) + '...' END
AS Description, Price, Thumbnail, Image, PromoFront, PromoDept
FROM Product
INNER JOIN @Results R
ON Product.ProductID = R.[KEY]
WHERE R.RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND R.RowNumber <= @PageNumber * @ProductsPerPage
ORDER BY R.Rank DESC
3. Finally, you can test that the stored procedure works as expected by executing it from SQL Server
Management Studio:
EXEC SearchCatalog @DescriptionLength=20, @PageNumber=1, ?
@ProductsPerPage=10, @HowManyResults=null, @AllWords=0, @Word1='balloon'