Sql Server Full Text Search Oluşturma ve kullanım

Index oluşturma ve tanım aşamaları

Burada ftCatalog, Index'e verilen isim:

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

Tablonun bir Primary Key index'i olmalı:
-SalesLT schema. Default dbo olarak alınabilir. ProductDescription ise Tablo adı.

CREATE FULLTEXT INDEX ON SalesLT.ProductDescription(Description) KEY INDEX ui_ukProductDescription ON ftCatalog; 

Var olan veriden Full text index oluşturmak için:

ALTER FULLTEXT INDEX ON SalesLT.ProductDescription ENABLE; 
GO 
ALTER FULLTEXT INDEX ON SalesLT.ProductDescription START FULL POPULATION;

Full text index oluşturma sürecini izlemek için;

SELECT * FROM sys.dm_fts_index_population

Oluşturulan index ile ilgili detaylar (hangi kelimeden kaç adet var vs.);

--Bir kelimenin en çok geçtiği belge/text.

SELECT * FROM sys.dm_fts_index_keywords_by_document  
(   
    DB_ID(N'database_name'), OBJECT_ID(N'table_name')   
)

--Bir kelimeden kaç tane belge/text'de geçiyor

SELECT * FROM sys.dm_fts_index_keywords

( 

     DB_ID(N'database_name'), OBJECT_ID(N'table_name') 

)  

--Hangi keyword hangi sirada
--1. veya sonuncu sirada en cok hangi kelimeler bulunuyor...

SELECT TOP 100 * FROM sys.dm_fts_index_keywords_position_by_document  
(   
    DB_ID(N'Database name'),  
    OBJECT_ID(N'Table name')   
)
WHERE position=0
GO 

Detaylar;
https://azure.microsoft.com/en-us/blog/full-text-search-is-now-available-for-preview-in-azure-sql-database/

Ms Docs

Populate Full-Text Indexes

System catalog views
Full-Text Search and Semantic Search Catalog Views
-sys.fulltext_catalogs
-sys.fulltext_document_types
-sys.fulltext_indexes
-sys.fulltext_stoplists
-sys.fulltext_stopwords
-sys.fulltext_system_stopwords


System dynamic management views
Full-Text and Semantic Search Dynamic Management Views - Functions
-dm_fts_active_catalogs
-dm_fts_index_keywords
-dm_fts_index_keywords_by_document
-dm_fts_index_keywords_position_by_document
-dm_fts_index_population
-dm_fts_parser (inflectional formları görmek için)

---server admin hakkı gerekiyor (Sql 2016 da değişmiş olabilir)
-dm_fts_semantic_similarity_population

System stored procedures
Full-Text Search and Semantic Search Stored Procedures
-sp_fulltext_keymappings


Durum sorgulama için:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'UniqueKeyCount') AS [UniqueKeyCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'LogSize') AS [LogSize],
    FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') AS [IndexSize]
FROM sys.fulltext_catalogs AS cat

Notlar;
Geliştirme ortamında Full Text Search service aktif edilmiş olmalı.  "User Mode"' de bu servis kullanılamıyor. Express Edition da Advanced services kurulmalı.
Azure Sql DB üstteki detayların yazıldığı tarihte Sql Server üzerinde çalışan bazı özellikleri desteklemiyor. Örn: pdf gibi dosyalar üzerinde Full Text tanım yapabilmek.

Sorgu örnekleri

Full-text sorgularında kullanılan fonksiyonlar;
-full-text predicates (CONTAINS and FREETEXT)
-functions (CONTAINSTABLE and FREETEXTTABLE)

Belirli bir kelime veya deyimi aramak için çift tırmak (deyimde):
WHERE CONTAINS(Sentence, ' "learning curve" ');

Bir kelimenin diğer formlarında aramak için:
WHERE CONTAINS(Sentence, 'FORMSOF(FREETEXT, "ride the donkey")')
Döneceği sonuçda "riding the donkey" dahil olacak.

Bir kelimenin alternatif formlarını görmek için, örneğin "ride":

SELECT * FROM sys.dm_fts_parser('FORMSOF(freetext, ride)', 1033, 0, 0)

Ön ek ile aramak için:
WHERE CONTAINS (Description, ' "top*" ' );
Deyim olarak aranması durumunda
WHERE CONTAINS (Description, ' "light bread*" ' ) > "light breaded," "lightly breaded," or "light bread"

Çekim ekli kelime aramak için:
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")');

Kelime veya deyimlerde her bir kelime için öncelik verebilmek:
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
         Street WEIGHT(0.9),
         View WEIGHT(0.1)
         ) ' ) AS KEY_TBL

Bir kelimeye yakın olan başka kelimeleri aramak:
FROM Production.Document AS DocTable INNER JOIN
CONTAINSTABLE(Production.Document, Document, '(Reflector NEAR Bracket)' ) AS KEY_TBL
ON DocTable.DocumentID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK;
/
WHERE FREETEXT((Sentence), 'go NEAR fishing');


http://www.peachpit.com/articles/article.aspx?p=1276352&seqNum=7
Örneğin tek karakter için _
sql like search tricks

Supported Forms of Query Terms (Full-Text Search)

Query with Full-Text Search

How Search Query Results Are Ranked

Search for Words Close to Another Word with NEAR

CONTAINS

Genel konuların özeti

EF Core ile Full Text arama yapmak

Henüz doğrudan destekleyen bir method yok. Kullanılabilecek yöntem ya SP üzerinde ya da;

phrase = $"FORMSOF(FREETEXT, \"{phrase}\")";

            var query = _dataContext.SentenceExample
                .FromSql(@"SELECT [Id]                        
                          ,[Text]
                        FROM [dbo].[TextExample]
                        WHERE CONTAINS(Text, @p0)", phrase)
                .AsNoTracking();
         
            return query.ToList();

Semantic Search

Azure Sql şu an desteklemiyor.
https://docs.microsoft.com/en-us/sql/relational-databases/search/semantic-search-sql-server


Bakım

sys.fulltext_index_fragments
How to automatically maintain Full-Text indexes and catalogs