SELECT DATABASEPROPERTY('Northwind', 'IsFullTextEnabled')
전체 텍스트 시스템 저장 프로시저, Transact-SQL 함수 및 SQL Server 엔터프라이즈 관리자를 사용하여 전체 텍스트 검색 정보를 수집할 수 있다. 데이터베이스, 카탈로그, 테이블, 열의 각 레벨에 대해 메타데이터와 상태 정보를 검색할 수 있다.
( ※ 각 항목을 클릭하세요 )
시스템 저장 프로시저 사용
다음 표에 열거한 전체 텍스트 시스템 저장 프로시저를 사용하여 전체 텍스트 인덱스에 관한 정보를 입수할 수 있다.
Microsoft Search 서비스에서는 전체 텍스트 인덱스와 전체 텍스트 카탈로그의 두 구조를 사용하여 전체 텍스트 검색을 구현한다.
저장 프로시저 기능
sp_help_fulltext_catalogs 지정된 전체 텍스트 카탈로그의 ID, 이름, 루트 디렉토리, 상태 및 전체 텍스트 인덱스된 테이블 수 반환
sp_help_fulltext_tables 전체 텍스트 인덱싱이 가능한 테이블 목록 반환
sp_help_fulltext_columns 전체 인덱싱이 가능한 열 반환
Transact-SQL 함수 사용
또한 Transact-SQL 함수를 사용하여 전체 텍스트 속성 값을 얻을 수 있다.
다음 표는 전체 텍스트 검색에 관한 정보를 얻는 데 사용할 수 있는 자주 사용되는 속성 값 목록이다.
함수 속성값 속성 설명
COLUMNPROPERTY IsFullTextIndexed 전체 텍스트 검색이 가능한 열
DATABASEPROPERTY IsFullTextEnabled 전체 텍스트
검색이 가능한
데이터베이스
INDEXPROPERTY IsFulltextKey Microsoft Search 서비스에 의해 사용된 인덱스
OBJECTPROPERTY TableHasActive
FulltextIndex 전체 텍스트 검색이 가능한 테이블
FULLTEXT
CATALOGPROPERTY PopulateStatus Microsoft Search 카탈로그 처리 상태 반환
ItemCount Microsoft Search 카탈로그에 포함된 항목 수 반환
IndexSize 전체 텍스트 인덱스 크기(메가바이트 단위) 반환
UniqueKeyCount Microsoft Search 카탈로그에서 고유하게 처리될 수 있는 잡음 없는 단어의 어림 수 반환
LogSize 최종 전체 텍스트 인덱스 크기(메가바이트 단위) 반환
Populate
CompletionAge 참조된 Microsoft Search 카탈로그에 대한 최신 업데이트 날짜 및 시간 반환
FULLTEXTSERVICE
PROPERTY ResourceUsage Microsoft Search 서비스를 위한 상대적인 운영 체제 실행 우선 순위 설정 지정
IsFullTextInstalled SQL Server를 실행하는 서버에 설치 성공
예제
IsFullTextEnabled 속성은 Northwind 데이터베이스에 대해 전체 텍스트 쿼리를 할 수 있는지 여부를 결정하는 데도 사용된다.
USE northwind
SELECT DATABASEPROPERTY('Northwind', 'IsFullTextEnabled')
GO
1이라는 값이 반환되면 Northwind 데이터베이스에 대해 전체 텍스트 쿼리가 가능하다. 반환된 값이 0이면 Northwind 데이터베이스에 대해 전체 텍스트 쿼리를 할 수 없다.
CONTAINS 조건자를 사용하여 특정 용어를 검색할 수 있다.
( ※ 각 항목을 클릭하세요 )
일반 구문
CONTAINS 조건자 함수의 첫번째 매개 변수는 검색되는 열 이름이고 두번째 매개 변수는 전체 텍스트 검색 조건이다.
UPDATE {table_name | view_name}
SET
{ column_name={expression | DEFAULT | NULL}
|@variable=expression}[,…n]
[FROM {
}
[WHERE search_conditions]
검색 조건 구문 요소
CONTAINS 조건자는 문자 기반 열에서 다음을 검색하는 복합 구문을 지원한다.
특정 단어의 어미 변화형(생성 용어).
예를 들어, drive라는 단어의 어미 변화형을 검색하려고 한다. 테이블에 있는 여러 행에 drive, drives, drove, driving, driven이 포함되어 있다면 drive라는 단어의 어미 변화를 통해 생성될 수 있는 단어들이므로 모두 결과 집합에 포함될 것이다.
지정된 텍스트로 시작하는 단어 또는 구(접두 용어).
구의 경우에는 구 안에 있는 각 단어가 접두어로 간주된다. 예를 들어, auto tran*은 automatic transmission과 automobile transducer와 일치된다.
다른 단어나 구와 가까운 단어 또는 구(근접 용어).
예를 들어, ice라는 단어가 hockey라는 단어와 가까이 있거나, ice skating이라는 구가 ice hockey라는 구와 가까이 있는 행을 검색할 수 있다.
하나 이상의 특정 단어 및 구(단순 용어).
한 단어(word)는 공백이나 문장 부호가 없는 하나 이상의 문자(character)이다. 구(phrase)는 단어와 단어 사이에 공백이 있고, 문장 부호가 있거나 없는 여러 단어로 이루어질 수 있다. 예를 들어, ice는 단어이고 ice skating은 구이다. 이러한 단어나 구를 단순 용어(simple term)이라고 한다. SQL Server는 잡음 단어를 검색 기준에서 빼버린다.
가중치가 있는 단어 또는 구(가중 용어).
단어 또는 구에 가중치를 지정함으로써 검색 결과에 등급을 매길 수 있다. 가중치가 높은 단어나 구를 포함하는 쿼리 결과가 그렇지 않은 결과보다 먼저 제시된다.
CONTAINS 조건자는 AND와 OR를 사용하여 이상의 여러 용어를 결합할 수 있다. 예를 들어, 같은 전체 텍스트 사용 가능 데이터베이스 열에서 latte와 New York-style bagel이 포함된 모든 행을 검색할 수 있다. 또한 AND NOT을 사용하여 용어를 부정할 수 있다. 예: bagel and not cream cheese.
CONTAINS 검색에서는 항상 대문자와 소문자를 구분하지 않는다.
예제 1
다음 쿼리는 전체 텍스트 검색 가능 열에서 English Thyme이라는 구가 있는 모든 행의 plant_id, common_name 및 price를 반환한다. 열 이름 자리에 사용된 별표(*)는 테이블에 대해 전체 텍스트 검색이 가능한 모든 열을 검색한다.
SELECT plant_id, common_name, price
FROM plants
WHERE CONTAINS( *, ' "English Thyme" ' )
GO
예제 2
다음 쿼리는 description 열에서 "Jean LeDuc has always loved ice hockey" 및 "Jean Leduc on Ice-Hockey at Its Best"와 같은 항목을 반환한다.
SELECT article_id
FROM hockey_articles
WHERE CONTAINS(description, '"Jean LeDuc" AND "ice hockey"' )
GO
FREETEXT 조건자를 사용할 때는 어떠한 단어나 구 집합, 완전한 문장까지 입력할 수 있다. 전체 텍스트 쿼리 엔진은 이 텍스트를 조사하여 모든 중요한 단어와 명사구를 찾아내 내부적으로 쿼리를 구성한다. FREETEXT 조건자는 CONTAINS 조건자보다 정확도가 떨어진다.
( ※ 각 항목을 클릭하세요 )
구문
…WHERE FREETEXT ( {column | * }, 'free_text' )
예제
다음은 description 열에 대해 FREETEXT 조건자를 사용하는 예제이다.
SELECT *
FROM news_table
WHERE FREETEXT( description,
'"The Fulton County Grand Jury said Friday an investigation of Atlanta's recent primary election produced no evidence that any irregularities took place."')
GO
이 예제에서 검색 엔진은 다음 단어와 명사구를 찾는다.
단어- Fulton, county, grand, jury, Friday, investigation, Atlanta, recent, primary, election, produce, evidence, irregularities
구-Fulton county grand jury, primary election, grand jury, Atlanta's recent primary election
이상의 단어와 구는 실제 검색이 수행되기 전에 내부적으로 쿼리로 결합되고 적절한 등급이 가중된다.
CONTAINSTABLE 함수와 FREETEXTTABLE 함수는 CONTAINS나 FREETEXT 조건자와는 약간 다르다. 이 두 함수는 기본적으로 파생 테이블을 생성한다.
( ※ 각 항목을 클릭하세요 )
CONTAINSTABLE 및 FREETEXTTABLE 사용
CONTAINSTABLE 함수와 FREETEXTTABLE 함수는 CONTAINS 및 FREETEXT 조건자와 유사하다. 그러나 이 두 함수는 문자 기반 데이터 형식을 포함하는 열에 대해 행이 하나도 없거나 행이 하나이거나 또는 행이 여러 개인 테이블을 반환한다. 또한 이 두 함수는 일반 테이블 이름처럼 SELECT 문의 FROM 절에서 참조될 수 있다.
일부구문
CONTAINSTABLE ( table , { column | * } ,'< contains_search_condition > '
[ , top_n_by_rank ] )
CONTAINSTABLE 함수는 CONTAINS 조건자와 같은 검색 조건을 사용한다.
일부구문
FREETEXTTABLE ( table , { column | * } , 'freetext_string' [
, top_n_by_rank ] )
이 두 함수를 사용할 때는 다음과 같은 사항과 지침을 고려한다.
CONTAINSTABLE과 FREETEXTTABLE은 모두 행이 하나도 없거나 하나이거나 또는 하나 이상인 테이블을 반환하므로 항상 FROM 절에서 지정되어야 한다.
CONTAINSTABLE과 FREETEXTTABLE은 검색 기준을 지정하는 데도 사용된다. 반환된 테이블에는 전체 텍스트 키 값을 포함하는 key 열이 있다.
전체 텍스트 검색이 가능한 각 테이블에는 고유값이 보장된 하나의 열이 있다. key 열에 반환된 값은 전체 텍스트 검색 조건에 지정된 검색 기준에 일치하는 행의 전체 텍스트 키 값이다.
CONTAINSTABLE 함수와 FREETEXTTABLE 함수에 의해 생성된 테이블에는 0부터 1,000 사이의 값을 포함하는 rank 열이 있다. 이 값은 검색 기준에 일치하는 정도에 따라 반환되는 행에 상대적으로 등급을 매기는 데 사용되며 결과 집합 외부의 범위에는 비교값이 없다.
top_n_by_rank 인수에는 정수를 지정하여 상위 n 등급까지만 내림차순으로 반환되도록 한다.
사용할 전체 텍스트 검색 방법 선택
이상에서 설명한 두 조건자와 두 함수는 전체 텍스트 검색 조건을 지정하는 데 사용되는 Transact-SQL 문은 같지만 사용 방법에는 중요한 차이가 있다.
CONTAINS 및 FREETEXT 조건자는 WHERE 절을 비롯한 SELECT 문의 어떠한 검색 조건에서나 사용한다.
CONTAINSTABLE 및 FREETEXTTABLE 함수는 SELECT 문의 FROM 절
에서 사용한다.
CONTAINSTABLE 함수와 FREETEXTTABLE 함수를 사용하는 쿼리는 키워드 CONTAINS와 FREETEXT를 사용하는 쿼리보다 더 복잡하다. 이 두 함수에 의해 반환되는 행을 SQL Server 원본 테이블의 행과 명시적으로 조인해야 한다.
CONTAINS 및 FREETEXT 조건자를 LIKE나 BETWEEN 등의 다른 Transact-SQL 조건자와 결합할 수 있다. 또한 이 두 조건자를 하위 쿼리에서 사용할 수 있다.
( ※ 각 항목을 클릭하세요 )
예제 1
다음은 카테고리가 Seafood가 아니고 sauces라는 단어와 seasonings라는 단어가 포함된 설명을 검색하는 예제이다.
USE northwind
GO
SELECT Description
FROM Categories
WHERE CategoryName <> 'Seafood'
AND CONTAINS(Description, ' sauces AND seasonings ')
GO
예제 2
이 예제는 하위 쿼리 내에서 CONTAINS를 사용한다. 이 쿼리는 pubs 데이터베이스를 사용하여 Moonbeam, Ontario의 flying saucer라는 곳 가까이에 있는 출판사에 대한 titles 테이블에 있는 모든 책의 제목 값을 찾는다. (출판사에 대한 이 정보는 pub_info 테이블의 pr_info 열에 있다. 이 설명과 일치하는 출판사는 한 곳 뿐이다.)
USE pubs
SELECT T.title, P.pub_name
FROM publishers AS P
INNER JOIN titles AS T ON P.pub_id = I.pub_id
WHERE P.pub_id = (
SELECT pub_id
FROM pub_info
WHERE CONTAINS (pr_info,
'moonbeam AND ontario AND "flying saucer"')
)
GO
