SQL Server Version : 2000 Standard Edition, sp4
I have a requirement in one of my application which needs to search on a table which contains billons of rows. This table have two columns FirstName and LastName. The input parameter of one of the sp is nvachar(4000) datatype. The value is passed as
The end result should be something like this
Select ID from Emp where FirstName like '%firstname1%' or LastName Like '%LastName1%'
Select ID from Emp where FirstName like '%firstname2%' or LastName Like '%LastName2%'
Select ID from Emp where FirstName like '%firstname3%' or LastName Like '%LastName3%'
Select ID from Emp where FirstName like '%firstname4%' or LastName Like '%LastName4%'
Is this scenario right candidate for Fulltext index? Please note that , I neither want rank value feature which is available in FTI nor I want Linguistic search. I just need to do a patern search but the parameter contains multiple values.
What is the best solution for this? Mind that this is 2000 and as far as Performance in FTI is concerned there were few issues in 2000 if am correct. The management of FTI in 2000 is also an issue like it is not completely integrated to the db engine.
any help is highly appreciated.
Given the "billion of rows" you may face some extremely steep challenges in SQL Server 2000 FT.
This is what I would do: Design a custom index Keyword/Results table including frequency column. Populate the table as the search requests coming in. If the custom index for a keyword is up-to-date to 5 billion rows, you only have to search the tail (like 100M) for recent hits, update the custom index table and return results.
You may find that the searches are quite biased, 100 or so very frequent and the rest downhill slide to a single search only.
Let us know how it works out. This project is quite exciting.
Yes there are issues with SQL Server 2000 Full Text because it was in Microsoft Search Service not installed by default. But I think it will be better than LIKE predicate in a UNION operation but if you can change to UNION ALL or JOIN then I would say run some tests.
Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
Although I am not an expert on FullText, What I have been bitten by in my first effort (POC) is that leading wild character searches doesn't fetch you the right data. The following post talks about the wild card search in general though. I had to drop the idea after learning that.
Sankar Reddy | http://sankarreddy.spaces.live.com/