asked
FULL-TEXT Search: Help Needed with the Unique Key Requirement
I am trying to create a full-text index on an table so that I can search it. The current primary key on the table contains two fields (Contract and LeaseName). I want to search LeaseName, but it is not unique by itself. I know that I can create a new field called ContractLeaseName which is a concatenation of the two fields (Contract and LeaseName) that are currently the primary key for the table. I can then use ContractLeaseName to then build the unique key for the full text search. This seems to me to be a potential waste of resources since I would have to maintain in my table the Contract, LeaseName, and the ContractLeaseName fields as well as modify my application to maintain all three fields for inserts and updates. Is this the only way to do this or is there something I still don't understand about Full-Text search?
I am going to make a couple assumptions here with my response that I hope is helpful! One is that you've evaluated and determined full text searching is the direction you need to go as well as that this is SQL Server 2008. With that disclaimer out of the way, I would research the Full-Text options available such as FREETEXT, CONTAINS, FREETEXTTABLE, and CONTAINSTABLE. This overview link may be helpful: [
http://msdn.microsoft.com/en-us/library/ms142583(v=SQL.105).aspx][1] ). [1]:
http://msdn.microsoft.com/en-us/library/ms142583(v=SQL.105).aspx
