Full Text Search in SQL Server 2005

Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server.

Full-Text Search Queries
For samples on full-text search, created a table named Documents.

CREATE TABLE Documents(
[DocumentId] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](1000) NOT NULL,
[Text] [nvarchar](max) NULL,
[FullText] [ntext] NULL,

CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED
(
[DocumentId] ASC
)

)

Then created the full-text catalog named FTCatalog

CREATE FULLTEXT CATALOG FTCatalog


Enabled the fulltext indexing by running the sp_fulltext_database

exec sp_fulltext_database 'enable'

Then created the full-text index on table Documents

CREATE FULLTEXT INDEX ON Documents
(
Title,
[Text],
[FullText]
)
KEY INDEX PK_Documents ON WorksFTCatalog
WITH CHANGE_TRACKING AUTO

Creating the fulltext index will return a warning message:

Warning: Table or indexed view 'Documents' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

After the population status is set to idle after the indexing has finished by running the below FULLTEXTCATALOGPROPERTY select query,

SELECT FULLTEXTCATALOGPROPERTY('WorksFTCatalog', 'Populatestatus')


We can now run our first search query on the documents table.



CONTAINS

SELECT * FROM Documents WHERE CONTAINS(Text, N'songs')
This query will return me rows that the [Text] columns containing the word "songs".

But when I run "SELECT * FROM Documents WHERE CONTAINS(Text, N'song')", this query will return me no rows. Since the [Text] column values does not contain the word "song" but contains the word "songs"

If you wish to search for more than one words you can use OR. But the syntax changes a little bit: N' "songs" OR "song" '

SELECT * FROM Documents WHERE CONTAINS(Text, N' "songs" OR "song" ');
The character "*" can be used for zero or more any characters. So I can get any words with containing "song" in it.

SELECT * FROM Documents WHERE CONTAINS(Text, N' "song*" ');
The below sample query will search for all rows where Text column contains words of the form "song", like "songs", etc.

SELECT * FROM Documents
WHERE CONTAINS(Text, N' FORMSOF (INFLECTIONAL, song) ');


FREETEXT

The following query will find the given search criterias in a text "By alerting you to potentially unsafe attachments, ...". This query will search for all rows containing the words related with ones in the search terms.

SELECT * FROM Documents
WHERE FREETEXT(Text, N'safe potential attachment');

CONTAINSTABLE

CONTAINSTABLE has the functionality of ranking within our searches.

SELECT *
FROM Documents
INNER JOIN CONTAINSTABLE(Documents, [Text], 'songs') AS KEY_TBL
ON Documents.DocumentId = KEY_TBL.[KEY]


The last two columns in the result set that are named as "KEY" and "RANK" are from ContainsTable command table KEY_TBL

Key is the primary key value on the Documents table. And Rank is the search rank for the search term in the related row. Run the ContainsTable part alone.

SELECT * FROM CONTAINSTABLE(Documents, [Text], 'songs') AS KEY_TBL