Friday, August 14, 2009

Why full-text population update is necessary?

--The below script will show why we need to regularly update full text index, if the change_tracking is not set to Auto
--Creating database
--There are two filegroups. The second flegroup will be used for the storing the full text objects.
--But, this is not a mendetory.
Create
database FullTextSearch
on primary (name = Data, filename = 'c:\FulltextSearch\data.mdf')
, FILEGROUP fulltextfilegroup (name= fulltextgroup,filename='c:\FulltextSearch\fulltext.ndf')
log on (name = Log, filename = 'c:\FulltextSearch\log.ldf');
Use FullTextSerch;

/******Why the full text index manual upade should run regularly?********/

If
object_id('FullTextedTable') is null
Drop table dbo.FullTextedTable;
Create
table dbo.FullTextedTable
(
id int identity(1,1),
name nvarchar(100)
);
-- any single column unique index is required for population for full text index
Alter table FullTextedTable add constraint PK_FullTextedTable primary key (id);
--Insert data into table
Insert into FullTextedTable
select 'Sachin'
union all
select 'Rahul'
union all
select 'Saurav'
--Creating full text catalog
--We are setting accent sensitivity to off because we do not require it
--We explicitly set it off because by default it will same as base database and at most of the user
--created database it is on
--Check-it by running follwoing query, it's SQL_Latin1_General_CP1_CI_AS
--select databasepropertyex('FullTextSearch','collation')
Create
Fulltext CataLog FullTextCatalog
on fileGroup fulltextfilegroup
IN PATH 'c:\FulltextSearch'
with ACCENT_SENSITIVITY = off
As Default
;
--Creating full text index
--We are setting change_tracking off and set no population for not to populate index just after index is created
Create fulltext Index on FullTextedTable
([name] language 1033)
key index PK_FullTextedTable
on FullTextCatalog
with change_tracking OFF, No Population
GO
-- Now we are populating the index, this should be done when workload is less
Alter fulltext index on FullTextedTable Start Full Population
Go
Alter fulltext index on FullTextedTable set change_tracking Manual
GO
--You can check the status in below table
select * from sys.fulltext_indexes;
--query the table
select * from FullTextedTable
where CONTAINS(name,'saurav')
--Now update the third row
update FullTextedTable set name = 'Virendra'
where id = 3;
--now again running the query
select * from FullTextedTable
where CONTAINS(name,'saurav')
-- This will return 3,Virendra
-- This is not correct and this can be misleading when there are more rows
--below query will not retrun any row
select * from FullTextedTable
where CONTAINS(name,'Virendra')
--Now we run the manual update
--This will update the index
Alter fulltext index on FullTextedTable Start update Population
;
--now again running the query, this will not retrun any rows
select * from FullTextedTable
where CONTAINS(name,'saurav')
--Now this will return the data
select * from FullTextedTable
where CONTAINS(name,'Virendra')

No comments: