Full-text search 900 bytes limitation
-
As I've understood, it's not possible to have full-text search indexing on columns larger than 900 bytes. I know I can overcome this problem by adding large columns as non-key included columns to non-clustered indexes. But still I'm not able to use the powerful search capabilities of full-text search. I can only use queries like "select c from t where c like '%find%'". That's, I can not easily look for combination of words. Is there any known solution for searching large fields that can not be easily indexed in SQL Server? As an example, how has it been implemented to search multiple words in codeproject forums? Is it something simple like this?
SELECT * FROM Messages WHERE Title LIKE '%word1%' OR Title LIKE '%word2%'
Thanks in advance for any help.
-
As I've understood, it's not possible to have full-text search indexing on columns larger than 900 bytes. I know I can overcome this problem by adding large columns as non-key included columns to non-clustered indexes. But still I'm not able to use the powerful search capabilities of full-text search. I can only use queries like "select c from t where c like '%find%'". That's, I can not easily look for combination of words. Is there any known solution for searching large fields that can not be easily indexed in SQL Server? As an example, how has it been implemented to search multiple words in codeproject forums? Is it something simple like this?
SELECT * FROM Messages WHERE Title LIKE '%word1%' OR Title LIKE '%word2%'
Thanks in advance for any help.
-
Full-text index doesn't have a limit of 900 bytes on indexed columns. The limit is only for key index. So if you have a good primary key, you should not have any problems using full-text indexes.
The need to optimize rises from a bad design
What do you mean by key index Mika? Primary clustered index? Should I necessarily add a column as a non-key included column to a non-clustered index if I want to have full-text search on it? Thanks
-
What do you mean by key index Mika? Primary clustered index? Should I necessarily add a column as a non-key included column to a non-clustered index if I want to have full-text search on it? Thanks
-
Not necessarily clustered index, but primary key or unique key of the table. See
KEY INDEXargument in CREATE FULLTEXT INDEX (Transact-SQL)[^]The need to optimize rises from a bad design
Thank you. So there should be no problem. I don't know why somebody told me it's not possible to use it on fields larger than 900 bytes! Maybe I misunderstood the point.
_
modified on Sunday, October 5, 2008 4:47 PM