Full text indexing

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max). Each full-text index indexes one or more columns from the base table, and each column can have a specific language. Beginning in SQL Server 2008, full-text search supports more than 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi. For a complete list of the supported full-text languages.

For writing full-text queries, SQL Server provides a set of full-text predicates (CONTAINS and FREETEXT) and rowset-valued functions (CONTAINSTABLE and FREETEXTTABLE). Using these, applications and users can perform a variety of types of full-text searches, such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word.



How to create Full text search indexes in the real time environment:

1. Create Full text catalog
2. Create Full text index
3. Populate index.

by following the above steps we can create the Full text search indexes in the real time environment.

Once creation and populating of Catalogs are done, we can check the complete status by running the following. This query will return all the necessary details of the catalogs and its related indexes.



set transaction isolation level read uncommitted
set nocount on
declare @tbl sysname
declare @cat sysname
create table #temp_ca(
TABLE_OWNER varchar(100),
TABLE_NAME varchar(256),
FULLTEXT_KEY_INDEX_NAME varchar(256),
FULLTEXT_KEY_COLID int,
FULLTEXT_INDEX_ACTIVE int,
FULLTEXT_CATALOG_NAME varchar(256)
)
create table #temp_status(
Catalog varchar(64),
TblName varchar(64),
[IsEnabled] bit,
ChangeTracking varchar(24),
PopulateStatus varchar(64),
RowCnt int,
FTS_CT int,
Delta int,
PercentCompleted varchar(128),
path nvarchar(260)
)
insert into #temp_ca
exec sp_help_fulltext_tables
declare ca_cursor cursor for
select TABLE_NAME, FULLTEXT_CATALOG_NAME from #temp_ca
open ca_cursor
fetch next from ca_cursor into @tbl, @cat
while @@fetch_STATUS = 0
begin
insert into #temp_status
select
cast (@cat as varchar(40)) Catalog
, cast(object_name(si.id) as varchar(25)) TblName
, cast(OBJECTPROPERTY(tbl.id,'TableHasActiveFulltextIndex') as bit) as [IsEnabled]
, case isnull(OBJECTPROPERTY(tbl.id,'TableFullTextBackgroundUpdateIndexon'),0)
+ ISNULL(OBJECTPROPERTY(tbl.id,'TableFullTextChangeTrackingon'),0)
when 0 then 'Do not track changes'
when 1 then 'Manual'
when 2 then 'Automatic'
end [ChangeTracking]
, case FULLTEXTCATALOGPROPERTY ( @cat , 'PopulateStatus' )
when 0 then 'Idle'
when 1 then 'Full population in progress'
when 2 then 'Paused'
when 3 then 'Throttled'
when 4 then 'Recovering'
when 5 then 'Shutdown'
when 6 then 'Incremental population in progress'
when 7 then 'Building index'
when 8 then 'Disk is full. Paused.'
when 9 then 'Change tracking'
end PopulateStatus
, si.RowCnt, fulltextcatalogproperty(@cat, 'ItemCount') FTS_CT
, si.RowCnt - fulltextcatalogproperty(@cat, 'ItemCount') Delta
, cast ( 100.0 * fulltextcatalogproperty(@cat, 'ItemCount')
/ cast(si.RowCnt as decimal (14,2))
as varchar) +'%' as PercentCompleted
, ISNULL(cat.path, 'Check Default Path')
from
dbo.sysobjects as tbl
INNER JOIN sysusers as stbl on stbl.uid = tbl.uid
INNER JOIN sysfulltextcatalogs as cat
on (cat.ftcatid=OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId'))
AND (1=CasT(OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId') as bit))
INNER JOIN sysindexes as si on si.id = tbl.id
where si.indid in (0,1) and si.id = object_id(@tbl)
fetch next from ca_cursor into @tbl, @cat
end
close ca_cursor
deallocate ca_cursor
select * from #temp_status
drop table #temp_ca
drop table #temp_status


We can even check the index status, about how it is getting utilized and how much data is getting inserted, deleted and updated from the leaf level row by running the following query:


SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1



SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1