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_T EXT ON dbo.DMS_REPOSITORY(DOCUMEN T_CONTENT_ TEXT);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMEN T_CONTENT_ TEXT)
KEY INDEX UI_DMS_REPOSITORY_DOCUMENT _CONTENT_T EXT
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.
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
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMEN
KEY INDEX UI_DMS_REPOSITORY_DOCUMENT
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.
Example from microsoft
Index on JobCandidateID
Search on Resume
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidat e(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidat e(Resume)
KEY INDEX ui_ukJobCand
WITH STOPLIST = SYSTEM;
GO
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql
Index on JobCandidateID
Search on Resume
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidat
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidat
KEY INDEX ui_ukJobCand
WITH STOPLIST = SYSTEM;
GO
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql
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,EFFECTIV E_DATE,VER SION_NUMBE R,FILE_NAM E FROM DMS_MANAGEMENT_REPOSITORY_ VIEW
WHERE
CONTAINS(DOCUMENT_CONTENT_ TEXT,@SEAR CH_KEYWORD )
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,EFFECTIV
WHERE
CONTAINS(DOCUMENT_CONTENT_
ASKER
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,EFFECTIV E_DATE,VER SION_NUMBE R,FILE_NAM E FROM DMS_MANAGEMENT_VIEW
WHERE
CONTAINS(DOCUMENT_CONTENT_ TEXT,@SEAR CH_KEYWORD )
Thank you.
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,EFFECTIV
WHERE
CONTAINS(DOCUMENT_CONTENT_
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search
ASKER
Thank you
CREATE UNIQUE INDEX UI_DMS_REPOSITORY_DOCUMENT
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMEN
KEY INDEX UI_DMS_REPOSITORY_DOCUMENT
WITH STOPLIST = SYSTEM;
GO