Thursday, January 30, 2014

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.



 



wps_clip_image-21464




wps_clip_image-21540



 



wps_clip_image-21647



 



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



2) Create a Full-Text Index



wps_clip_image-21716





wps_clip_image-21765



wps_clip_image-21804



wps_clip_image-21860



wps_clip_image-21912



wps_clip_image-21958



wps_clip_image-22007



wps_clip_image-22049



wps_clip_image-22095



3) Populate the Index



 



wps_clip_image-22147



wps_clip_image-22222



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).

0 comments:

Post a Comment