SQL Server full text index

From full text index we can enhance the performance of SQL queries which you can directly see on SQL actual plan execution.

Documents supported for full text index :-

SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types

Language supported for full text :-

SELECT * FROM sys.fulltext_languages

ORDER BY lcid

Already existed full text catalog in Database :-

SELECT fulltext_catalog_id, name 

FROM sys.fulltext_catalogs

Table and Full Text Catalog relation :-

SELECT t.name AS TableName, c.name AS FTCatalogName

FROM sys.tables t JOIN sys.fulltext_indexes i

  ON t.object_id = i.object_id

JOIN sys.fulltext_catalogs c

  ON i.fulltext_catalog_id = c.fulltext_catalog_id

Creation of Full text Index on table column :-

--!!! Drop Full Text Catalogue !!!--
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
ALTER FULLTEXT INDEX ON [dbo].[tblData] DISABLE
GO

/****** Object:  FullTextIndex     Script Date: 06/13/2013 23:11:07 ******/
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
DROP FULLTEXT INDEX ON [dbo].[tblData]
GO
-------------------------------------------------------------------------------------------------------------------
--!!! Add a new column !!!--
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
  WHERE TABLE_NAME = 'tblData'
  AND COLUMN_NAME = 'Records') 
BEGIN
ALTER TABLE dbo.tblData ADD
Records nvarchar(450) NULL
END
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Update the Records column with Values !!!---
UPDATE dbo.tblData SET [Records]=FirstName +' | ' LastName
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Add index on DisplayUserText Column !!!---
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_Users_Records' AND object_id = OBJECT_ID('tblData'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Users_Records] ON [dbo].[tblData] 
(
[Records] ASC
)
WHERE ([Records] IS NOT NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create full text catalog !!!---
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = 'FTC_tblData')
BEGIN
CREATE FULLTEXT CATALOG [FTC_tblData] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create Stoplist !!!---
IF NOT EXISTS (SELECT Name FROM sys.fulltext_stoplists WHERE [name] = 'FTS_tblData')
CREATE FULLTEXT STOPLIST FTS_tblData FROM SYSTEM STOPLIST;
GO
------------------------------------------------------------------------------------------------------------------
--!!! Create Full text index !!!--
IF NOT EXISTS (SELECT * FROM sys.fulltext_indexes WHERE object_id = object_id('tblData'))
BEGIN
 CREATE FULLTEXT INDEX ON DBO.tblData([FirstName] LANGUAGE 0, [LastName] LANGUAGE 0)
 KEY INDEX PK_tblData ON FTC_tblData
    WITH STOPLIST = [FTS_tblData];
END
GO

---!!! Now we are done with full text index !!!---
(For more details visit this URL : https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/)

How to use :-

SELECT *
FROM tblUsers
WHERE CONTAINS(FirstName,'"*Jes*"');