SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Create table content 
(
Pagename varchar(20) not null primary key,
URL varchar(30) not null,
Description text null,
Keywords varchar(4000) null
)
INSERT content values ('home.asp','home.asp','This is the home page','home,SQL')
GO
INSERT content values ('pagetwo.asp','/page2/pagetwo.asp','NT Magazine is great','second')
GO
INSERT content values ('pagethree.asp','/page3/pagethree.asp','SQL Magazine is the greatest','third')
GO

 

To create an Index, follow the steps:

1. Create a Full-Text Catalog

2. Create a Full-Text Index

3. Populate the Index

1) Create a Full-Text Catalog

Following Screenshot shown as AdventureWorks2008 -  follow the same in ur Db.

 

 

 

Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index

 

SELECT Description, Keywords
FROM content
WHERE FREETEXT(*, 'second');
SELECT Description, Keywords
FROM content
WHERE FREETEXT((Description,Keywords), 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
(Note : If you dont have a primary key,Full text Index cannot be created).