SQL Server Performance Forum – Threads Archive
Free Text search
Hi All In SQL 2K Suppose there is a columnNotes Varchar (5000) I want to find those rows whose notes contain keyword "Complaint"
So I write a query SELECT * FROM tblXYZ WHERE Notes LIKE ‘%Complaint%’ Now since this a non sargable argument even if I index this column my result for "complaint"
won’t be good in performance . What to do in this case?? I have heard that free text search feature is there in SQL 2000 which will create index on this column based on the keyword . Can anyone help me with this Articles , references and snippets are welcome
There is a node in enterprise manager for full text catalogs. Also check books online for the sp’s prefixed with ‘sp_fulltext’. Once configured, you should then read BOL for the CONTAINS clause, along with related items.
…and in addtion to Chappy comments, for optimization refer to this link http://www.sql-server-performance.com/tb_search_optimization.asp] _________
Satya SKJ
…and make sure that the COLLATION (language setting) of the target columns is supported by SQL Server FTS. Nathan H.O.
If you want to look for the work Complaint then you could set up an index table and create a link to the main table whenever the word Complaint is entered. This will certainly speed up queries but at the expense of slowing the data entry. i.e. You will have to parse the data field when entered and inset a row in the ‘ComplaintIndexTable’ if the word appears. You could also parse for variations on the spelling and insert into the ‘ComplaintIndexTable’ – complaint, compliant complant etc. If the need is more generic, needing to look for many different words, there are applications that will do that for you by creating indexes based on data. They will omit common word ‘the’ ‘in’ ‘of’ etc. as required. It depends how great your need is and how generic the query as to whther it would be worth the investment.
]]>