MSSQL) Full Text Search(FREETEXTABLE) 관리 및 데이터 쿼리 모음

-- Full Text Search 를 enable로 설정
exec sp_fulltext_database @action='enable'

-- Full Text Search 한글로 설정되어있는지 확인
EXEC sp_help_fulltext_system_components @component_type = 'wordbreaker', @param = 1042;

-- Full Text Search 에 한글이 설치되어있는지 확인
SELECT * FROM sys.fulltext_languages WHERE lcid='1042';

-- 현재 설치되어있는 언어 목록들
SELECT * FROM sys.fulltext_languages;

-- Catalog 재구성
ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD

-- catalog 진행 현황 SQL
DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'YourCatalogName'
SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
     ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
                  WHEN 0 THEN 'Idle'
                  WHEN 1 THEN 'Full Population In Progress'
                  WHEN 2 THEN 'Paused'
                  WHEN 3 THEN 'Throttled'
                  WHEN 4 THEN 'Recovering'
                  WHEN 5 THEN 'Shutdown'
                  WHEN 6 THEN 'Incremental Population In Progress'
                  WHEN 7 THEN 'Building Index'
                  WHEN 8 THEN 'Disk Full.  Paused'
                  WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
                  
 -- Full Text indexing
ALTER FULLTEXT INDEX ON [YourDBName] START FULL POPULATION                 


-- 하단은 FREETEXT, FREETEXTTABLE 사용방법

-- FREETEXT 를 이용한 Full Text Searching
SELECT * FROM YOUR_TABLE WHERE FREETEXT(YOUR_COLUMN, 'Searching keywords')

-- FREETEXTTALBE 을 이용한 Full Text Searching
SELECT FT_TBL.*,
       KEY_TBL.RANK
FROM YOUR_TABLE AS FT_TBL
         INNER JOIN FREETEXTTABLE(YOUR_TABLE,
                                  (YOUR_COLUMN_1, YOUR_COLUMN_2 ,YOUR_COLUMN_3),
                                  'Searching keywords',
                                  LANGUAGE N'Korean', 10) AS KEY_TBL
                    ON FT_TBL.Index = KEY_TBL.[KEY]
ORDER BY RANK DESC;
-- column1, column2, column3 순서대로 Rank점수가 산정
-- LANGUAGE N'Korean', 10 => 사용할 언어는 한국어, Rank 점수가 높은 10개를 순서대로 가져오기