Fulltext Index SqlServer

1. create catalog

2. Fulltext Index

3. Warning: Table or indexed view 'Documents' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

1. create catalog

-- Create a fulltext catalog
CREATE FULLTEXT CATALOG ft_catalog_database1
GO

-- Create a table to contain the poems
CREATE TABLE poems
(
 id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
 author VARCHAR(50) NOT NULL,
 title VARCHAR(100) NOT NULL,
 poem TEXT NOT NULL
);

-- Insert some data
INSERT INTO poems
(
 author, title, poem
)
SELECT
 
 'Johann Wolfgang von Goethe',
 'Night Thoughts',
 'Stars, you are unfortunate, I pity you,
Beautiful as you are, shining in your glory,
Who guide seafaring men through stress and peril.'
UNION ALL
SELECT
 'Nikki Giovanni',
 'I Love You',
 'I love you
because the Earth turns round the sun
because the North wind blows north.'
UNION ALL
SELECT
 'Lord Byron',
 'She Walks In Beauty',
 'She walks in beauty, like the night
Of cloudless climes and starry skies;
And all that''s best of dark and bright
Meet in her aspect and her eyes'
UNION ALL
SELECT
 'Christopher Marlowe',
 'Come Live With Me',
 'Come live with me, and be my love;
And we will all the pleasures prove
That valleys, groves, hills, and fields,
Woods or steepy mountain yields.'
UNION ALL
SELECT
 'Thomas Campbell',
 'Freedom and Love',
 'How delicious is the winning
Of a kiss at love''s beginning,
When two mutual hearts are sighing
For the knot there''s no untying!.';

-- Create a fulltext index on title and poem
CREATE FULLTEXT INDEX ON database1.dbo.poems
(
 title
 Language 0X0,
 poem
 Language 0X0
)
KEY INDEX PK__poems__00551192 ON ft_catalog_database1
WITH CHANGE_TRACKING AUTO;

*note - PK__poems__00551192 (primary key name)

It will populate error
Warning: Table or indexed view 'Documents' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

--it will return result

SELECT * 
FROM poems
WHERE CONTAINS(title, '"I love you"');

error is here

because it does not support writetext/updatetext as below:

 
UPDATETEXT
 Following is the code snippet to update portion of the string of the column. We will replace ‘I love you’ with ‘hello’ so that result will be ‘hello...’
DECLARE @ptr varbinary(16)

SELECT @ptr = textptr(poem)  FROM poems  WHERE id = 1
UPDATETEXT poems.poem @ptr 0 10 'hello'

--RESOLUTION 

--it will not return any result 

SELECT * 
FROM poems
WHERE CONTAINS(poem, 'hello');