question

hardingr2000 avatar image
hardingr2000 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?
indexfull-textsearchunique
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Michael Wanke avatar image
Michael Wanke answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.