Link to home
Start Free TrialLog in
Avatar of Member_2_7967119
Member_2_7967119

asked on

FULLTEXT Index On a Column defined as Varchar(max) which hold text content

I have a table that hold the contents of a PDF document in a SQL Server Table COLUMN "DOCUMENT_CONTENT_TEXT"  .

When I attempt to search on the contents in this column it is taking a lot of time. And hence I was suggested to use FULLText Index feature. I am new to the same and I use the following script and I get the following error "Column 'DOCUMENT_CONTENT_TEXT' in table 'dbo.DMS_REPOSITORY' is of a type that is invalid for use as a key column in an index."

CREATE UNIQUE INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT ON dbo.DMS_REPOSITORY(DOCUMENT_CONTENT_TEXT);  

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMENT_CONTENT_TEXT)
   KEY INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT  
   WITH STOPLIST = SYSTEM;
GO

On googling I found that for Indexing a column the limit is 900 bytes but I am not sure if I could predict the size of the content being stored in this varchar(max) column.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

DOCUMENT_CONTENT_TEXT is a text column with more than 900 bytes. we have to use an ID column in the unique index.

CREATE UNIQUE INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT ON dbo.DMS_REPOSITORY(SomeIdColumn);  

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMENT_CONTENT_TEXT)
   KEY INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT  
   WITH STOPLIST = SYSTEM;
GO
Example from microsoft

Index on JobCandidateID
Search on Resume


CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);  
CREATE FULLTEXT CATALOG ft AS DEFAULT;  
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)  
   KEY INDEX ui_ukJobCand  
   WITH STOPLIST = SYSTEM;  
GO

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql
Avatar of Member_2_7967119
Member_2_7967119

ASKER

Now in the query of the table, I get the following error.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'DMS_REPOSITORY_VIEW' because it is not full-text indexed.

In the view I use the following statement

SELECT ID,DOCUMENT_TITLE,EFFECTIVE_DATE,VERSION_NUMBER,FILE_NAME  FROM DMS_MANAGEMENT_REPOSITORY_VIEW
WHERE
CONTAINS(DOCUMENT_CONTENT_TEXT,@SEARCH_KEYWORD)
Any reason why it is giving me the error.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'DMS_REPOSITORY_VIEW' because it is not full-text indexed.

In the view I use the following statement

SELECT ID,DOCUMENT_TITLE,EFFECTIVE_DATE,VERSION_NUMBER,FILE_NAME  FROM DMS_MANAGEMENT_VIEW
WHERE
CONTAINS(DOCUMENT_CONTENT_TEXT,@SEARCH_KEYWORD)

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_7967119
Member_2_7967119

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you have full text search installed ? If not then you have to follow all the steps given from the Microsoft here-

https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search
Thank you