Full-Text Search - practical introduction [video]

Arkadiusz Fronc SQL Server 21 March 2014

Full Text Search It is the first article on dba-presents.com about Full-Text Search in SQL Server 2012. As it is the beginning, it is a kind of an introduction to the topic. I wanted to show practical usage of the component so theory presented here is very limited. I focused on practice. There are two parts: text and video. Video is more detailed but all scripts used can be found in the article.

It is presented step by step how to install it, setup and use in a simple scenario. All of this can be watched on the video or read in a short form on this page.

Full-Text Search is an engine for search in texts. It uses linguistic knowledge to improve search results. It is much more powerful then LIKE operator in WHERE clause as it is more flexible. Full-Text Search knows how to split text to words and how words inflecting works. All this and more is used to provide better answers for queries.

Full-Text Search is an optional component of SQL Server. If it is not installed, it can be easily added.

Adding the component to existing installation

To check whether it is installed or not, I query IsFullTextInstalled parameter.

select serverproperty('IsFullTextInstalled')

If it returns 1, Full-Text Search is installed. If 0 then it is not.

If not, then it can be done by running the installer.

For SQL Server 2012 Express edition, the installation file can be found at Microsoft Download Center (SQL Server 2012). Remember to download Advanced Services version (SQLEXPRADV* file) as only this one contains Full-Text Search component.

Then run the downloaded file. Choose Installation on the left and New SQL Server stand-alone installation or add features to an existing installation.

On the Installation Type screen choose Add features to an existing instance of SQL Server 2012. Select an instance name from the list to which you want to add Full-Text Search and click Next.

On the Features Selection screen mark Full-Text and Semantic Extractions for Search as checked. Then click Nexts until the installation starts. It takes a few minutes to complete.

The installation can be verified by querying IsFullTextInstalled parameter again. It should return 1.

Database structure creation

I create a sample table for articles.

create table dbo.articles (
   id int identity(1, 1) not null,
   title varchar(255),
   content varchar(max)
);
alter table dbo.articles add constraint PK_articles primary key clustered (
   id
);

Then I load sample data from files using statements like the below one:

insert into dbo.articles (title, content)
select 'DBA presents - Testing T-SQL script before execution', bulkcolumn
from openrowset(bulk 'c:\temp\articles\DBA presents - Testing T-SQL script before execution.htm', single_clob) as t

Create Full-Text Search structures

A catalog for Full-Text objects needs to be created

create fulltext catalog DbaPresentsArticles;

An index has to be created on columns which will be searched on. I want to search on title and content columns so those two will be indexed:

create fulltext index on dbo.articles (
   title language 1033,
   content language 1033
)
key index PK_articles on DbaPresentsArticles
with change_tracking auto;

Querying data - samples

Examples presented on the video:

Contains function usage

select *
from dbo.articles
where contains(content, 'dbcc');

It returns rows where content column contains dbcc word.

select *
from dbo.articles
where contains(content, 'dbcc AND NOT checkdb');

It returns rows where content column contains dbcc word but not checkdb.

```
select *
from dbo.articles
where contains(content, '"SQL query"');
```
It returns rows where content column contains exact phrase SQL query. The words must appear in this order in the text close to each other.
```
select *
from dbo.articles
where contains(content, 'near(dbcc, checkident)');
```
It returns rows where content column contains dbcc word and checkident. They must be near to each other but they can be separated by other words. Maximum allowed distance between those words can be specified as the third parameter. If not specified, the default is used which is MAX - any distance is accepted.
```
select *
from dbo.articles
where contains(content, 'formsof(INFLECTIONAL, detached)');
```
It returns rows where content column contains any form of detached word like detach, detaching. Full-Text Search uses knowledge about a language to find the word including its inflectional forms.

Freetext function usage

```
select *
from dbo.articles
where freetext(content, 'detached');
```
It returns rows where content column contains any form of detached word like detach, detaching or synonyms based on thesaurus file. freetext function is more liberal than contains. It generally returns more or the same number of rows as much more forms are accepted.
```
select *
from dbo.articles
where freetext(content, 'datafiles filegroup space');
```
It returns rows where content column contains any of those three words datafiles, filegroup, space. Opposite to contains function, logical operators like OR, AND are not required.

Ranking results with freetexttable

```
select *
from freetexttable(dbo.articles, content, 'datafiles filegroup space') as t
        join dbo.articles a on t.[key] = a.id
order by t.rank desc;
```
It returns rows where content column contains any of those three words datafiles, filegroup, space. An advantage of freetexttable comparing to freetext is rank column in the result. The higher rank value is the more matching results are.

select *
from freetexttable(dbo.articles, content, 'list of dbs size') as t
        join dbo.articles a on t.[key] = a.id
order by t.rank desc;

It behaves similarly to the previous example.

Video demonstration

The main part of this article is the video demonstration below.