I am trying to come up with the most efficient way to search for records in a collection of about 130,000 total records. The long, short of it is we have about 130,000 Active Directory records. I am building a site that looks up users in AD. I would like to implement TypeAhead functionality on a Search box. I could query AD directly in code, but am thinking about dumping the data into SQL Server and querying that instead. TypeAhead does a search as you type characters in the textbox. Is it efficient to search a table of 130,000 records by single characters? I am trying to determine if the searching should be done by the SQL Server engine or on the site? I am thinking SQL Server. If true, is it better to use FREETEXT or CONTAINS full text searches?
Curious what the community thinks about this? Or am I way off?
Best Answer
I'm not sure I'm understanding your question. FREETEXT evaluates not only on the string, but also the meaning. Whereas CONTAINS is used for precise or fuzzy searching on a string.